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: Jonathan Lewis <>
Date: Mon, 9 Feb 2004 19:59:52 -0000
Message-ID: <01ed01c3ef47$5c80d790$6702a8c0@Primary>


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.


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

The Co-operative Oracle Users' FAQ

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: ---------------------------------------------------------------- To unsubscribe send email to: put 'unsubscribe' in the subject line. -- Archives are at FAQ is at -----------------------------------------------------------------
Received on Mon Feb 09 2004 - 13:59:52 CST

Original text of this message