Re: statistics_level='ALL' with 10046, level 8 : Cost/Benefit Analysis
Date: Mon, 29 Sep 2008 11:19:13 -0700
Is this on Linux?
There is no fast timer on Linux so it is not uncommon to see the sys CPU time go through the roof (>50%) if you set statistics_level=all and the over all time to drastically increase. On other platforms it is not as expensive, but I would avoid it. Use the GATHER_PLAN_STATISTICS hint as an alternative.
On Mon, Sep 29, 2008 at 9:48 AM, Allen, Brandon
> 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!