RE: normalization

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_at_freelists.org 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...

--
http://www.freelists.org/webpage/oracle-l