Re: autotrace not showing statistics

On Mar 30, 2:26 pm, "sybrandb" <sybra..._at_gmail.com> wrote:
> On Mar 30, 11:00 am, "peter" <hhh.datab..._at_gmail.com> wrote:
>
>
>
>
>
> > SQL> exec dbms_stats.gather_table_stats('SYS', 'PLAN_TABLE',
> > METHOD_OPT=>'FOR ALL COLUMNS');
>
> > PL/SQL procedure successfully completed.
>
> > SQL> set autotrace traceonly explain statistics
> > SQL> select * from plan_table;
>
> > 2268 rows selected.
>
> > Execution Plan
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=2268 Bytes=25
> > 4016)
>
> > 1 0 TABLE ACCESS (FULL) OF 'PLAN_TABLE' (Cost=6 Card=2268 Byte
> > s=254016)
>
> > 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)
> > 2268 rows processed
>
> > SQL> SET AUTOTRACE OFF
> > SQL> SHOW PARAMETER STATISTIC
>
> > NAME TYPE VALUE
> > ------------------------------------ -----------
> > ------------------------------
> > statistics_level string ALL
> > timed_os_statistics integer 5
> > timed_statistics boolean TRUE
> > SQL> SHOW PARAMETER TIMED_STATI
>
> > NAME TYPE VALUE
> > ------------------------------------ -----------
> > ------------------------------
> > timed_statistics boolean TRUE
>
> > oracle database : 9.2.0.8
> > OS : Windows XP
>
> > Could anyone please let me what can be problem, why I cann't see the
> > statistics here.
>
> Apparently you need glasses. I see
>
> > 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)
> > 2268 rows processed
>
> Look at the first line, carefully. What does it read?
>
> --
> Sybrand Bakker
> Senior Oracle DBA- Hide quoted text -
>
> - Show quoted text -

Which first line are you talking about. It is not working for any query. But in trace file I can see all statistics.

SQL> set autotrace on
SQL> select count(1) from mcc_catalog.tc;

  COUNT(1)

    734850

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=29 Card=1)    1 0 SORT (AGGREGATE)

   2    1     BITMAP CONVERSION (COUNT)
   3    2       BITMAP INDEX (FAST FULL SCAN) OF 'IND_VISIBLE'

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