Re: Stale statistics

From: John Hurley <johnbhurley_at_sbcglobal.net>
Date: Wed, 1 Jul 2009 06:25:15 -0700 (PDT)
Message-ID: <afb6f409-f1a8-42ee-848f-ebfe2a8e973f_at_y17g2000yqn.googlegroups.com>

On Jul 1, 8:58 am, John Hurley <johnbhur..._at_sbcglobal.net> wrote:

snip

> Don't forget about the changes version 10 introduced when new
> statistics are gathered.
>
> By default now... unless you invoke dbms_stats with NO_INVALIDATE set
> to FALSE ( a backward weird logic setting if any ever existed ...
> shouldn't it be INVALIDATE = TRUE ) ... parsed already active SQL
> statements do not get invalidated right away ... they stay around even
> though new statistics are in place.
>
> This is a huge change in the behavior of 10 and above but many people
> seem to still be unaware of it.
>
> If you run dbms_stats without using the NO_INVALIDATE parameter
> ( ie ... like 99.9 percent of the world ) then you start running into
> the effects introduced by another ( new with 10 ) undocumented oracle
> parameter _optimizer_invalidation_period ...
>
> So even if SQL statements logically should be re parsed ... you
> wait ... a variable length of time depending on how much a specific
> SQL statement keeps getting issued ( or not ) ... before the new
> statistics are reflected in the execution plan.
>
> Worth looking at is thishttp://forums.oracle.com/forums/thread.jspa?threadID=592771
>
> There's a doc id worth looking at in Metalink but geez I am getting
> login errors right now.

Doc id is 557661.1 Received on Wed Jul 01 2009 - 08:25:15 CDT