| dynamic sql choosing wrong index [message #593461] |
Fri, 16 August 2013 14:10  |
 |
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 #593463 is a reply to message #593461] |
Fri, 16 August 2013 14:17  |
Lalit Kumar B
Messages: 3174
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 |
 |
Kevin Meade
Messages: 2103
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
