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_at_oneneck.com> 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