Re: stale stats
Date: Tue, 18 Jan 2011 09:29:31 -0800 (PST)
Message-ID: <4bc9e455-b1f5-4f4d-a8cf-8523b7e25462_at_29g2000prb.googlegroups.com>
On Jan 17, 5:32 pm, "RF" <r..._at_hotmail.com> wrote:
> Is there any link to those options you provided below tied to locking plans
> ? I know of hints.. but the others are new to me.
>
> We do know that we have some massive data inserts at times and right now we
> dont have any procedures in place as far as when to collect stats.. we have
> tons of nightly jobs that run and is it best to collect stats after every
> job run ?
Please don't top-post.
What is generally best is to find out the plan or plans that work for your situation, and do what is necessary to get the right ones. If there is one right plan, so much the better, but if there is more than one right plan, you have to be careful in what clues you give to the optimizer as to which plan to use (for examples, google bind variable peeking - the same query might need different plans depending on values passed in).
How to figure it out depends partly on which tools you have licensed - the tuning pack certainly makes it easier.
The common situation I've seen on my 10gR2 OLTP system is, everything running fine, a particular query suddenly gets bad, I use dbconsole to go back to a previous set of statistics and lock them, and everybody is happy.
11g has more features and options that can make the situation more complex, and some of the consequences of the newer features are still kind of mysterious, but regardless, there is no formula that can tell you what simple things to do in your situation.
What you can do is look up in the docs all the features Mark mentioned to get a basic understanding, get Jonathan's optimizer book to learn the details of how to follow what the optimizer is doing, and figure out what you need for your situation.
Jonathan Lewis and Kerry have posted some interesting examples of using baselines on their blogs recently, such as:
http://jonathanlewis.wordpress.com/2011/01/12/fake-baselines/ http://kerryosborne.oracle-guy.com/2009/04/oracle-11g-sql-plan-management-sql-plan-baselines/
(Don't stop there, poke around on their sites, and google the concepts to see what others may have found.)
Also, be aware of when the statistics are collected, be sure you aren't doing something silly like deleting all the data just when a default stats collection job is about to run.
>
> "Mark D Powell" <Mark.Powe..._at_hp.com> wrote in messagenews:8d1f0202-ac58-4091-9703-349a723ad569_at_fx12g2000vbb.googlegroups.com...
> On Jan 16, 4:10 pm, "RF" <r..._at_hotmail.com> wrote:
>
> > We keep running into suboptimal query plans with 11g and then we run into
> > slow queries and once we collect stats, things get better again. How can
> > we
> > ensure our plans dont change as such ?
>
> > Please advice. It is very frustrating and leads to downtime of our apps..
>
> You should look at the plans and compare the before and after
> statistics to see why the plan is changing. If you have an index on a
> column which constantly have new values inserted on one end of a value
> range while having the other end of the value range deleted and
> histograms exist on the column the histograms can result in misleading
> information to Oracle since the histograms can quickly become out of
> date.
>
> To lock a plan in depending on Oracle version (11g in your case) and
> purchased options you can use hints, Outlines, SQL Profiles, or
> Adaptive Cursor Sharing to deal with the statements in question. When
> you have access to the SQL I like hints since their presence in the
> code is visible to future developers/DBA's to see.
>
> HTH -- Mark D Powell --
jg
-- _at_home.com is bogus. http://sushi-master.com/usa/products/robot0.htmlReceived on Tue Jan 18 2011 - 11:29:31 CST