Re: Oracle Development - Best Practice
- From: Tim Gorman <tim@xxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 08 Feb 2004 14:37:12 -0700
Jonathan,
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
AUTOTRACE)...
>> 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
significant.
---
So, I still see no substantiation to the assertion that BEFORE triggers
perform worse than AFTER triggers.
Hope this helps,
-Tim
on 2/8/04 3:27 AM, Jonathan Lewis at jonathan@xxxxxxxxxxxxxxxxxx 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 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
> -----------------------------------------------------------------
----------------------------------------------------------------
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:
