RE: statistics_level='ALL' with 10046, level 8 : Cost/Benefit Analysis

Here is one specific example I just encountered - this is a recursive
query that Oracle calls when you execute the dbms_session.set_role
procedure - this is on 10.2.0.4, on the exact same system, exact same
query, exact same explain plan, exact same number of logical and
physical reads, no other activity on the server - the only difference is
statistics_level = typical vs. all, and you can see the execution time
increases 20x!

STATISTICS_LEVEL=TYPICAL:
************************************************************************
********

select max(nvl(option$,0))
from
 sysauth$ where privilege#=:1 connect by grantee#=prior privilege# and
  privilege#>0 start with (grantee#=:2 or grantee#=1) and privilege#>0
group
  by privilege#


call     count       cpu    elapsed       disk      query    current
rows
------- ------  -------- ---------- ---------- ---------- ----------
----------
Parse      242      0.00       0.00          0          0          0
0
Execute    242      0.03       0.02          0          0          0
0
Fetch      242      0.98       0.96          0      11616          0
242
------- ------  -------- ---------- ---------- ---------- ----------
----------
total      726      1.02       0.99          0      11616          0
242

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT GROUP BY (cr=48 pr=0 pw=0 time=9523 us)
      1   FILTER  (cr=48 pr=0 pw=0 time=9405 us)
    367    CONNECT BY WITH FILTERING (cr=48 pr=0 pw=0 time=10555 us)
     14     TABLE ACCESS BY INDEX ROWID SYSAUTH$ (cr=12 pr=0 pw=0
time=62 us)
     14      INLIST ITERATOR  (cr=4 pr=0 pw=0 time=4672 us)
     14       INDEX RANGE SCAN I_SYSAUTH1 (cr=4 pr=0 pw=0 time=65
us)(object id 105)
    353     HASH JOIN  (cr=36 pr=0 pw=0 time=7045 us)
    326      CONNECT BY PUMP  (cr=0 pr=0 pw=0 time=13561 us)
   9680      TABLE ACCESS FULL SYSAUTH$ (cr=36 pr=0 pw=0 time=10221 us)

************************************************************************
********

STATISTICS_LEVEL=ALL:
************************************************************************
********

select max(nvl(option$,0))
from
 sysauth$ where privilege#=:1 connect by grantee#=prior privilege# and
  privilege#>0 start with (grantee#=:2 or grantee#=1) and privilege#>0
group
  by privilege#


call     count       cpu    elapsed       disk      query    current
rows
------- ------  -------- ---------- ---------- ---------- ----------
----------
Parse      242      0.00       0.00          0          0          0
0
Execute    242      0.02       0.02          0          0          0
0
Fetch      242     20.67      20.19          0      11616          0
242
------- ------  -------- ---------- ---------- ---------- ----------
----------
total      726     20.70      20.22          0      11616          0
242

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT GROUP BY (cr=48 pr=0 pw=0 time=86260 us)
      1   FILTER  (cr=48 pr=0 pw=0 time=86221 us)
    367    CONNECT BY WITH FILTERING (cr=48 pr=0 pw=0 time=85349 us)
     14     TABLE ACCESS BY INDEX ROWID SYSAUTH$ (cr=12 pr=0 pw=0
time=246 us)
     14      INLIST ITERATOR  (cr=4 pr=0 pw=0 time=119 us)
     14       INDEX RANGE SCAN I_SYSAUTH1 (cr=4 pr=0 pw=0 time=69
us)(object id 105)
    353     HASH JOIN  (cr=36 pr=0 pw=0 time=82703 us)
    326      CONNECT BY PUMP  (cr=0 pr=0 pw=0 time=999 us)
   9680      TABLE ACCESS FULL SYSAUTH$ (cr=36 pr=0 pw=0 time=19654 us)

************************************************************************
********

--
http://www.freelists.org/webpage/oracle-l

Other related posts: