Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Oracle Development - Best Practice

Re: Oracle Development - Best Practice

From: Tim Gorman <>
Date: Sun, 08 Feb 2004 14:37:12 -0700
Message-ID: <>

I've been worried all along :-) , that's why I posted this description of my testing, because I may have missed or misunderstood something...


When you ask how much I trust AUTOTRACE, we have to remember that it is only
a before/after query of V$SESSTAT for ten specific statistics.  So, if I
trust AUTOTRACE, I am trusting:

   * the information in V$SESSTAT
   * that these ten specific statistics are the worthwhile ones
   * how much collection error is the mechanism introducing

So, the question evolves to:  where exactly is my trust misplaced?

* If it is the first point (i.e. validity of information in V$SESSTAT)...

>> To clarify further from my previous post, I had supposed that the single "db
>> block get" was to update the undo block and the single "consistent get" was
>> to update the table/data block.  When I change the test to commit after each
>> insert, then the number of "db block gets" increases from 1 to 2, which makes
>> sense if the transaction table in the undo header block is to be modified to
>> begin the transaction.  This is proven easily by adjusting the test slightly
>> to introduce more commits.  So, unless this accounting for the logical reads
>> recorded in V$SESSTAT is incorrect, I think my trust on the first point is
>> not misplaced...

* If the question is the second point (i.e. Specific statistics gathered by

>> The original poster indicated that the problem with BEFORE triggers was
>> visible via logical reads, so since AUTOTRACE displays a couple of those
>> statistics (i.e. "db block gets" and "consistent gets"), it seemed a safe bet
>> to utilize AUTOTRACE.  So, my trust on the second point seems valid, at least
>> when questioning this particular assertion...

* That leaves the third point, of measurement error, to which I have no
answer, but as a skeptic I take as a given to some degree anyway...

So, unless there's a flaw in this reasoning (and I'm not saying there
isn't!), it seems like AUTOTRACE is worthy of trust for this testing.


However, on the chance that it isn't, I chose to utilize Mogen's suggestion
of the "Ensor Test" (i.e. bang the heck out of it in a long loop, which
effectively aggregates all possible effects).  I tried the three test
scenarios successively:

* table without any triggers
* table with BEFORE INSERT FOR EACH ROW trigger, does NULL action
* table with AFTER INSERT FOR EACH ROW trigger, does NULL action

Within each of these scenarios, I ran a simple PL/SQL loop that performed an
INSERT for 100,000 iterations.  This loop was run ten times, with a TRUNCATE
TABLE ... REUSE STORAGE prior to each, then the elapsed times were averaged
(elapsed times gathered by SQL*Plus SET TIMING ON mechanism).

The averaged timing results:

* table without any triggers, avg ela:  163.06 secs
* table with BEFORE INSERT trigger, avg ela: 207.73 secs
* table with AFTER INSERT trigger, avg ela: 205.95 secs

The difference between the BEFORE INSERT and AFTER INSERT scenario average
timings was 1.78 seconds or well under 1% of the total.  I'm not a
statistician, so I guess I'll leave it up to y'all to decide if it is
statistically significant or not?  My gut feel is that it is not


So, I still see no substantiation to the assertion that BEFORE triggers
perform worse than AFTER triggers.

Hope this helps,


on 2/8/04 3:27 AM, Jonathan Lewis at wrote:

> 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 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
> 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:
> Three-day seminar:
> see
> ____UK___February
> ____UK___June
> The Co-operative Oracle Users' FAQ
> ----- Original Message -----
> From: "Tim Gorman" <>
> To: <>
> 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:
> ----------------------------------------------------------------
> To unsubscribe send email to:
> put 'unsubscribe' in the subject line.
> --
> Archives are at
> FAQ is at
> -----------------------------------------------------------------
---------------------------------------------------------------- Please see the official ORACLE-L FAQ: ---------------------------------------------------------------- To unsubscribe send email to: put 'unsubscribe' in the subject line. -- Archives are at FAQ is at -----------------------------------------------------------------
Received on Sun Feb 08 2004 - 15:37:12 CST

Original text of this message