Deferred_segment_creation in 11gR2

Use Deferred_segment_creation parameter to determine when the database should create the segment(s) for tables

rajesh@ORCL> select * from v$version;

BANNER
--------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Elapsed: 00:00:00.40

rajesh@ORCL> show parameter deferred_segment;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------------
deferred_segment_creation            boolean     TRUE
rajesh@ORCL>
rajesh@ORCL>
rajesh@ORCL> create table t(
  2     x number constraint t_pk primary key,
  3     y number constraint t_uk unique,
  4     z clob )
  5     lob (z)
  6     store as t_z_lob;

Table created.

Elapsed: 00:00:02.46
rajesh@ORCL>
rajesh@ORCL>
rajesh@ORCL> select segment_name, segment_type
  2  from user_segments
  3  /

no rows selected

Elapsed: 00:00:00.03
rajesh@ORCL>
rajesh@ORCL>
rajesh@ORCL> drop table t purge;

Table dropped.

Elapsed: 00:00:00.32



Setting the parameter deferred_segment_creation to TRUE will prevent underlying segments to be created, unless data gets actually inserted.

rajesh@ORCL>
rajesh@ORCL> alter session set deferred_segment_creation = false;

Session altered.

Elapsed: 00:00:00.01
rajesh@ORCL>
rajesh@ORCL> create table t(
  2     x number constraint t_pk primary key,
  3     y number constraint t_uk unique,
  4     z clob )
  5     lob (z)
  6     store as t_z_lob
  7  /

Table created.

Elapsed: 00:00:01.07
rajesh@ORCL>
rajesh@ORCL> select segment_name, segment_type
  2  from user_segments
  3  /

SEGMENT_NAME                   SEGMENT_TYPE
------------------------------ ------------------
T                                                      TABLE
SYS_IL0000074718C00003$$       LOBINDEX
T_PK                                               INDEX
T_UK                                               INDEX
T_Z_LOB                                        LOBSEGMENT

Elapsed: 00:00:00.03
rajesh@ORCL>
rajesh@ORCL>

Row Chaining and Migrating

When a row that originally fit into one data block is updated so that the overall row length increases, and the block's free space is already completely filled. In this case, Oracle migrates the data for the entire row to a new data block, assuming the entire row can fit in a new block. Oracle preserves the original row piece of a migrated row to point to the new block containing the migrated row. The rowid of a migrated row does not change.

rajesh@10GR2> drop table t purge;

Table dropped.

Elapsed: 00:00:00.09
rajesh@10GR2>
rajesh@10GR2> create table t(x number,y varchar2(4000),z varchar2(4000));

Table created.

Elapsed: 00:00:00.03
rajesh@10GR2> insert into t(x) values(1);

1 row created.

Elapsed: 00:00:00.00
rajesh@10GR2> commit;

Commit complete.

Elapsed: 00:00:00.03
rajesh@10GR2>
rajesh@10GR2> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
rajesh@10GR2>
rajesh@10GR2> exec show_space(USER,'T','TABLE');
l_total_blocks****************  16
l_total_bytes*****************  131072
l_unused_blocks***************  12
l_unused_bytes****************  98304
l_last_used_extent_file_id****  7
l_last_used_extent_block_id***  377
l_last_used_block*************  4
l_unformatted_blocks**********  0
l_unformatted_bytes***********  0
l_fs1_blocks******************  0
l_fs1_bytes*******************  0
l_fs2_blocks******************  0
l_fs2_bytes*******************  0
l_fs3_blocks******************  0
l_fs3_bytes*******************  0
l_fs4_blocks******************  1
l_fs4_bytes*******************  8192
l_full_blocks*****************  0
l_full_bytes******************  0

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
rajesh@10GR2> select rowid from t;

ROWID
------------------
AAAjDIAAHAAAAF8AAA

Elapsed: 00:00:00.03
rajesh@10GR2>

Now table 'T' consumes 4 blocks to store its data. Now lets add more data's to current row so that overall length increases and data spans to new block.

rajesh@10GR2> update t
  2  set y = rpad('*',4000,'*'),
  3     z = rpad('*',4000,'*')
  4  where x = 1;

1 row updated.

Elapsed: 00:00:00.01
rajesh@10GR2>
rajesh@10GR2> commit;

Commit complete.

Elapsed: 00:00:00.04
rajesh@10GR2> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09
rajesh@10GR2> exec show_space(USER,'T','TABLE');
l_total_blocks****************  16
l_total_bytes*****************  131072
l_unused_blocks***************  6
l_unused_bytes****************  49152
l_last_used_extent_file_id****  7
l_last_used_extent_block_id***  377
l_last_used_block*************  10
l_unformatted_blocks**********  0
l_unformatted_bytes***********  0
l_fs1_blocks******************  0
l_fs1_bytes*******************  0
l_fs2_blocks******************  0
l_fs2_bytes*******************  0
l_fs3_blocks******************  0
l_fs3_bytes*******************  0
l_fs4_blocks******************  5
l_fs4_bytes*******************  40960
l_full_blocks*****************  2
l_full_bytes******************  16384

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06

Now Unused blocks got reduced from 12 to 6, means some data has migrated to new blocks but the rowid of this migrated rows remains unchanged.

rajesh@10GR2> select rowid from t;

ROWID
------------------
AAAjDIAAHAAAAF8AAA

Elapsed: 00:00:00.01
rajesh@10GR2>

Tracking operation on Specific Schema object ** Audit **

Oracle build in Auditing feature to Track the occurrence of SQL statements in subsequent user sessions You can track the occurrence of a specific SQL statement or of all SQL statements authorized by a particular system privilege. ( To audit occurrences of a SQL statement audit_trail should be set in init.ora )


rajesh@ORCL> select name,value,display_value
  2  from v$parameter
  3  where name = 'audit_trail'
  4  /

NAME                 VALUE      DISPLAY_VA
-------------------- ---------- ----------
audit_trail          DB         DB

Elapsed: 00:00:00.00
rajesh@ORCL>
rajesh@ORCL>
rajesh@ORCL> create table t1(x number);

Table created.

Elapsed: 00:00:02.07
rajesh@ORCL> audit insert on t by access;

Audit succeeded.

Elapsed: 00:00:00.21
rajesh@ORCL>
rajesh@ORCL> create table t1_timing(x timestamp,y timestamp);

Table created.

Elapsed: 00:00:00.14
rajesh@ORCL>
rajesh@ORCL> create or replace procedure audit_test (p_limit in number)
  2  as
  3     p_rowid rowid;
  4  begin
  5     insert into t1_timing(x) values(systimestamp) returning rowid into p_rowid;
  6
  7     for i in 1..p_limit
  8     loop
  9             insert into t1 values (i);
 10             commit;
 11     end loop;
 12
 13     update t1_timing set y = systimestamp where rowid = p_rowid;
 14     commit;
 15  end;
 16  /

Procedure created.

Elapsed: 00:00:04.95
rajesh@ORCL>
rajesh@ORCL>
rajesh@ORCL> exec audit_test(30000);

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.15

rajesh@ORCL> select y - x from t1_timing;

Y-X
---------------------------------------------------------------------------
+000000000 00:00:02.609000

Elapsed: 00:00:00.01

Now the same approach using " do it yourself " auditing

rajesh@ORCL> create table t2(x number);

Table created.

Elapsed: 00:00:00.39
rajesh@ORCL> create table t2_audit as
  2  select sysdate as dt,
  3        a.*
  4  from v$session a
  5  where 1 = 0;

Table created.

Elapsed: 00:00:00.14
rajesh@ORCL> create or replace trigger t2_audit_trig
  2  after insert on t2
  3  for each row
  4  begin
  5     insert into t2_audit
  6     select sysdate as dt,
  7                     a.*
  8     from v$session a
  9     where sid = userenv('sid');
 10  end;
 11  /

Trigger created.

Elapsed: 00:00:00.21
rajesh@ORCL>
rajesh@ORCL> truncate table t1_timing;

Table truncated.

Elapsed: 00:00:01.96
rajesh@ORCL> create or replace procedure audit_test (p_limit in number)
  2  as
  3     p_rowid rowid;
  4  begin
  5     insert into t1_timing(x) values(systimestamp) returning rowid into p_rowid;
  6
  7     for i in 1..p_limit
  8     loop
  9             insert into t2 values (i);
 10             commit;
 11     end loop;
 12
 13     update t1_timing set y = systimestamp where rowid = p_rowid;
 14     commit;
 15  end;
 16  /

Procedure created.

Elapsed: 00:00:00.35
rajesh@ORCL>
rajesh@ORCL>
rajesh@ORCL> exec audit_test(30000);

PL/SQL procedure successfully completed.

Elapsed: 00:00:44.35
rajesh@ORCL> select y - x from t1_timing;

Y-X
---------------------------------------------------------------------------
+000000000 00:00:44.359000

Elapsed: 00:00:00.00
rajesh@ORCL>

Even for this simple example, results are clear. Its easier, faster and all around more efficient to use native functionality

Pivot in 11gR2

Oracle documentation indicates that the pivot operation is optimized, which to me implies that the optimizer has some special code available that can improve its performance. In the testing I did (admittedly not exhaustive), I saw performance improvements in execution time of the pivot over the case method that was slightly significant.

What was really interesting in these tests was that the cost of the two statements was equivalent except that the execution plan for the case method used a hash group by operation and the pivot plan used the new sort group by pivot operation. 

rajesh@11GR2>
rajesh@11GR2> drop table t purge;

Table dropped.

Elapsed: 00:00:00.06
rajesh@11GR2> create table t
  2  nologging
  3  as select * from all_objects;

Table created.

Elapsed: 00:00:06.01
rajesh@11GR2>
rajesh@11GR2> begin
  2     for i in 1..6
  3     loop
  4             insert /*+ append */ into t select * from t;
  5             commit;
  6     end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:01:28.60
rajesh@11GR2> commit;

Commit complete.

Elapsed: 00:00:00.01
rajesh@11GR2> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:24.67
rajesh@11GR2> select count(*) from T;

  COUNT(*)
----------
   4577600

Elapsed: 00:00:15.62
rajesh@11GR2>
rajesh@11GR2>
rajesh@11GR2>

********************************************************************************

select * from (
select owner,object_type,object_id
from t
)
pivot
(
    count(object_id)
    for object_type in ('PROCEDURE','DESTINATION','LIBRARY','TRIGGER','JAVA SOURCE','SEQUENCE','TABLE PARTITION','INDEX PARTITION','JOB CLASS',
        'PROGRAM','EVALUATION CONTEXT','JAVA DATA','INDEX','OPERATOR','TYPE','CONTEXT','RULE SET','JAVA CLASS','EDITION','SYNONYM',
        'PACKAGE BODY','WINDOW','RULE','DIRECTORY','TABLE','PACKAGE','VIEW','TYPE BODY','SCHEDULE','JAVA RESOURCE','XML SCHEMA',
        'MATERIALIZED VIEW','CLUSTER','FUNCTION','CONSUMER GROUP','JOB','SCHEDULER GROUP','INDEXTYPE')
)
order by owner

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      3.73      16.26      65159      65170          0          30
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      3.73      16.26      65159      65170          0          30

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91 

Rows     Row Source Operation
-------  ---------------------------------------------------
     30  VIEW  (cr=65170 pr=65159 pw=0 time=0 us cost=18054 size=412377 card=807)
     30   TRANSPOSE  (cr=65170 pr=65159 pw=0 time=0 us)
    236    SORT GROUP BY PIVOT (cr=65170 pr=65159 pw=0 time=117 us cost=18054 size=12105 card=807)
4577600     TABLE ACCESS FULL T (cr=65170 pr=65159 pw=0 time=21653452 us cost=17827 size=68664000 card=4577600)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  direct path read                              506        0.24         11.91
  asynch descriptor resize                        1        0.00          0.00
  SQL*Net message from client                     2        0.01          0.01
********************************************************************************
select owner,
       max(case when object_type = 'PROCEDURE' then cnt else null end) as PROCEDURE,
       max(case when object_type = 'DESTINATION' then cnt else null end) as DESTINATION,
       max(case when object_type = 'LIBRARY' then cnt else null end) as LIBRARY,
       max(case when object_type = 'TRIGGER' then cnt else null end) as TRIGGER_cnt,
       max(case when object_type = 'JAVA SOURCE' then cnt else null end) as JAVA_SOURCE,
       max(case when object_type = 'SEQUENCE' then cnt else null end) as SEQUENC_cntE,
       max(case when object_type = 'TABLE PARTITION' then cnt else null end) as TABLE_PARTITION,
       max(case when object_type = 'INDEX PARTITION' then cnt else null end) as INDEX_PARTITION,
       max(case when object_type = 'JOB CLASS' then cnt else null end) as JOB_CLASS,
       max(case when object_type = 'PROGRAM' then cnt else null end) as PROGRAM_cnt,
       max(case when object_type = 'EVALUATION CONTEXT' then cnt else null end) as EVALUATION_CONTEXT,
       max(case when object_type = 'JAVA DATA' then cnt else null end) as JAVA_DATA,
       max(case when object_type = 'INDEX' then cnt else null end) as INDEX_cnt,
       max(case when object_type = 'OPERATOR' then cnt else null end) as OPERATOR_cnt,
       max(case when object_type = 'TYPE' then cnt else null end) as TYPE,
       max(case when object_type = 'CONTEXT' then cnt else null end) as CONTEXT_cnt,
       max(case when object_type = 'RULE SET' then cnt else null end) as RULE_SET,
       max(case when object_type = 'JAVA CLASS' then cnt else null end) as JAVA_CLASS,
       max(case when object_type = 'EDITION' then cnt else null end) as EDITION,
       max(case when object_type = 'PACKAGE BODY' then cnt else null end) as PACKAGE_BODY,
       max(case when object_type = 'WINDOW' then cnt else null end) as WINDOW,
       max(case when object_type = 'RULE' then cnt else null end) as RULE,
       max(case when object_type = 'DIRECTORY' then cnt else null end) as DIRECTORY_cnt,
       max(case when object_type = 'TABLE' then cnt else null end) as TABLE_cnt,
       max(case when object_type = 'PACKAGE' then cnt else null end) as PACKAGE_cnt,
       max(case when object_type = 'VIEW' then cnt else null end) as VIEW_cnt,
       max(case when object_type = 'TYPE BODY' then cnt else null end) as TYPE_BODY,
       max(case when object_type = 'SCHEDULE' then cnt else null end) as SCHEDULE,
       max(case when object_type = 'JAVA RESOURCE' then cnt else null end) as JAVA_RESOURCE,
       max(case when object_type = 'XML SCHEMA' then cnt else null end) as XML_SCHEMA,
       max(case when object_type = 'MATERIALIZED VIEW' then cnt else null end) as MATERIALIZED_VIEW,
       max(case when object_type = 'CLUSTER' then cnt else null end) as CLUSTER_cnt,
       max(case when object_type = 'FUNCTION' then cnt else null end) as FUNCTION_cnt,
       max(case when object_type = 'CONSUMER GROUP' then cnt else null end) as CONSUMER_GROUP,
       max(case when object_type = 'JOB' then cnt else null end) as JOB_cnt,
       max(case when object_type = 'SCHEDULER GROUP' then cnt else null end) as SCHEDULER_GROUP,
       max(case when object_type = 'INDEXTYPE' then cnt else null end) as INDEXTYPE_cnt
from ( select owner,object_type,count(*)  as cnt
        from t
        group by owner,object_type)
group by owner
order by owner

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.04          0          0          0           0
Fetch        2      2.07      16.47      65159      65170          0          30
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      2.07      16.52      65159      65170          0          30

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91 

Rows     Row Source Operation
-------  ---------------------------------------------------
     30  SORT GROUP BY (cr=65170 pr=65159 pw=0 time=0 us cost=18054 size=900 card=30)
    236   VIEW  (cr=65170 pr=65159 pw=0 time=352 us cost=18054 size=24210 card=807)
    236    HASH GROUP BY (cr=65170 pr=65159 pw=0 time=235 us cost=18054 size=12105 card=807)
4577600     TABLE ACCESS FULL T (cr=65170 pr=65159 pw=0 time=22080070 us cost=17827 size=68664000 card=4577600)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       3        0.00          0.00
  SQL*Net message from client                     3        0.00          0.00
  direct path read                              505        0.39         13.76
  asynch descriptor resize                        2        0.00          0.00
********************************************************************************

Scalar Subquery Caching

rajesh@10GR2> create table t as select object_id,object_name,owner from all_objects;

Table created.

Elapsed: 00:00:01.50
rajesh@10GR2> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.31
rajesh@10GR2>
rajesh@10GR2> create or replace function f(x in varchar2)
  2  return number
  3  is
  4  begin
  5     dbms_application_info.set_client_info(userenv('client_info')+1);
  6     return (length(x));
  7  end f;
  8  /

Function created.

Elapsed: 00:00:00.18
rajesh@10GR2> select count(object_name),count(distinct object_name)
  2  from t;

COUNT(OBJECT_NAME) COUNT(DISTINCTOBJECT_NAME)
------------------ --------------------------
             56228                      31794

Elapsed: 00:00:00.09
rajesh@10GR2>
rajesh@10GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
rajesh@10GR2>
rajesh@10GR2> set autotrace traceonly statistics;
rajesh@10GR2> select object_name,f(object_name) as obj_length from t;

56228 rows selected.

Elapsed: 00:00:02.46

Statistics
----------------------------------------------------------
         17  recursive calls
          0  db block gets
        706  consistent gets
          0  physical reads
          0  redo size
    1337669  bytes sent via SQL*Net to client
       4510  bytes received via SQL*Net from client
        376  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      56228  rows processed

rajesh@10GR2> set autotrace off;
rajesh@10GR2>
rajesh@10GR2> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
56228

Elapsed: 00:00:00.01

As you can see the function called ONCE - PER - ROW over and over again.

rajesh@10GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@10GR2>
rajesh@10GR2> set autotrace traceonly statistics;
rajesh@10GR2> select object_name, (select f(object_name) from dual) as obj_length from T;

56228 rows selected.

Elapsed: 00:00:02.09

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        681  consistent gets
          0  physical reads
          0  redo size
    1337669  bytes sent via SQL*Net to client
       4510  bytes received via SQL*Net from client
        376  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      56228  rows processed

rajesh@10GR2> set autotrace off;
rajesh@10GR2>
rajesh@10GR2> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
40462

Now its called 40462 times - the effect of this scalar sub query caching is now apparent.

Similar demonstration using Inline views.

rajesh@10GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@10GR2>
rajesh@10GR2> set autotrace traceonly statistics;
rajesh@10GR2> select object_name,f(object_name) as obj_length from ( select object_name from t order by object_name);

56228 rows selected.

Elapsed: 00:00:02.34

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        308  consistent gets
          0  physical reads
          0  redo size
    1137252  bytes sent via SQL*Net to client
       4510  bytes received via SQL*Net from client
        376  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      56228  rows processed

rajesh@10GR2> set autotrace off;
rajesh@10GR2>
rajesh@10GR2> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
56228

Elapsed: 00:00:00.01
rajesh@10GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@10GR2>
rajesh@10GR2> set autotrace traceonly statistics;
rajesh@10GR2> select object_name, (select f(object_name) from dual) as obj_length from ( select object_name from t order by object_name);

56228 rows selected.

Elapsed: 00:00:02.14

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        308  consistent gets
          0  physical reads
          0  redo size
    1137252  bytes sent via SQL*Net to client
       4510  bytes received via SQL*Net from client
        376  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      56228  rows processed

rajesh@10GR2> set autotrace off;
rajesh@10GR2> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
40462

Elapsed: 00:00:00.03
rajesh@10GR2>

Availability and Optimization of Free Space in a Data Block

Two types of statements can increase the free space of one or more data blocks: DELETE statements, and UPDATE statements that update existing values to smaller values. The released space from these types of statements is available for subsequent INSERT statements under the following conditions:

• If the INSERT statement is in the same transaction and subsequent to the statement that frees space, then the INSERT statement can use the space made available

rajesh@10GR2> create table t
  2  as
  3  select *
  4  from all_objects
  5  where rownum <= 100;

Table created.

Elapsed: 00:00:00.10

rajesh@10GR2> begin
  2     dbms_output.put_line (' Block number Allocated for the Table ''T'' ');
  3
  4     for x in (select distinct dbms_rowid.rowid_block_number(rowid) as block_num from T)
  5     loop
  6             dbms_output.put_line (x.block_num);
  7     end loop;
  8
  9     delete from t;
 10
 11     insert into t
 12     select *
 13     from all_objects
 14     where rownum <= 100;
 15
 16     dbms_output.put_line (' Utilized block number after delete and Insert on table ''T'' ');
 17
 18     for x in (select distinct dbms_rowid.rowid_block_number(rowid) as block_num from T)
 19     loop
 20             dbms_output.put_line (x.block_num);
 21     end loop;
 22
 23  end;
 24  /

 Block number Allocated for the Table 'T'
    78365
    78364
 Utilized block number after delete and Insert on table 'T'
    78365
    78364

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09

• If the INSERT statement is in a separate transaction from the statement that frees space (perhaps being run by another user), then the INSERT statement can use the space made available only after the other transaction commits and only if the space is needed.

rajesh@10GR2> select userenv('sid') from dual;

USERENV('SID')
--------------
           144

Elapsed: 00:00:00.00
rajesh@10GR2> begin
  2     dbms_output.put_line (' Utilized block number ');
  3
  4     for x in (select distinct dbms_rowid.rowid_block_number(rowid) as block_num from T)
  5     loop
  6             dbms_output.put_line (x.block_num);
  7     end loop;
  8
  9     delete from t;
 10  end;
 11  /

Utilized block number
78368
78369
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

rajesh@10GR2> select userenv('sid') from dual;

USERENV('SID')
--------------
           149

Elapsed: 00:00:00.01

rajesh@10GR2> begin
  2     dbms_output.put_line (' Before Insert Block Utilized ');
  3
  4     for x in (select distinct dbms_rowid.rowid_block_number(rowid) as block_num from T)
  5     loop
  6             dbms_output.put_line (x.block_num);
  7     end loop;
  8
  9
 10     insert into t
 11     select *
 12     from all_objects
 13     where rownum <= 100;
 14
 15     dbms_output.put_line (' After Insert Block Utilized ');
 16
 17     for x in (select distinct dbms_rowid.rowid_block_number(rowid) as block_num from T)
 18     loop
 19             dbms_output.put_line (x.block_num);
 20     end loop;
 21
 22  end;
 23  /

 Before Insert Block Utilized
78368
78369
 After Insert Block Utilized
78368
78369
78371
78372

PL/SQL procedure successfully completed.

Instrumentation

Learnt something recently from oracle product documentation, its about setting use full session information for monitoring long running operation in v$session_longops dynamic table.

Session - 1

rajesh@ORCL> create or replace procedure p(p_limit in number)
  2  as
  3     l_rindex BINARY_INTEGER default dbms_application_info.set_session_longops_nohint;
  4     l_slno     BINARY_INTEGER default dbms_application_info.set_session_longops_nohint;
  5
  6  begin
  7     for x in 1..p_limit
  8     loop
  9             dbms_lock.sleep(x);
 10             dbms_application_info.set_session_longops(
 11                     rindex  => l_rindex,
 12                     slno    => l_slno,
 13                     op_name => 'Procedure p',
 14                     target  => p_limit,
 15                     context => x,
 16                     sofar   => x,
 17                     totalwork => p_limit,
 18                     target_desc => 'Working for procedure p',
 19                     units           => 'Seconds'
 20             );
 21     end loop;
 22  end p;
 23  /

Procedure created.

Elapsed: 00:00:00.06
rajesh@ORCL>
rajesh@ORCL>
rajesh@ORCL> select userenv('sid') from dual;

USERENV('SID')
--------------
            15

Elapsed: 00:00:00.00
rajesh@ORCL>
rajesh@ORCL>
rajesh@ORCL> exec p(10);

PL/SQL procedure successfully completed.

Elapsed: 00:00:55.06

Session - 2

rajesh@ORCL> select userenv('sid') from dual;

USERENV('SID')
--------------
             9

Elapsed: 00:00:00.00
rajesh@ORCL>
rajesh@ORCL> exec p(10);

PL/SQL procedure successfully completed.

Elapsed: 00:00:55.04

Session - 3

rajesh@ORCL> exec print_table (' select * from v$session_longops where time_remaining > 0 ');
     SID----------------- 9
     SERIAL#------------- 1565
     OPNAME-------------- Procedure p
     TARGET-------------- 10
     TARGET_DESC--------- Working for procedure p
     SOFAR--------------- 5
     TOTALWORK----------- 10
     UNITS--------------- Seconds
     START_TIME---------- 25-SEP-10
     LAST_UPDATE_TIME---- 25-SEP-10
     TIMESTAMP-----------
     TIME_REMAINING------ 13
     ELAPSED_SECONDS----- 13
     CONTEXT------------- 5
     MESSAGE------------- Procedure p: Working for procedure p 10: 5 out of 10 Seconds done
     USERNAME------------ RAJESH
     SQL_ADDRESS--------- 2576CE60
     SQL_HASH_VALUE------ 3124956440
     SQL_ID-------------- 32rn81yx4638s
     SQL_PLAN_HASH_VALUE- 0
     SQL_EXEC_START------ 25-SEP-10
     SQL_EXEC_ID--------- 16777217
     SQL_PLAN_LINE_ID----
     SQL_PLAN_OPERATION--
     SQL_PLAN_OPTIONS----
     QCSID--------------- 0

     SID----------------- 15
     SERIAL#------------- 961
     OPNAME-------------- Procedure p
     TARGET-------------- 10
     TARGET_DESC--------- Working for procedure p
     SOFAR--------------- 4
     TOTALWORK----------- 10
     UNITS--------------- Seconds
     START_TIME---------- 25-SEP-10
     LAST_UPDATE_TIME---- 25-SEP-10
     TIMESTAMP-----------
     TIME_REMAINING------ 14
     ELAPSED_SECONDS----- 9
     CONTEXT------------- 4
     MESSAGE------------- Procedure p: Working for procedure p 10: 4 out of 10 Seconds done
     USERNAME------------ RAJESH
     SQL_ADDRESS--------- 2576CE60
     SQL_HASH_VALUE------ 3124956440
     SQL_ID-------------- 32rn81yx4638s
     SQL_PLAN_HASH_VALUE- 0
     SQL_EXEC_START------ 25-SEP-10
     SQL_EXEC_ID--------- 16777218
     SQL_PLAN_LINE_ID----
     SQL_PLAN_OPERATION--
     SQL_PLAN_OPTIONS----
     QCSID--------------- 0


PL/SQL procedure successfully completed.

Blocking Session and Waiting Events in 10gR2

Now blocking sessions and waiting events can easily be monitored using additional columns added to V$session tables in 10g database.

rajesh@10GR2> create table t (x number primary key);

Table created.

Elapsed: 00:00:00.07
rajesh@10GR2>
rajesh@10GR2> select userenv('sid') from dual;

USERENV('SID')
--------------
           142

Elapsed: 00:00:00.01
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> insert into t values (1);

1 row created.

Elapsed: 00:00:00.01

Now the Session - 1 has some uncommitted transactions ( sid = 142 )

scott@10GR2> select userenv('sid') from dual;

USERENV('SID')
--------------
           149

Elapsed: 00:00:00.00
scott@10GR2>
scott@10GR2> insert into rajesh.t values (1);

Now the Session - 2  (sid = 149) is blocked by session - 1 ( sid = 142)

Now this information is clearly visible in V$session dynamic table's.

test@10GR2> exec print_table(' select * from v$session where sid = 149 ');
    ........
    ........
     PDDL_STATUS--------- ENABLED
     PQ_STATUS----------- ENABLED
     CURRENT_QUEUE_DURATI 0
     CLIENT_IDENTIFIER---
     BLOCKING_SESSION_STA VALID
     BLOCKING_INSTANCE--- 1
     BLOCKING_SESSION---- 142
     SEQ#---------------- 45
     EVENT#-------------- 183
     EVENT--------------- enq: TX - row lock contention
     P1TEXT-------------- name|mode
     P1------------------ 1415053316
     P1RAW--------------- 54580004
     P2TEXT-------------- usn<<16 | slot
     P2------------------ 655382
     P2RAW--------------- 000A0016
     P3TEXT-------------- sequence
     P3------------------ 69712
     P3RAW--------------- 00011050
     WAIT_CLASS_ID------- 4217450380
     WAIT_CLASS#--------- 1
     WAIT_CLASS---------- Application
     WAIT_TIME----------- 0
     SECONDS_IN_WAIT----- 72
     STATE--------------- WAITING
     SERVICE_NAME-------- SYS$USERS
     SQL_TRACE----------- DISABLED
     SQL_TRACE_WAITS----- FALSE
     SQL_TRACE_BINDS----- FALSE


PL/SQL procedure successfully completed.

Elapsed: 00:00:00.17
test@10GR2>

Dml error logging in 10gr2 Part II

Taking a deeper look at the LOG ERRORS option for DML in 10g Release 2 with some bad records ( where unique constraint is actually violated ).

 Physically loaded in Sorted order

test@10GR2> create table big_table_clust
  2  nologging
  3  as
  4  select rownum as cust_id,
  5         object_name as cust_name,
  6         abs(dbms_random.random) as txn_amount,
  7         to_date('01/01/1900','mm/dd/yyyy')+rownum as dob
  8  from all_objects;

Table created.

Elapsed: 00:00:08.74
test@10GR2> declare
  2     l_max_id number;
  3  begin
  4     for i in 1..2
  5     loop
  6             select max(cust_id)
  7             into l_max_id
  8             from big_table_clust;
  9
 10             insert /*+ append */ into big_table_clust (cust_id,cust_name,txn_amount,dob)
 11             select l_max_id+rownum-1, cust_name,txn_amount,dob
 12             from big_table_clust;
 13
 14             commit;
 15     end loop;
 16  end;
 17  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.76
test@10GR2>
test@10GR2>
test@10GR2> select cust_id,count(*)
  2  from big_table_clust
  3  group by cust_id
  4  having count(*) > 1;

   CUST_ID   COUNT(*)
---------- ----------
    112455          2
     56228          2

Elapsed: 00:00:00.71
test@10GR2>
test@10GR2> create table t1(
  2     x number constraint t1_pk primary key,
  3     y varchar2(30),
  4     z number constraint t1_chk check (z is not null),
  5     dt date
  6  );

Table created.

Elapsed: 00:00:00.06
test@10GR2>
test@10GR2> create table t2(
  2     x number constraint t2_pk primary key,
  3     y varchar2(30),
  4     z number constraint t2_chk check (z is not null),
  5     dt date
  6  );

Table created.

Elapsed: 00:00:00.04
test@10GR2>
test@10GR2> create table t3(
  2     x number constraint t3_pk primary key,
  3     y varchar2(30),
  4     z number constraint t3_chk check (z is not null),
  5     dt date
  6  );

Table created.

Elapsed: 00:00:00.06
test@10GR2>
test@10GR2> create table t4(
  2     x number constraint t4_pk primary key,
  3     y varchar2(30),
  4     z number constraint t4_chk check (z is not null),
  5     dt date
  6  );

Table created.

Elapsed: 00:00:00.04
test@10GR2>
test@10GR2> create table t5(
  2     x number constraint t5_pk primary key,
  3     y varchar2(30),
  4     z number constraint t5_chk check (z is not null),
  5     dt date
  6  );

Table created.

Elapsed: 00:00:00.06
test@10GR2>
test@10GR2>
test@10GR2> exec dbms_errlog.create_error_log(dml_table_name=>'T2');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.12
test@10GR2> exec dbms_errlog.create_error_log(dml_table_name=>'T4');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06
test@10GR2>
test@10GR2>
test@10GR2> alter session set timed_statistics=true;

Session altered.

Elapsed: 00:00:00.00
test@10GR2>
test@10GR2> alter session set events '10046 trace name context forever, level 12';

Session altered.

Elapsed: 00:00:01.12
test@10GR2>
test@10GR2>
test@10GR2> insert into t1 (x,y,z,dt)
  2  select cust_id, cust_name,txn_amount,dob
  3  from big_table_clust;
insert into t1 (x,y,z,dt)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.T1_PK) violated


Elapsed: 00:00:01.70
test@10GR2>
test@10GR2> commit;

Commit complete.

Elapsed: 00:00:00.01
test@10GR2>
test@10GR2> insert into t2 (x,y,z,dt)
  2  select cust_id, cust_name,txn_amount,dob
  3  from big_table_clust
  4  log errors reject limit unlimited;

224910 rows created.

Elapsed: 00:00:27.98
test@10GR2>
test@10GR2> commit;

Commit complete.

Elapsed: 00:00:00.11
test@10GR2>
test@10GR2> insert /*+ append */ into t3 (x,y,z,dt)
  2  select cust_id, cust_name,txn_amount,dob
  3  from big_table_clust;
insert /*+ append */ into t3 (x,y,z,dt)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.T3_PK) violated


Elapsed: 00:00:02.45
test@10GR2>
test@10GR2> commit;

Commit complete.

Elapsed: 00:00:00.00
test@10GR2>
test@10GR2> insert /*+ append */ into t4 (x,y,z,dt)
  2  select cust_id, cust_name,txn_amount,dob
  3  from big_table_clust
  4  log errors reject limit unlimited;
insert /*+ append */ into t4 (x,y,z,dt)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.T4_PK) violated


Elapsed: 00:00:02.74
test@10GR2>
test@10GR2> commit;

Commit complete.

Elapsed: 00:00:00.00
test@10GR2>
test@10GR2>
test@10GR2> declare
  2     cursor c1 is select * from big_table_clust;
  3     type t1 is table of c1%rowtype index by pls_integer;
  4     r1 t1;
  5     l_err_cnt number;
  6  begin
  7     open c1;
  8
  9     loop
 10             fetch c1 bulk collect into r1 limit 1000;
 11             begin
 12                     exit when r1.count = 0;
 13                             forall i in 1..r1.count save exceptions
 14                                     insert /* plsql_bulk */ into t5 values r1(i);
 15                                     exception
 16                                             when others then
 17                                                     l_err_cnt := l_err_cnt + sql%bulk_exceptions.count;
 18             end;
 19             r1.delete;
 20     end loop;
 21
 22     close c1;
 23  end;
 24  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.40
test@10GR2> commit;

Commit complete.

Elapsed: 00:00:00.03

Randomly Sorted Order

test@10GR2> create table big_table_nonclust
  2  nologging
  3  as
  4  select rownum as cust_id,
  5         object_name as cust_name,
  6         abs(dbms_random.random) as txn_amount,
  7         to_date('01/01/1900','mm/dd/yyyy')+rownum as dob
  8  from all_objects
  9  order by dbms_random.random;

Table created.

Elapsed: 00:00:09.99
test@10GR2> declare
  2     l_max_id number;
  3  begin
  4     for i in 1..2
  5     loop
  6             select max(cust_id)
  7             into l_max_id
  8             from big_table_nonclust;
  9
 10             insert /*+ append */ into big_table_nonclust (cust_id,cust_name,txn_amount,dob)
 11             select l_max_id+rownum-1, cust_name,txn_amount,dob
 12             from big_table_nonclust
 13             order by dbms_random.random;
 14
 15             commit;
 16     end loop;
 17  end;
 18  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.14
test@10GR2> create table t1(
  2     x number constraint t1_pk primary key,
  3     y varchar2(30),
  4     z number constraint t1_chk check (z is not null),
  5     dt date
  6  );

Table created.

Elapsed: 00:00:00.06
test@10GR2>
test@10GR2> create table t2(
  2     x number constraint t2_pk primary key,
  3     y varchar2(30),
  4     z number constraint t2_chk check (z is not null),
  5     dt date
  6  );

Table created.

Elapsed: 00:00:00.04
test@10GR2>
test@10GR2> create table t3(
  2     x number constraint t3_pk primary key,
  3     y varchar2(30),
  4     z number constraint t3_chk check (z is not null),
  5     dt date
  6  );

Table created.

Elapsed: 00:00:00.04
test@10GR2>
test@10GR2> create table t4(
  2     x number constraint t4_pk primary key,
  3     y varchar2(30),
  4     z number constraint t4_chk check (z is not null),
  5     dt date
  6  );

Table created.

Elapsed: 00:00:00.04
test@10GR2>
test@10GR2> create table t5(
  2     x number constraint t5_pk primary key,
  3     y varchar2(30),
  4     z number constraint t5_chk check (z is not null),
  5     dt date
  6  );

Table created.

Elapsed: 00:00:00.06
test@10GR2>
test@10GR2> exec dbms_errlog.create_error_log(dml_table_name=>'T2');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.10
test@10GR2> exec dbms_errlog.create_error_log(dml_table_name=>'T4');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09
test@10GR2>
test@10GR2>
test@10GR2> alter session set timed_statistics=true;

Session altered.

Elapsed: 00:00:00.01
test@10GR2>
test@10GR2> alter session set events '10046 trace name context forever, level 12';

Session altered.

Elapsed: 00:00:01.07
test@10GR2>
test@10GR2> insert into t1 (x,y,z,dt)
  2  select cust_id, cust_name,txn_amount,dob
  3  from big_table_nonclust;
insert into t1 (x,y,z,dt)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.T1_PK) violated


Elapsed: 00:00:04.84
test@10GR2>
test@10GR2> commit;

Commit complete.

Elapsed: 00:00:00.01
test@10GR2>
test@10GR2> insert into t2 (x,y,z,dt)
  2  select cust_id, cust_name,txn_amount,dob
  3  from big_table_nonclust
  4  log errors reject limit unlimited;

224914 rows created.

Elapsed: 00:00:22.88
test@10GR2>
test@10GR2> commit;

Commit complete.

Elapsed: 00:00:00.07
test@10GR2>
test@10GR2> insert /*+ append */ into t3 (x,y,z,dt)
  2  select cust_id, cust_name,txn_amount,dob
  3  from big_table_nonclust;
insert /*+ append */ into t3 (x,y,z,dt)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.T3_PK) violated


Elapsed: 00:00:08.06
test@10GR2>
test@10GR2> commit;

Commit complete.

Elapsed: 00:00:00.01
test@10GR2>
test@10GR2> insert /*+ append */ into t4 (x,y,z,dt)
  2  select cust_id, cust_name,txn_amount,dob
  3  from big_table_nonclust
  4  log errors reject limit unlimited;
insert /*+ append */ into t4 (x,y,z,dt)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.T4_PK) violated


Elapsed: 00:00:03.12
test@10GR2>
test@10GR2> commit;

Commit complete.

Elapsed: 00:00:00.01
test@10GR2> declare
  2     cursor c1 is select * from big_table_nonclust;
  3     type t1 is table of c1%rowtype index by pls_integer;
  4     r1 t1;
  5     l_err_cnt number;
  6  begin
  7     open c1;
  8
  9     loop
 10             fetch c1 bulk collect into r1 limit 1000;
 11             begin
 12                     exit when r1.count = 0;
 13                             forall i in 1..r1.count save exceptions
 14                                     insert /* plsql_bulk */ into t5 values r1(i);
 15                                     exception
 16                                             when others then
 17                                                     l_err_cnt := l_err_cnt + sql%bulk_exceptions.count;
 18             end;
 19             r1.delete;
 20     end loop;
 21
 22     close c1;
 23  end;
 24  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:18.34
test@10GR2> commit;

Commit complete.

Elapsed: 00:00:00.04
test@10GR2>

In short – direct path, LOG ERRORS rocks, when your data's are good especially dml error logging does not work with direct path loads and unique constraints - if you violate a unique constraint with direct path - you rollback the entire statement at the end.

Clusterd Table	CPU	Elapsed	Physical IO	Logical IO	Rows
Conventional path loading	0.31	0.41	1	9032	0
Conventional path loading With Error logging	6.37	27.6	0	706887	224910
Direct path loading	0.65	2.11	0	7301	224912
Direct path loading With Error logging	0.71	2.39	0	7344	224912
Plsql Bulk binding with SAVE Exceptions	1.06	1.47	0	31304	224910
NON - Clusterd Table	CPU	Elapsed	Physical IO	Logical IO	Rows
Conventional path loading	0.31	0.41	1	9032	0
Conventional path loading With Error logging	6.37	27.6	0	706887	224910
Direct path loading	0.65	2.11	0	7301	224912
Direct path loading With Error logging	0.71	2.39	0	7344	224912
Plsql Bulk binding with SAVE Exceptions	1.06	1.47	0	31304	224910

Dml Error logging in 10gR2

Taking a deeper look at the LOG ERRORS option for DML in 10g Release 2. Ok, so I’ve done the insert component of the testing and found something Interesting.

I set up a test to compare

    * Conventional path loading
    * Conventional path loading With Error logging
    * Direct path loading
    * Direct path loading With Error logging
    * Plsql Bulk binding with SAVE Exceptions

and how each would go.  I did it with three levels of constraints:

    * no constraints (beyond NOT NULL)
    * a check constraint
    * a primary key constraint

and in two modes:

    * direct path for the inserts (conventional of course for the row by row)
    * conventional path all around.

With data source
    * One that is physically loaded in sorted order ( Clustered table )
    * One that is randomly sorted ( non-clustered table )


Physically loaded in Sorted order

create table big_table_clust
nologging
as
select rownum as cust_id,
       object_name as cust_name,
       abs(dbms_random.random) as txn_amount,
       to_date('01/01/1900','mm/dd/yyyy')+rownum as dob
from all_objects;


declare
    l_max_id number;
begin
    for i in 1..2
    loop
        select max(cust_id)
        into l_max_id
        from big_table_clust;
       
        insert /*+ append */ into big_table_clust (cust_id,cust_name,txn_amount,dob)
        select l_max_id+rownum, cust_name,txn_amount,dob
        from big_table_clust;
       
        commit;
    end loop;
end;   
/
      
create table t1(
    x number constraint t1_pk primary key,
    y varchar2(30),
    z number constraint t1_chk check (z is not null),
    dt date
);

create table t2(
    x number constraint t2_pk primary key,
    y varchar2(30),
    z number constraint t2_chk check (z is not null),
    dt date
);

create table t3(
    x number constraint t3_pk primary key,
    y varchar2(30),
    z number constraint t3_chk check (z is not null),
    dt date
);

create table t4(
    x number constraint t4_pk primary key,
    y varchar2(30),
    z number constraint t4_chk check (z is not null),
    dt date
);

create table t5(
    x number constraint t5_pk primary key,
    y varchar2(30),
    z number constraint t5_chk check (z is not null),
    dt date
);

exec dbms_errlog.create_error_log(dml_table_name=>'T2');
exec dbms_errlog.create_error_log(dml_table_name=>'T4');

alter session set timed_statistics=true;

alter session set events '10046 trace name context forever, level 12';

insert into t1 (x,y,z,dt)
select cust_id, cust_name,txn_amount,dob
from big_table_clust;

commit;

insert into t2 (x,y,z,dt)
select cust_id, cust_name,txn_amount,dob
from big_table_clust
log errors reject limit unlimited;

commit;

insert /*+ append */ into t3 (x,y,z,dt)
select cust_id, cust_name,txn_amount,dob
from big_table_clust;

commit;

insert /*+ append */ into t4 (x,y,z,dt)
select cust_id, cust_name,txn_amount,dob
from big_table_clust
log errors reject limit unlimited;

commit;

declare
    cursor c1 is select * from big_table_clust;
    type t1 is table of c1%rowtype index by pls_integer;
    r1 t1;
    l_err_cnt number;
begin
    open c1;
   
    loop
        fetch c1 bulk collect into r1 limit 1000;
        begin
            exit when r1.count = 0;
                forall i in 1..r1.count save exceptions
                    insert /* plsql_bulk */ into t5 values r1(i);
                    exception
                        when others then
                            l_err_cnt := l_err_cnt + sql%bulk_exceptions.count;
        end;                   
        r1.delete;
    end loop;
   
    close c1;   
end;
/
commit;

Randomly Sorted Order

create table big_table_non_clust
nologging
as
select rownum as cust_id,
       object_name as cust_name,
       abs(dbms_random.random) as txn_amount,
       to_date('01/01/1900','mm/dd/yyyy')+rownum as dob
from all_objects
order by dbms_random.random;


declare
    l_max_id number;
begin
    for i in 1..2
    loop
        select max(cust_id)
        into l_max_id
        from big_table_non_clust;
       
        insert /*+ append */ into big_table_non_clust (cust_id,cust_name,txn_amount,dob)
        select l_max_id+rownum, cust_name,txn_amount,dob
        from big_table_non_clust
        order by dbms_random.random;
       
        commit;
    end loop;
end;   
/
      
create table t1(
    x number constraint t1_pk primary key,
    y varchar2(30),
    z number constraint t1_chk check (z is not null),
    dt date
);

create table t2(
    x number constraint t2_pk primary key,
    y varchar2(30),
    z number constraint t2_chk check (z is not null),
    dt date
);

create table t3(
    x number constraint t3_pk primary key,
    y varchar2(30),
    z number constraint t3_chk check (z is not null),
    dt date
);

create table t4(
    x number constraint t4_pk primary key,
    y varchar2(30),
    z number constraint t4_chk check (z is not null),
    dt date
);

create table t5(
    x number constraint t5_pk primary key,
    y varchar2(30),
    z number constraint t5_chk check (z is not null),
    dt date
);

exec dbms_errlog.create_error_log(dml_table_name=>'T2');
exec dbms_errlog.create_error_log(dml_table_name=>'T4');

alter session set timed_statistics=true;

alter session set events '10046 trace name context forever, level 12';

insert into t1 (x,y,z,dt)
select cust_id, cust_name,txn_amount,dob
from big_table_non_clust;

commit;

insert into t2 (x,y,z,dt)
select cust_id, cust_name,txn_amount,dob
from big_table_non_clust
log errors reject limit unlimited;

commit;

insert /*+ append */ into t3 (x,y,z,dt)
select cust_id, cust_name,txn_amount,dob
from big_table_non_clust;

commit;

insert /*+ append */ into t4 (x,y,z,dt)
select cust_id, cust_name,txn_amount,dob
from big_table_non_clust
log errors reject limit unlimited;

commit;

declare
    cursor c1 is select * from big_table_non_clust;
    type t1 is table of c1%rowtype index by pls_integer;
    r1 t1;
    l_err_cnt number;
begin
    open c1;
   
    loop
        fetch c1 bulk collect into r1 limit 1000;
        begin
            exit when r1.count = 0;
                forall i in 1..r1.count save exceptions
                    insert /* plsql_bulk */ into t5 values r1(i);
                    exception
                        when others then
                            l_err_cnt := l_err_cnt + sql%bulk_exceptions.count;
        end;                   
        r1.delete;
    end loop;
   
    close c1;   
end;
/
commit;

In short – direct path, LOG ERRORS rocks. details below.

Clusterd Table	CPU	Elapsed	Physical IO	Logical IO	Rows
Conventional path loading	1.21	3.55	404	32306	224756
Conventional path loading With Error logging	6.43	28.18	13	706516	224756
Direct path loading	0.67	2.62	0	15978	224756
Direct path loading With Error logging	1.17	14.79	0	16415	224756
Plsql Bulk binding with SAVE Exceptions	1.48	17.09	0	31714	224756
NON - Clusterd Table	CPU	Elapsed	Physical IO	Logical IO	Rows
Conventional path loading	3.75	6.68	451	471078	224760
Conventional path loading With Error logging	6.85	34.63	4	710078	224760
Direct path loading	1.51	3.61	0	15960	224760
Direct path loading With Error logging	1.57	3.11	0	15966	224760
Plsql Bulk binding with SAVE Exceptions	4.1	26.37	0	469459	224760