RE: statistics_level='ALL' with 10046, level 8 : Cost/Benefit Analysis
Date: Mon, 29 Sep 2008 09:48:29 -0700
Message-ID: <04DDF147ED3A0D42B48A48A18D574C450D84253E@NT15.oneneck.corp>
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-lReceived on Mon Sep 29 2008 - 11:48:29 CDT