Redo and Undo - Oracle Database Transactions and Locking Revealed - page 120

■ This commit-time optimization in PL/SQL may be suspended when you are performing distributed transactions
or data Guard in maximum availability mode. Since there are two participants, PL/SQL must wait for the commit to
actually be complete before continuing. also, it can be suspended by directly invoking COMMIT WORK WRITE WAIT in
PL/SQL with database version oracle 11 g release 1 and above.

Note

To demonstrate that a COMMIT is a “flat response time” operation, we'll generate varying amounts of redo and
time the INSERT s and COMMIT s. As we do these INSERT s and COMMIT s, we'll measure the amount of redo our session
generates using this small utility function:

EODA@ORA12CR1> create or replace function get_stat_val( p_name in varchar2 ) return number
2 as
3 l_val number;
4 begin
5 select b.value
6 into l_val
7 from v$statname a, v$mystat b
8 where a.statistic# = b.statistic#
9 and a.name = p_name;
10
11 return l_val;
12 end;
13 /
Function created.

■ The owner of the previous function will need to have been directly granted the SELECT privilege on the V$ views
V_$STATNAME and V_$MYSTAT .

Note

Drop the table T (if it exists) and create an empty table T of the same structure as BIG_TABLE :

EODA@ORA12CR1> drop table t purge;

EODA@ORA12CR1> create table t
2 as
3 select *
4 from big_table
5 where 1=0;
Table created.

■ 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