Solved

using the new Index in queries

Posted on 2015-02-14
8
160 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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
C# Connection String for Oracle database is not working 22 89
Queries 15 34
Export table into csv file in oracle 10 41
sort a spool into file output in oracle 1 22
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

911 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

21 Experts available now in Live!

Get 1:1 Help Now