Re: Conceivably a daft question...

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 31 Mar 2004 12:59:07 +0000 (UTC)
Message-ID: <c4efar$e1a$1@titan.btinternet.com>

Based on 9.2.0.1 - which is all I have in front of me at the moment - I've switch sql_trace on globally.

connect -- I get a connection and session 11

set autotrace trace statistics

I see (with lots of bits cut out)

• SESSION ID:(11.7) 2004-03-31 13:45:03.000

SELECT SID FROM V$SESSION WHERE USERENV('SESSIONID')=AUDSID PARSE
#1:c=0,e=2380,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=4,tim=18446742428539406704 EXEC #1:c=0,e=30,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=18446742428539406930 FETCH
#1:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=2,dep=0,og=4,tim=18446742428539407020

• SESSION ID:(12.8) 2004-03-31 13:45:36.000

SELECT STATISTIC# S, NAME FROM SYS.V_$STATNAME WHERE NAME IN ('recursive calls','db block gets','consistent gets','physical reads','redo size','bytes sent via SQL*Net to client','bytes received via SQL*Net from client','SQL*Net roundtrips to/from client','sorts (memory)','sorts (disk)') ORDER BY S SELECT PT.VALUE FROM SYS.V_$SESSTAT PT WHERE PT.SID=:1 AND PT.STATISTIC# IN (7,40,41,42,115,236,237,238,242,243) ORDER BY PT.STATISTIC#

• SESSION ID:(11.7) 2004-03-31 13:45:45.000

select count(*) from tab$ -- my test query

• SESSION ID:(12.8) 2004-03-31 13:45:45.000

SELECT PT.VALUE FROM SYS.V_$SESSTAT PT WHERE PT.SID=:1 AND PT.STATISTIC# IN (7,40,41,42,115,236,237,238,242,243) ORDER BY PT.STATISTIC#

• SESSION ID:(11.7) 2004-03-31 13:45:57.000

.... rest of trace file where I did a simple EXIT

This looks like:

    In current session I try to find my SID (and get the wrong one probably)     I switch to a new session to collect statistic numbers     then check the original sessions stats

    I switch back to run a query

    I switch to the new session to get the statistics again

        and presumably work out the differences

    I switch back

The rows processed comes our right all the time because it isn't from v$sesstat

I'll check it again on 9.2.0.4 and 10.1 if I get a chance. There are all sorts of odd little glitches in autotrace, so I guess they're being fixed over time.

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

April 2004 Iceland  http://www.index.is/oracleday.php
June  2004      UK - Optimising Oracle Seminar


"srivenu" <srivenu_at_hotmail.com> wrote in message
news:1a68177.0403310442.c932756_at_posting.google.com...


> Jonathan,

> Are you sure that the tracing session uses the AUDSID to find out the

> first session ?

> If i issue SET AUTOTRACE TRACE STATISTICS

>

> Because on my local 9.2.0.3.0 i traced the sessions and saw that the

> SID is passed to the tracing session using this SQL

> SELECT DISTINCT SID FROM V$MYSTAT

> Then using this SID, the tracing session is using this SQL to get the

> statistics about the traced session.

> SELECT STATISTIC# S, NAME

> FROM SYS.V_$STATNAME

> WHERE NAME IN ('recursive calls','db block gets','consistent

> gets','physical reads','redo size','bytes sent via SQL*Net to

> client','bytes received via SQL*Net from client','SQL*Net roundtrips

> to/from client','sorts (memory)','sorts (disk)') ORDER BY S

>

> Whereas if i issue SET AUTOTRACE TRACE EXPLAIN

> it is issuing this SQL

> SELECT USERENV('SESSIONID') FROM DUAL

>

> Can you please check it out.

> regards

> Srivenu

Received on Wed Mar 31 2004 - 06:59:07 CST