RE: NOLOGGING

From: Gaja Krishna Vaidyanatha <oraperfman_at_yahoo.com>
Date: Mon, 25 Jun 2001 18:02:37 -0700
Message-ID: <F001.00337841.20010625181027@fatcity.com>

Ramon,

That is not true. Setting NOLOGGING at the object level only reduces the amount of redo generated for bulk INSERT operations with the /*+ APPEND */ hint, certain partition administration operations and of course during the creation of the object itself. It does not eliminate generation of redo during a delete operation.

Attached is a sample output from a couple of delete commands, one with LOGGING and the other with NOLOGGING: Cheers,

Gaja

Ramon Estevez <com.banilejas_at_codetel.net.do> wrote:
> Hi Greg,
>
> Yes, you should get a improve performance due to the
> nologging option in the
> delete wont write redo log information.
>
>
>
> Ramón Estévez
>
>
>
> *809-565-3121 x 225
>
>
>
> * com.banilejas_at_codetel.net.do
>
>
>
>
>
> -----Mensaje original-----
> De: root_at_fatcity.com [mailto:root_at_fatcity.com]En
> nombre de Greg Solomon
> Enviado el: Monday, 25 June, 2001 9:23 AM
> Para: Multiple recipients of list ORACLE-L
> Asunto: NOLOGGING
>
>
> Hi All
>
> If I have a delete which I know I never want to roll
> back, can I get a
> performance gain by using the nologging option ?
>
> Delete nologging is not, to the best of my
> knowledge, documented on 8.1.6
> ... but the query executes OK.
>
> However, when I used autotrace, the delete with
> nologging seemed to give the
> same or worse results than a normal delete.
>
> Greg
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Greg Solomon
> INET: greg.solomon_at_betfair.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>

> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Ramon Estevez
> INET: com.banilejas_at_codetel.net.do
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>

> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).

Gaja Krishna Vaidyanatha
Director, Storage Management Products,
Quest Software, Inc.
Co-author - Oracle Performance Tuning 101

Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/

SQL> select table_name, logging from user_tables;

TABLE_NAME                     LOG                                              
------------------------------ ---                                              
IDX_OBJ                        YES                                              
IOX_EXPLAIN                    YES                                              
MY_TAB                                                                          
PLAN_TABLE                     YES                                              
PROD_OBJECTS                   YES                                              
TEST_OBJECTS                   YES                                              
TEST_TAB                       YES

7 rows selected.

SQL> set autotrace traceonly
SQL> delete from test_objects where rownum < 11;

10 rows deleted.

Execution Plan

   0      DELETE STATEMENT Optimizer=CHOOSE (Cost=540 Card=306828 Byte          
          s=920484)                                                             
                                                                                
   1    0   DELETE OF 'TEST_OBJECTS'                                            
   2    1     COUNT (STOPKEY)                                                   
   3    2       TABLE ACCESS (FULL) OF 'TEST_OBJECTS' (Cost=540 Card=3          
          06828 Bytes=920484)                                                   
                                                                                

Statistics
----------------------------------------------------------                      
        243  recursive calls                                                    
         48  db block gets                                                      
         37  consistent gets                                                    
         22  physical reads                                                     
       5704  redo size                                                          
        850  bytes sent via SQL*Net to client                                   
        568  bytes received via SQL*Net from client                             
          3  SQL*Net roundtrips to/from client                                  
          7  sorts (memory)                                                     
          0  sorts (disk)                                                       
         10  rows processed

SQL> alter table test_objects nologging;

Table altered.

SQL> rollback;

Rollback complete.

SQL> select table_name, logging from user_tables;

TABLE_NAME                     LOG                                              
------------------------------ ---                                              
IDX_OBJ                        YES                                              
IOX_EXPLAIN                    YES                                              
MY_TAB                                                                          
PLAN_TABLE                     YES                                              
PROD_OBJECTS                   YES                                              
TEST_OBJECTS                   NO                                               
TEST_TAB                       YES

7 rows selected.

SQL> delete from test_objects where rownum < 11;

10 rows deleted.

Execution Plan

   0      DELETE STATEMENT Optimizer=CHOOSE (Cost=540 Card=306828 Byte          
          s=920484)                                                             
                                                                                
   1    0   DELETE OF 'TEST_OBJECTS'                                            
   2    1     COUNT (STOPKEY)                                                   
   3    2       TABLE ACCESS (FULL) OF 'TEST_OBJECTS' (Cost=540 Card=3          
          06828 Bytes=920484)                                                   
                                                                                

Statistics
----------------------------------------------------------                      
          0  recursive calls                                                    
         46  db block gets                                                      
          1  consistent gets                                                    
          0  physical reads                                                     
       5628  redo size                                                          
        854  bytes sent via SQL*Net to client                                   
        568  bytes received via SQL*Net from client                             
          3  SQL*Net roundtrips to/from client                                  
          2  sorts (memory)                                                     
          0  sorts (disk)                                                       
         10  rows processed

SQL> set autotrace off

SQL> spool off Received on Mon Jun 25 2001 - 20:02:37 CDT