Re: insert /*+APPEND*/ Unexpected result (more redo log)

From: Arup Nanda <orarup_at_hotmail.com>
Date: Tue, 06 May 2003 08:51:47 -0800
Message-ID: <F001.0059138A.20030506085147@fatcity.com>

The explanation is quite simple - you have encountered the classic case of block logging redo generation. In ARCHIVELOG mode, the redo generation occurs for each block, not for each data change as in case of a NOARCHIVELOG database. In your case the PCTUSED is 99, making the table allocating few rows per block, notmany; I suspect a single row per block and with row chaining Therefore a single row may change a few blocks and all those are logged as changes to data dictionary.

Here is the same test but using a PCTFREE of 10 and making the table NOLOGGING

SQL> create table redolog_test pctfree 10 pctused 90 as select * from all_objects where 1=2;

Table created.

SQL> alter table redolog_test nologging;

Table altered.

SQL> insert into redolog_test select * from all_objects;

1532 rows created.

Statistics

---

         29  recursive calls
        139  db block gets
      14654  consistent gets
          0  physical reads
     156248  redo size <====================================
       1186  bytes sent via SQL*Net to client
        756  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       1532  rows processed

SQL> insert /*+APPEND*/ into redolog_test select * from all_objects;

1532 rows created.

Statistics

---

         29  recursive calls
         17  db block gets
      14636  consistent gets
          0  physical reads
       3368  redo size <====================================
       1170  bytes sent via SQL*Net to client
        768  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       1532  rows processed

See the redo generation; it's much more reduced.

Hope this helps.

Arup Nanda
www.proligence.com

• Original Message ----- From: Saminathan_Seerangan_at_i2.com To: Multiple recipients of list ORACLE-L Sent: Tuesday, May 06, 2003 9:51 AM Subject: insert /*+APPEND*/ Unexpected result (more redo log)

  Hi All,

  I'm getting UNEXPECTED result in both 8i and 9iR2.

  Could someone please tell me why insert /*+APPEND*/ generates more redo log.

  I'm pretty sure that there is NO index involved in my test table(redolog_test).   Also the user is not a SYS and SYSTEM.   The DB is running in ARCHIVE LOG mode.

  SQL> show user
  USER is "MUT"

  SQL> drop table redolog_test;
  Table dropped.

  SQL> create table redolog_test pctfree 99 pctused 1 as select * from all_objects   where 1=2;
  Table created.

  SQL> insert into redolog_test select * from all_objects;   19412 rows created.

  Statistics

---

         2003  recursive calls 
        89992  db block gets 
       161720  consistent gets 
            0  physical reads 
     10661144  redo size  <======== 
          438  bytes sent via SQL*Net to client 
          433  bytes received via SQL*Net from client 
            4  SQL*Net roundtrips to/from client 
            2  sorts (memory) 
            0  sorts (disk) 
        19412  rows processed 

  SQL> truncate table redolog_test;
  Table truncated.

  SQL> insert /*+APPEND*/ into redolog_test select * from all_objects;   19412 rows created.

  Statistics

---

         2012  recursive calls 
          365  db block gets 
       142229  consistent gets 
            0  physical reads 
    159534376  redo size  <=======(15 Times higher) 
          430  bytes sent via SQL*Net to client 
          445  bytes received via SQL*Net from client 
            4  SQL*Net roundtrips to/from client 
            2  sorts (memory) 
            0  sorts (disk) 
        19412  rows processed 

  SQL>   What could be wrong?
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Arup Nanda
  INET: orarup_at_hotmail.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

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). Received on Tue May 06 2003 - 11:51:47 CDT