Re: Conceivably a daft question...

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Wed, 31 Mar 2004 14:18:42 +0200
Message-ID: <c4ecv3$fo7$1@news.BelWue.DE>

Howard J. Rogers wrote:
>
> That can't be it, because:
>
> C:\Documents and Settings\Howard>sqlplus "/ as sysdba"
>
> SQL*Plus: Release 9.2.0.1.0 - Production on Wed Mar 31 20:28:50 2004
>
> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
>
> Connected to:
> Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
> With the Partitioning, OLAP and Oracle Data Mining options
> JServer Release 9.2.0.1.0 - Production
>
> SQL> connect howard/rogers
> Connected.
> SQL> set autotrace trace stat
> SQL> select * from blah;
>
> 999 rows selected.
>
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 97 consistent gets
> 0 physical reads
> 0 redo size
> 84390 bytes sent via SQL*Net to client
> 1225 bytes received via SQL*Net from client
> 68 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 999 rows processed
>
> See? Now it works. It's when it's run in a script that it doesn't.

Sorry, I missed your point - and the funny thing is, after I run your script I'm no longer able to retreive statistics at all without exiting sqlplus.

And it get's even better: the following script gives at first the correct stats, after the shutdown/startup no stats until I exit sql*plus.

So let's call it a bug, shall we?

connect / as sysdba

drop user howard cascade;
create user howard identified by rogers
default tablespace users
temporary tablespace temp;

grant dba to howard;

connect howard/rogers
@?/rdbms/admin/utlxplan
create table blah
as select * from dba_objects where rownum < 1000; set timing on
set autotrace trace stat
select * from blah;

connect / as sysdba
shutdown immediate
startup

connect / as sysdba
spool blocktestrun.txt
alter system checkpoint;

connect howard/rogers
--@?/rdbms/admin/utlxplan

set timing on
set autotrace trace stat
select * from blah;

and the output is:

SQL> @test
Connect durchgeführt.

Benutzer wurde gelöscht.

Benutzer wurde angelegt.

Benutzerzugriff (Grant) wurde erteilt.

Connect durchgeführt.

Tabelle wurde angelegt.

999 Zeilen ausgewählt.

Abgelaufen: 00:00:00.36

Statistiken

           0  recursive calls
           0  db block gets
          84  consistent gets
          17  physical reads
           0  redo size
       66409  bytes sent via SQL*Net to client
        1229  bytes received via SQL*Net from client
          68  SQL*Net roundtrips to/from client
           0  sorts (memory)
           0  sorts (disk)
         999  rows processed

Connect durchgeführt.
Datenbank geschlossen.
Datenbank abgehängt.
ORACLE-Instanz heruntergefahren.
ORACLE-Instanz hochgefahren.

Total System Global Area 420550020 bytes

Fixed Size                   451972 bytes
Variable Size             369098752 bytes
Database Buffers           50331648 bytes
Redo Buffers                 667648 bytes

Datenbank mit MOUNT angeschlossen.
Datenbank geöffnet.
Connect durchgeführt.

System wurde geändert.

Abgelaufen: 00:00:00.07
Connect durchgeführt.

999 Zeilen ausgewählt.

Abgelaufen: 00:00:00.26

Statistiken

           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)
         999  rows processed

SQL> set autotrace trace stat
SQL> select * from blah;

999 Zeilen ausgewählt.

Abgelaufen: 00:00:00.26

Statistiken

           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)
         999  rows processed

SQL> Note that I did a select * from blah; in sql*plus with the same result.

My guess is, that the shutdown/startup sequence plays havoc with the tracing feature. And it's still the same with 10g.

Cheers,

Holger Received on Wed Mar 31 2004 - 06:18:42 CST