Database Reference
In-Depth Information
waited for
LGWR
once—not 100 times—due to this optimization. Does this imply that committing frequently in PL/SQL
is a good or OK idea? No, not at all—just that it is not
as bad an idea
as it is in other languages. The guiding rule is to
commit when your logical unit of work is complete—not before.
■
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.
