Hi,
Lately, there has been a handful of intermittent performance issues and it all boiled down to the fact that Oracle happened to choose a "bad" plan to execute them for a just a set of bind variables.

I understand Oracle must be doing it for a good reason but it just worked for us when we ensured a single plan is always used. We did it either by creating indexes or sometimes out of no other choice forcing a particular plan (manual plan).

Now, as a proactive measure, I am interested in identifying if there are more such cases. I managed to find about 23 such cases by using the query below. I am planning to regularly check the report generated by the below query.

Select Sql_Id,Count(*) From
(Select Distinct Sql_Id,Plan_Hash_Value From Dba_Hist_Sqlstat where plan_hash_value <> 0 )
Group By Sql_Id
having count(*) > 1

But before we really start addressing them I wanted to know if it is correct to consider that it is always a bad thing to happen if Oracle chooses multiple plans? I am not saying all such cases are giving problems but we did see that a handful of such problems boiled down to this case.

John WatsonMessages: 5032Registered: January 2010 Location: Global Village

Senior Member

With 10g (and 9i before it) you have a big problem, Rags. Believe me, I've been there.

BS is probably right: bind variable peeking can cause this. It gets even worse in a RAC, where each instance does its own parsing. As a first step, I would set _optim_peek_user_binds=false which should give you some stability. Then watch for a while, and see what plans you get. When you sort out what plan is usually OK, grab a stored outline to nail it down.

This problem is really only fixed in 11g with Adaptive Cursor Sharing and SQL Plan Management. They can be a major driver for the 11g upgrade, though they do have their own problems.

Thanks Blackswan. Yes it is the classic Bind variable peeking that we are facing.

If we really have an issue, we can do something about it. But I am more interested in setting up a monitoring framework before we get into serious issues. Thats the reason I wanted to know if theoritically it is correct when I say "if Oracle chooses multiple plans for a single SQL, it requires some attention".

The statistics of certain tables are gathered on a daily basis, does that affect the plan_hash to large extent thereby making that report less reliable?

From my experience the problem was not "bad statistics" but SKEWED data.
Simple example case below.
INVOICE table has column to indicate if any particular invoice STATUS is OPEN ("O") or CLOSED ("C").
the vast, vast majority of invoice are old & closed.

WHERE STATUS = :bind

ideally you want index used when "bind='O'", but FTS otherwise.
So which is optimal/default PLAN?

There are a lot of reasons for opening a new child cursor and of changing the execution plan (I don't want to discuss them). If you find sql with several child cursors (and possibly with several execution plans), you can look into v$sql_shared_cursor for a reason of no sharing the existing cursor. It can be at the same time the reason for a different execution plan.