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>
Thursday, September 30, 2010
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>
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>
Wednesday, September 29, 2010
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
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
Tuesday, September 28, 2010
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
********************************************************************************
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>
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>
Sunday, September 26, 2010
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:
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
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.
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.
Saturday, September 25, 2010
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.
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.
Thursday, September 23, 2010
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>
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.
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 |
Tuesday, September 21, 2010
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.
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 |
Subscribe to:
Posts (Atom)