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

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
<Brandon.Allen@xxxxxxxxxxx> wrote:
> 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!
>

Regards,
Greg Rahn
http://structureddata.org
--
http://www.freelists.org/webpage/oracle-l

Other related posts: