Re: Conceivably a daft question...

"VC" <boston103_at_hotmail.com> wrote in message news:gGTac.151673$1p.1962772_at_attbi_s54...
> Hello Roger,
>
> The phenomenon you are experiencing appears to be a sqlplus bug rather
than
> an Oracle bug.
>
> The sequence triggering the bug seems to be:
>
> sqlplus /nolog
> connect hr/hr
> connect / as sysdba
> shutdown
> startup
> connect hr/hr
> set autot on
>
>
> Consider this:
>
> SQL*Plus: Release 9.2.0.4.0 - Production on Thu Apr 1 07:01:23 2004
>
> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
> SQL> connect hr/hr
> Connected.
> SQL> connect / as sysdba
> Connected.
> SQL> shutdown immediate
> Database closed.
> Database dismounted.
> ORACLE instance shut down.
> SQL> startup
> ORACLE instance started.
>
> Database mounted.
> Database opened.
> SQL> connect hr/hr
> Connected.
> SQL> select * from v$mystat where rownum=1;
>
> SID STATISTIC# VALUE
> ---------- ---------- ----------
> 9 0 1
>
> SQL> set autot on
> SQL> select * from v$mystat where rownum=1;
>
> SID STATISTIC# VALUE
> ---------- ---------- ----------
> 14 0 1
> <--- that's where the bug happens
>
>
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 0 consistent gets
> 0 physical reads
> 0 redo size
> 0 bytes sent via SQL*Net to client
> 0 bytes received via SQL*Net from client
> 0 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
> SQL>
>
> The probllem is caused by the fact that sqlplus opens a new session to
> obtain statistics deltas using a statement like this:
>
> SELECT b.name, VALUE
> FROM SYS.V_$SESSTAT a,
> SYS.V_$STATNAME b
> WHERE SID=:SID AND a.STATISTIC# IN (7,
> 40,41,42,115,236,237,238,242,243)
> and a.STATISTIC#=b.STATISTIC#
>
> The actual statement is supposed to run in the original session (9) but,
in
> fact, it runs in the same session as the autotrace statement (14) whilst
the
> autotrace select queries the original session (9) statistics getting,
> naturally, zero deltas.
>
> The correct sequence should be like this:
>
> sqlplus /nolog
>
> SQL*Plus: Release 9.2.0.4.0 - Production on Thu Apr 1 07:03:29 2004
>
> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
> SQL> connect hr/hr
> Connected.
> SQL> select * from v$mystat where rownum=1;
>
> SID STATISTIC# VALUE
> ---------- ---------- ----------
> 15 0 1
>
> SQL> set autot on
> SQL> select * from v$mystat where rownum=1;
>
> SID STATISTIC# VALUE
> ---------- ---------- ----------
> 15 0 1
>
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 0 consistent gets
> 0 physical reads
> 0 redo size
> 490 bytes sent via SQL*Net to client
> 499 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
> SQL>
>
> Here, the statement runs in the original session (15) wjilst the
autotrace
> runs elsewhere.
>
> Rgds.
>

It's late so I'll have to read this more slowly tomorrow, but you certainly seem to have hit the nail on the head here. Is this a known bug, recorded and logged or such? Should it be?
Would someone do the honours if so?
Regards
HJR

-- 
-------------------------------------------
Dizwell Informatics: http://www.dizwell.com
  -A mine of useful Oracle information-
===========================================