Home » RDBMS Server » Performance Tuning » Avoid Index to be used by Queries
Avoid Index to be used by Queries [message #248934] Mon, 02 July 2007 14:55 Go to next message
feroze
Messages: 144
Registered: September 2005
Location: chicago
Senior Member
Hi,

we impelemented some partitioned indexes (local ) and some global composite indexes are also there for a table with 120 million rows,
Now question some queries from the Application is still using OLD index, 2 questions
a ) Is there a way in the DB to know which queries are using this index

b ) is it possible to disable the index so that select statements and queries don't use the index ,but at same time inserts should be recorded, because if things get's bad then i can enable that index back and in this case i will have those inserted records entry also in index.


Thanks
Re: Avoid Index to be used by Queries [message #248943 is a reply to message #248934] Mon, 02 July 2007 15:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
a) trace/tkprof

b) NO_INDEX hint

Regards
Michel
Re: Avoid Index to be used by Queries [message #248945 is a reply to message #248943] Mon, 02 July 2007 15:33 Go to previous messageGo to next message
feroze
Messages: 144
Registered: September 2005
Location: chicago
Senior Member
Thanks for follow up,

Answer B can't be done till we know the query,
and to know the query you want us to do Answer (a) which we don't know which query is using those indexes, and we can't trace whole DB or 300 + Users,

I will try for a 1 or 2 hr STATSPACK and see if there is any entry for this index.


Thanks
Re: Avoid Index to be used by Queries [message #248947 is a reply to message #248945] Mon, 02 July 2007 15:42 Go to previous message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For a), there is no other way to be sure you get ALL queries. Statspack polls the sql area and so may miss some statements.

For b), you can always put the hint on each and every query. But why not drop the index and recreate it if you see it is bad.
In the end, why do you think you have to "disable" the index?

Regards
Michel
Previous Topic: Performance issue in report
Next Topic: pga_aggregate_target - Issue
Goto Forum:
  


Current Time: Tue Dec 06 02:23:30 CST 2016

Total time taken to generate the page: 0.10985 seconds