Re: stale stats
From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Wed, 19 Jan 2011 06:55:12 -0800 (PST)
Message-ID: <8978e46f-c2c8-4bb3-99ad-2a7536703c6f_at_c13g2000prc.googlegroups.com>
On Jan 17, 8: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 ?
>
> "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 --
Date: Wed, 19 Jan 2011 06:55:12 -0800 (PST)
Message-ID: <8978e46f-c2c8-4bb3-99ad-2a7536703c6f_at_c13g2000prc.googlegroups.com>
On Jan 17, 8: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 ?
>
> "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 --
If you are currently running "massive" inserts at irregular intervals then updating the statistics immediatly after such inserts may well be a good idea at least until such time as the amount of data inserted is not longer relatively a significant percentage of the data.
Locking and unlocking statistics using dbms_stats is covered in the PL/ SQL Types and References Manual while Adaptive Cursor Sharing is discussed in the Performance and Tuning manual.
HTH -- Mark D Powell -- Received on Wed Jan 19 2011 - 08:55:12 CST