Mega Code Archive
Time to commit
<![CDATA[
SQL>
SQL> set echo on
SQL>
SQL> create table t
2 as
3 select * from all_objects;
Table created.
SQL>
SQL> insert into t select * from t;
12210 rows created.
SQL> insert into t select * from t;
24420 rows created.
SQL> insert into t select * from t where rownum < 12000;
11999 rows created.
SQL> insert into t select * from t;
60839 rows created.
SQL> insert into t select * from t;
121678 rows created.
SQL> insert into t select * from t;
243356 rows created.
SQL> insert into t select * from t;
486712 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> create or replace procedure do_commit( p_rows in number )
2 as
3 l_start number;
4 l_after_redo number;
5 l_before_redo number;
6 begin
7 l_start := dbms_utility.get_time;
8 insert into t select * from t where rownum < p_rows;
9 dbms_output.put_line( sql%rowcount || ' rows created' );
10 dbms_output.put_line ( 'Time to INSERT: ' ||to_char( round( (dbms_utility.get_time-l_start)/100, 5 ), '999.99') || ' seconds' );
11
12 l_start := dbms_utility.get_time;
13 commit;
14 dbms_output.put_line( 'Time to COMMIT: ' ||to_char( round( (dbms_utility.get_time-l_start)/100, 5 ), '999.99') || ' seconds' );
15
16 dbms_output.put_line( 'Generated ' || to_char(l_after_redo-l_before_redo,'999,999,999,999') || ' bytes of redo' );
17 dbms_output.new_line;
18 end;
19 /
Procedure created.
SQL> show error
No errors.
SQL>
SQL> set serveroutput on format wrapped
SQL> begin
2 for i in 1 .. 5
3 loop
4 do_commit( power(10,i) );
5 end loop;
6 end;
7 /
9 rows created
Time to INSERT: .06 seconds
Time to COMMIT: .00 seconds
Generated bytes of redo
99 rows created
Time to INSERT: .00 seconds
Time to COMMIT: .00 seconds
Generated bytes of redo
999 rows created
Time to INSERT: .02 seconds
Time to COMMIT: .00 seconds
Generated bytes of redo
9999 rows created
Time to INSERT: .31 seconds
Time to COMMIT: .00 seconds
Generated bytes of redo
99999 rows created
Time to INSERT: .80 seconds
Time to COMMIT: .00 seconds
Generated bytes of redo
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL> --
]]>