Re: stale stats
Date: Mon, 17 Jan 2011 08:14:14 -0800 (PST)
Message-ID: <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 - 10:14:14 CST