Re: Oracle Development - Best Practice
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 8 Feb 2004 10:27:26 -0000
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
----- Original Message -----
From: "Tim Gorman" <tim@xxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Sunday, February 08, 2004 5:12 AM
Subject: Re: Oracle Development - Best Practice
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
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
