Home » RDBMS Server » Server Administration » Multiple plans for a particular SQL (Oracle 10g 10.2.0.3.0, Linux)
Multiple plans for a particular SQL [message #529524] Tue, 01 November 2011 12:39 Go to next message
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 #529527 is a reply to message #529524] Tue, 01 November 2011 12:45 Go to previous messageGo to next message
BlackSwan
Messages: 22905
Registered: January 2009
Senior Member
>Any suggestions on how to go about avoiding those issues?
Trade-offs!

SQL which utilizes bind variables can be afflicted with Bind Variable Peeking.
You can force Oracle to always use a specific plan, but that can result in sub-optimal execution in some cases.
Re: Multiple plans for a particular SQL [message #529533 is a reply to message #529527] Tue, 01 November 2011 12:55 Go to previous messageGo to next message
John Watson
Messages: 4685
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 #529535 is a reply to message #529527] Tue, 01 November 2011 12:58 Go to previous messageGo to next message
Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom
Member
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?
Re: Multiple plans for a particular SQL [message #529536 is a reply to message #529535] Tue, 01 November 2011 13:00 Go to previous messageGo to next message
Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom
Member
Thanks John, I will check the optim_peek_user_binds option. But is there a catch with that?
Re: Multiple plans for a particular SQL [message #529538 is a reply to message #529535] Tue, 01 November 2011 13:05 Go to previous messageGo to next message
BlackSwan
Messages: 22905
Registered: January 2009
Senior Member
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?

Re: Multiple plans for a particular SQL [message #529892 is a reply to message #529536] Thu, 03 November 2011 09:16 Go to previous message
LNossov
Messages: 289
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.
Previous Topic: ORA-07445: exception encountered: core dump [SIGSEGV] [Address not mapped to object] (merged 7)
Next Topic: dbms job not created
Goto Forum:
  


Current Time: Thu Oct 23 07:11:40 CDT 2014

Total time taken to generate the page: 0.13360 seconds