Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Oracle Development - Best Practice
Tim,
Nice testing - but here's a thought to make you worry.
When you update a row in a table, Oracle records some information about the previous version in an undo block.
When Oracle changes a buffered block, it first gets it in current mode.
To change a row, you change (at least) two blocks, the block holding the row, and the block where you're going to put the undo.
Your example (second insert) has autotrace
reporting ONE db block get ? Is that the
current get on the table block , or the
current get on the undo ?
How much do you trust autotrace ?
There is a difference in costs between
before and after, by the way, but the
degree of difference has varied with
version of Oracle, and the type of
operation. Bear in mind that triggers
turn array processes into multiple
single processes, and before row triggers
(seem to) have to be aware of the current
value of the row before they fire.
Set up a table with a couple of thousand
rows, then do a simple array update on
the table with and without a before/after
row trigger that does
begin
null;
end;
On my 9.2.0.4 system, the after row trigger adds no buffer or redo costs; the before row trigger adds a couple of thousand logical I/Os, and a couple of thousand redo records.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
Next public appearances:
March 2004 Hotsos Symposium - The Burden of Proof
March 2004 Charlotte NC OUG - CBO Tutorial
April 2004 Iceland
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February
____UK___June
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The reason I asked for proof is because, when I saw the assertion, I didnšt
like it. So, I set about proving it with a quick little test. Then, I
replied asking for proof, because I may have misunderstood the assertion and
missed the point.
So, here was my proof...
>
> SQL> insert into x values (2,2);
>
> 1 row created.
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 1 db block gets
> 1 consistent gets
> 0 physical reads
> 240 redo size
> 623 bytes sent via SQL*Net to client
> 526 bytes received via SQL*Net from client
> 3 SQL*Net roundtrips to/from client
> 1 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Sun Feb 08 2004 - 04:27:26 CST
![]() |
![]() |