time to commit : dbms_utility « System Packages « Oracle PL / SQL

1. Oracle PL / SQL
2. System Packages
3. dbms_utility

time to commit

   
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> --

   
    
  

Related examples in the same category