TIP # 63 : DIRECT Load and redo log generation

I had a client who uses "direct load" for its nightly job on productiton database.Suprisingly many archive logs were generated during job run.The whole purpose of "Direct load" is to improve performance by gnerating less archive logs.
However, in this case does not see any difference.
I am trying to explain on how Direct load would help.

Some facts :
1.The undo would normally be used to un-insert the rows in the event of a
failure or rollback - with DIRECT LOAD, undo is not necessary since the new rows are added entirely above the high water mark for the table.
2. When "Direct load" (/*+ append */) is used, Oracle can skip undo generation for the TABLE data - but not on the indexes.
3. In archivelog mode , REDO is normally generated with "Direct load" (/*+ APPEND */), it is UNDO that is skipped and then only for the table itself.
4.If table is placed into "nologging" mode (or use nologging in insert
command), then redo for the table as well as undo can be skipped..Again, only for the table - not for any indexes on the table itself.
5. Small redos which are generated in nologgin/DIRECT LOAD is used to protect the data dictionary.
6. To prevent archivelog generation in "Direct load", database and tablespace should not be in "Force logging mode". (Check v$database and dba_tablespaces).

Considering above facts, my first answer to a client was: "Archives are generated because of indexes on tables".
To demonstrate, Please follow below simple steps :

========== Table without index in Direct load ===========

SQL> create table tbl2 as select * from dba_objects where 1=2;

Table created.


--- Checking redo and undo before direct load


select a.value,b.name from v$sesstat a,v$statname b where a.statistic#=b.statistic# and a.sid in (select sid from v$mystat) and name like '%redo size%';

VALUE NAME
---------- ----------
0 redo size

select sum(undoblks)*8192/1024/1024 UNDO-MB from v$undostat where trunc(begin_time,'DD')=trunc(sysdate,'DD');

UNDO-MB
----------------------------
19.78125

---- Direct load


insert /*+ append */ into tbl2 nologging select * from dba_objects;

select a.value,b.name from v$sesstat a,v$statname b where a.statistic#=b.statistic# and a.sid in (select sid from v$mystat) and name like '%redo size%';

VALUE NAME
---------- ----------
7068580 redo size

select sum(undoblks)*8192/1024/1024 UNDO-MB from v$undostat where trunc(begin_time,'DD')=trunc(sysdate,'DD');

UNDO-MB
----------------------------
25.78125



Generated redo : 7068580 bytes
Generated undo : 6 MB


========== Table with index in Direct load ===========



SQL> create table tbl1 as select * from dba_objects where 1=2;

Table created.

SQL> create index idx1 on tbl1(object_name);

Index created.

SQL> create index idx2 on tbl1(owner);

Index created.


---- redo log before direct load


select a.value,b.name from v$sesstat a,v$statname b where a.statistic#=b.statistic# and a.sid in (select sid from v$mystat) and name like '%redo size%';

VALUE NAME
---------- ----------
0 redo size

select sum(undoblks)*8192/1024/1024 UNDO-MB from v$undostat where trunc(begin_time,'DD')=trunc(sysdate,'DD');

UNDO-MB
----------
25.78125


------- Direct load



SQL> insert /*+ append */ into tbl1 nologging select * from dba_objects;

62908 rows created.


------ Redo log and Undo after direct load.



select a.value,b.name from v$sesstat a,v$statname b where a.statistic#=b.statistic# and a.sid in (select sid from v$mystat) and name like '%redo size%';

VALUE NAME
---------- ----------
28251320 redo size

select sum(undoblks)*8192/1024/1024 from v$undostat where trunc(begin_time,'DD')=trunc(sysdate,'DD');

UNDO-MB
----------------------------
42.5078125




Generated redo : 28251320 bytes
Generated undo : 17 MB

More archives and more UNDOs in direct load on table with indexes.


Sum up :
=========

To use direct load and to take advantage of less archivelog generation and better performance, always consider the followings :
1. Disable indexes during direct load.
2. Make sure to use both /*+ append */ with nologging at the same time.
3. Make sure database and tablespace are not in nologging mode.




No comments: