Re: stale stats
Date: Mon, 17 Jan 2011 20:32:31 -0500
Message-ID: <R46Zo.62$CN7.56_at_newsfe11.iad>
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 ?
"Mark D Powell" <Mark.Powell2_at_hp.com> wrote in message
news: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 -- Received on Mon Jan 17 2011 - 19:32:31 CST