Re: autotrace

Prasath wrote:
> Why does the statistics report in the explain plan take more time to be
> generated?
>
> When I use "set autotrace traceonly explain", it takes zero seconds for
> the plan to be generated. If the autotrace is set to "set autotrace
> traceonly explain statistics" then it takes 14 seconds. Any idea why
> this happens?
>
>
> SQL> desc test_p
> Name Null? Type
> ----------------------------------------- -------- -----------------
> MYCOL1 NUMBER
>
> SQL> truncate table test_p;
>
> Table truncated.
>
> Elapsed: 00:00:00.00
> SQL> set autotrace traceonly explain
> SQL> select * from test_p;
> Elapsed: 00:00:00.00
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 TABLE ACCESS (FULL) OF 'TEST_P'
>
>
>
> SQL> set autotrace traceonly explain statistics
> SQL> /
>
> no rows selected
>
> Elapsed: 00:00:14.04
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 TABLE ACCESS (FULL) OF 'TEST_P'
>
>
>
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 3 consistent gets
> 0 physical reads
> 0 redo size
> 140 bytes sent via SQL*Net to client
> 232 bytes received via SQL*Net from client
> 1 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 0 rows processed

If you would think one sec about it, you would realize those statistics need to be SELECTed from your session statistics. Obviously this takes time.

-- 
Sybrand Bakker
Senior Oracle DBA