| Multiple plans for a particular SQL [message #529524] |
Tue, 01 November 2011 12:39  |
 |
Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom
|
Member |
|
|
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.
Any suggestions on how to go about avoiding those issues?
Cheers,
Rags
|
|
|
|
|
|
| Re: Multiple plans for a particular SQL [message #529533 is a reply to message #529527] |
Tue, 01 November 2011 12:55  |
John Watson
Messages: 8962
Registered: 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.
Good luck.
|
|
|
|
|
|
|
|
|
|
| Re: Multiple plans for a particular SQL [message #529892 is a reply to message #529536] |
Thu, 03 November 2011 09:16 |
 |
LNossov
Messages: 318
Registered: July 2011
Location: Germany
|
Senior Member |
|
|
|
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
