Re: Conceivably a daft question...

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.

VC Received on Thu Apr 01 2004 - 06:11:56 CST