RE: normalization
- From: "Steve Adams" <steve.adams@xxxxxxxxxxxx>
- To: <davewendelken@xxxxxxxxxxxxx>
- Date: Wed, 2 Nov 2005 14:38:46 +1100
Hi David,
The difference can be demonstrated with a small test.
This is 10.2.0 with _in_memory_undo = false to show the redo.
SQL> connect test/test
Connected.
SQL> create table test (key number);
SQL> insert into test (key) values (1);
SQL> create trigger test before update on test for each row begin null;
end;
2 /
SQL> connect test/test
Connected.
SQL> update test set key = key + 1;
SQL> select n.name, m.value from sys.v_$mystat m, sys.v_$statname n
2 where m.value > 0 and n.statistic# = m.statistic# and
bitand(n.class, 2) > 0;
NAME
VALUE
----------------------------------------------------------------
----------
redo entries
2
redo size
632
SQL> drop table test;
SQL> create table test (key number);
SQL> insert into test (key) values (1);
SQL> create trigger test after update on test for each row begin null;
end;
2 /
SQL> connect test/test
Connected.
SQL> update test set key = key + 1;
SQL> select n.name, m.value from sys.v_$mystat m, sys.v_$statname n
2 where m.value > 0 and n.statistic# = m.statistic# and
bitand(n.class, 2) > 0;
NAME
VALUE
----------------------------------------------------------------
----------
redo entries
1
redo size
432
SQL>
We got 2 redo records for the BEFORE trigger (but only 1 for the AFTER trigger).
An extra redo record was needed because the row was locked separately before the
application of the change vectors for the update.
This may look trivial, but it is a potential scalability issue in a busy OLTP
environment. The extra current mode buffer also has a non-trivial impact on
scalability and CPU usage.
@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/ - For DBAs
@ http://www.christianity.net.au/ - For all
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx On Behalf Of David Wendelken
Sent: Wednesday, 2 November 2005 1:45 PM
To: 'Oracle-L Freelists'
Subject: RE: normalization
>BEFORE ROW triggers also need to lock each target row, even if
>nothing is done. This doubles the number of current mode
>buffer gets and increases redo generation significantly.
Given that the reason the before row trigger fires is because we are FOR
SURE about to change the row, I would expect that we have to pay for the
lock ANYWAY...
--
//www.freelists.org/webpage/oracle-l
Other related posts:
