Redo and Undo - Expert Oracle Database Architecture - page 318

■ directions on how to create and populate the BIG_TABLE table used in many examples is in the “Setting Up
Your environment” section at the very front of this topic.

Note

And we'll measure the CPU and Elapsed time used to commit our transaction using the DBMS_UTILITY package
routines GET_CPU_TIME and GET_TIME . The actual PL/SQL block used to generate the workload and report on it is:

EODA@ORA12CR1> declare
2 l_redo number;
3 l_cpu number;
4 l_ela number;
5 begin
6 dbms_output.put_line
7 ( '-' || ' Rows' || ' Redo' ||
8 ' CPU' || ' Elapsed' );
9 for i in 1 .. 6
10 loop
11 l_redo := get_stat_val( 'redo size' );
12 insert into t select * from big_table where rownum <= power(10,i);
13 l_cpu := dbms_utility.get_cpu_time;
14 l_ela := dbms_utility.get_time;
15 commit work write wait;
16 dbms_output.put_line
17 ( '-' ||
18 to_char( power( 10, i ), '9,999,999') ||
19 to_char( (get_stat_val('redo size')-l_redo), '999,999,999' ) ||
20 to_char( (dbms_utility.get_cpu_time-l_cpu), '999,999' ) ||
21 to_char( (dbms_utility.get_time-l_ela), '999,999' ) );
22 end loop;
23 end;
24 /

- Rows Redo CPU Elapsed
- 10 7,072 0 1
- 100 10,248 0 0
- 1,000 114,080 0 0
- 10,000 1,146,484 0 2
- 100,000 11,368,512 0 2
- 1,000,000 113,800,488 1 2

PL/SQL procedure successfully completed.

* This test was performed on a single-user machine with a 1.7 MB log buffer and three 500MB
online redo log files. Times are in hundredths of seconds.

As you can see, as we generate varying amount of redo from 7,072 bytes to 113MB, the difference in time to
COMMIT is not measurable using a timer with a one hundredth of a second resolution. As we were processing and
generating the redo log, LGWR was constantly flushing our buffered redo information to disk in the background. So,
when we generated 113MB of redo log information, LGWR was busy flushing every 1MB, or so. When it came to the
COMMIT , there wasn't much left to do—not much more than when we created ten rows of data. You should expect to
see similar (but not exactly the same) results, regardless of the amount of redo generated.