Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 210
  • Last Modified:

using the new Index in queries

I created an index and the index is used in the query when I run it from the sqlplus. The application uses the same query (shows bind variable in the AWR report) but the index is not used.
The same sql runs better when the query uses  this new index(from sqlplus it is taking  this index by itself) . How can I force this  query to use this index when run from the application ?
0
Oranew
Asked:
Oranew
  • 4
  • 3
2 Solutions
 
PortletPaulCommented:
>>"(shows bind variable in the AWR report)"

bind variable -v-  values
You cannot tune a query with values and expect the same result with bind variables.  It doesn't work that way.  With a value, the optimizer can make assumptions.  Without a value (i.e. with a bind variable) it cannot make assumptions about the value.
johnsone 2015-02-13  http://www.experts-exchange.com/Database/Oracle/Q_28616777.html

You could try a hint to force use of that index... but that index might not suit all bind variable values (or combination of values)
0
 
johnsoneSenior Oracle DBACommented:
Besides needing to tune the query with bind variables as discussed in the other question.  Once the index is created, you should update statistics on the table.
0
 
OranewAuthor Commented:
The issue is when I am trying to tune the query without bind variables it is not running. it is asking me for the bind values in the sqlplus. What is the way to tune the query without passing the bind variables ?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
johnsoneSenior Oracle DBACommented:
I posted an example of how to do exactly that in your other question.  The one referenced by PortletPaul.
0
 
OranewAuthor Commented:
Not sure what is the issue but the index is only used when I am running the sql using values.(not bind variables). The explain plan is different when using bind variable and the plan with the new indexes is way better and also the sql actually runs from the application and it uses bind. The stats ran on all the tables but still the new index is not used
0
 
johnsoneSenior Oracle DBACommented:
The difference is that the optimizer knows the value it is looking for when it generates the plan.  Knowing the value helps with histograms and a better prediction of which plan would be "best".
0
 
OranewAuthor Commented:
Thanks, then how can I tell the optimizer to take the plan B which is a better plan.
Do you suggest to save this plan and flush the old saved plan?
I am talking about sqlplanbaseline.
0
 
johnsoneSenior Oracle DBACommented:
I would put a hint on the query, use a stored outline or look at changing session parameters that relate to the optimizer (specifically OPTIMIZER_MODE).  If the optimizer isn't going to find the right plan, then you have to use one of the methods available to help it along.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now