Re: Oracle Development - Best Practice

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sun, 8 Feb 2004 10:27:26 -0000
Message-ID: <027301c3ee2e$27ee49a0$6702a8c0@Primary>

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_at_sagelogix.com> To: <oracle-l_at_freelists.org> 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_at_freelists.org put 'unsubscribe' in the subject line.

--
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