Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • 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
 
PaulCommented:
>>"(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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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