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

  • From: "Greg Rahn" <greg@xxxxxxxxxxxxxxxxxx>
  • To: Brandon.Allen@xxxxxxxxxxx
  • 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
<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, 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

Other related posts: