HOW TO STOP GENERATION OF ARCHIVES DURING HEAVY INSERT

LOGGING is a keyword that used on creating the index, table or tablespace. If we use LOGGING when creating the object then DML operations on the objects are logged in redo log file. If we use NOLOGGING when creating the object, in some cases DML operations on the objects are not logged in redo log file.

The following scenario is run against a database running in ARCHIVELOG mode

and table is created with nologging option.

SQL> conn / as sysdba

16:11:52 SQL> create table finance.invoice nologging as select * from finance.material_records;

Table created.

16:16:10 SQL> select table_name,LOGGING FROM DBA_tables WHERE table_name='INVOICE' AND OWNER='FINANCE';

TABLE_NAME                     LOG

------------------------------ ---

INVOICE                        NO

We can see table has been created with NOLOGGING.

Now insert rows to INVOICE table.

SET AUTOTRACE ON STATISTICS

SQL>  select count(*) from  finance.invoice;

  COUNT(*)

----------

     90860

15:24:14 SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     474

Next log sequence to archive   476

Current log sequence           476

15:24:23 SQL>

Check the current sequence before inserting the bulk load. We have current sequence of 476.

Let’s insert bulk data to check and see how much archives are generated:

15:24:23 SQL> insert into finance.invoice select * from finance. material_records;

90860 rows created.

Statistics

---------------------------------------------------

         75  recursive calls

     304088  db block gets

      93827  consistent gets

      34311  physical reads

  231903788  redo size

        843  bytes sent via SQL*Net to client

        819  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          2  sorts (memory)

          0  sorts (disk)

      90860  rows processed

Now we can check the current sequence again.

15:32:29 SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     490

Next log sequence to archive   492

Current log sequence           492

We can see number of archive generated during the bulk inserts (492-476=16). It means archive logs generated with normal INSERT statement even we have NOLOGGING table. Now we will use HINT to insert the data

and see if archives are generating or not.

Again check the current sequence after manually switching the archives(3 times if you are using 3 redolog file). And then INSERT the data using “APPEND” Hint.

15:39:44 SQL> alter system switch logfile;

System altered.

15:39:59 SQL> /

System altered.

15:40:09 SQL> /

System altered.

15:36:15 SQL>  archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     495

Next log sequence to archive   497

Current log sequence           497

15:36:19 SQL>

16:42:33 SQL> INSERT /*+ APPEND */ INTO finance.invoice select * from finance.material_records;

90860 rows created.

Statistics

----------------------------------------------------------

         83  recursive calls

      41547  db block gets

      35927  consistent gets

      34311  physical reads

    9962248  redo size

        827  bytes sent via SQL*Net to client

        832  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

      90860  rows processed

15:43:53 SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     495

Next log sequence to archive   497

Current log sequence           497=è No CHANGE

15:46:41 SQL>

Commit;

We can see no archive generated during INSERT operation by using APPEND hint.

NOTE: In ARCHIVELOG mode, the APPEND hint will not reduce redo generation only when the table is set to NOLOGGING. Our table is set to nologging this is the reason archives are not generated during insert.

With the database running on NOARCHIVELOG mode, the addition of the APPEND hint will reduce the amount of redo generated even if the table is created with normal logging option. Same APPEND hint will generate redo if the database is in ARCHIVELOG mode if the table is created with normal logging option.