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

From: Greg Rahn <>
Date: Mon, 29 Sep 2008 11:19:13 -0700
Message-ID: <>

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 <> 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, 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!

Greg Rahn

Received on Mon Sep 29 2008 - 13:19:13 CDT

Original text of this message