Home » RDBMS Server » Performance Tuning » dynamic sql choosing wrong index (oracle 11g / aix )
dynamic sql choosing wrong index [message #593461] Fri, 16 August 2013 14:10 Go to next message
4wheels
Messages: 1
Registered: August 2013
Location: Houston, Tx
Junior Member
Yea yea - I know, don't use dynamic sql. This is a vendor app and some times the dynamic sql will work great. Sometimes - not so much. There is something that is telling the optimizer to use an incorrect index. But I don't know what that something is or how to find out how the optimizer made it's choice.

1) Is there a memory setting I can use that will help the optimizer determine the correct index to use?

2) How do I figure out what influenced the optimizer's decision to choose a particular index?

Re: dynamic sql choosing wrong index [message #593462 is a reply to message #593461] Fri, 16 August 2013 14:16 Go to previous messageGo to next message
BlackSwan
Messages: 22555
Registered: January 2009
Senior Member
1) have correct & current statistics
2) http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:63445044804318

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: dynamic sql choosing wrong index [message #593463 is a reply to message #593461] Fri, 16 August 2013 14:17 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1884
Registered: May 2013
Location: World Wide on the Web
Senior Member
Man you did not post any relevant information to make any sort of suggestion. What is the sql your are executing, where is the plan, which is the index you THINK the CBO is not using. Without any information it's impossible to do anything.

Moreover, if you are just saying Why Oracle isn't using my index. Then go to the indexes part here
Re: dynamic sql choosing wrong index [message #594008 is a reply to message #593463] Thu, 22 August 2013 08:31 Go to previous message
Kevin Meade
Messages: 1934
Registered: December 1999
Location: Connecticut USA
Senior Member
since you are on 11g there are several possibilities:

DYNAMIC SAMPLING. It was introduced in 9i. Given the right conditions, Oracle can choose to read a small portion of tables in your query and compute statistics based on what it reads. It can then use these results to generate a query plan. This can cause plans to change for the same query, especially if the query contains bind variables.

BIND VARIABLE PEEKING.

new optimization in 11g (assuming you are comparing to a pre-11g plan) (compare the outline data bewteen plans to maybe get an idea).

this is all just guessing though given more info is needed.

Kevin

[Updated on: Thu, 22 August 2013 08:31]

Report message to a moderator

Previous Topic: query tuning
Next Topic: Moving Tables to Encrypted Tablespace.
Goto Forum:
  


Current Time: Fri Aug 01 09:19:50 CDT 2014

Total time taken to generate the page: 0.10365 seconds