Re: Oracle Development - Best Practice

Tim,

The question on trust was slightly tongue in cheek, and slightly rhetorical. There is one other possible error though. Although autotrace clearly selects from v$sesstat, does it tell you what it's selected, or does it massage it first ? I get the impression that it is adjusting the CUR gets before reporting them, which is partly why I gave up on it some time ago and just run a before/after snapshot of the correct v$sesstat set from another session when I want to be picky.

If you commit after each insert, I would have predicted four CUR gets.

    One          seg header to change transaction table slot
    Two         undo block to record undo record
    Three       table block to make change
    Four         seg header to clear transaction table slot
and possibly
    Five         because commit cleanout should take place
                   if all you've changed is one user-data block.

But I'm not sure that commit cleanout uses CUR gets - or maybe it does, but forgets to record them. I'll have to run some tests.

Autotrace is very devious about not introducing measurement error, by the way. I don't know when it happened, but in recent versions your SQL*Plus connection starts a second session to query the v$sesstat for the first session - so whatever work is done taking the stats does not get included in the stats.

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 9:37 PM Subject: Re: Oracle Development - Best Practice

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




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