Solved

using the new Index in queries

Posted on 2015-02-14
8
155 Views
Last Modified: 2015-03-10
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
Comment
Question by:Oranew
  • 4
  • 3
8 Comments
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 100 total points
ID: 40610636
>>"(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
 
LVL 34

Expert Comment

by:johnsone
ID: 40610756
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
 

Author Comment

by:Oranew
ID: 40612185
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
 
LVL 34

Expert Comment

by:johnsone
ID: 40612313
I posted an example of how to do exactly that in your other question.  The one referenced by PortletPaul.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Oranew
ID: 40615680
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
 
LVL 34

Expert Comment

by:johnsone
ID: 40616449
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
 

Author Comment

by:Oranew
ID: 40616465
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
 
LVL 34

Accepted Solution

by:
johnsone earned 400 total points
ID: 40616514
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now