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..

On Jan 16, 4:10*pm, "RF" <r... (AT) hotmail (DOT) com> wrote:

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 --

RF:

# 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..

No silver bullets but a ton of very powerful techniques and potential
useful solutions these days with 11g.

You won't get very far though without investing the time and energy
into coming up to speed technically with what is going on.

Work on a short term strategy and long term strategy for how you are
going to manage and maintain optimizer statistics!

On Jan 17, 2:43*pm, John Hurley <hurleyjo... (AT) yahoo (DOT) com> wrote:

I have had at least one instance where the table was partitioned and
the new partitions with no stats performed very badly. By just adding
stats with 0 (zero) values the query plans would go back to "normal".

What the OP may need to do is run a nightly or weekly job to gather
the stats - depending in the rate of change in the table (inserts,
deletes etc..).

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 (DOT) com> wrote

On Jan 16, 4:10 pm, "RF" <r... (AT) hotmail (DOT) com> wrote:

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 --

On Jan 17, 5:32*pm, "RF" <r... (AT) hotmail (DOT) com> wrote:
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/2...ake-baselines/
http://kerryosborne.oracle-guy.com/2...lan-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.

jg
--
@home.com is bogus.
http://sushi-master.com/usa/products/robot0.html

On Jan 17, 8:32*pm, "RF" <r... (AT) hotmail (DOT) com> wrote:
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 --