Discussions
Categories
- 382.6K All Categories
- 2.1K Data
- 212 Big Data Appliance
- 1.9K Data Science
- 448.4K Databases
- 221.1K General Database Discussions
- 3.7K Java and JavaScript in the Database
- 25 Multilingual Engine
- 541 MySQL Community Space
- 469 NoSQL Database
- 7.8K Oracle Database Express Edition (XE)
- 2.9K ORDS, SODA & JSON in the Database
- 499 SQLcl
- 3.9K SQL Developer Data Modeler
- 186.3K SQL & PL/SQL
- 21.1K SQL Developer
- 293.8K Development
- 7 Developer Projects
- 129 Programming Languages
- 290.5K Development Tools
- 96 DevOps
- 3K QA/Testing
- 645.6K Java
- 24 Java Learning Subscription
- 36.9K Database Connectivity
- 151 Java Community Process
- 104 Java 25
- 22.1K Java APIs
- 137.9K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 146 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 200 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 290 LiveLabs
- 36 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.8K Other Languages
- 2.3K Chinese
- 168 Deutsche Oracle Community
- 1.2K Español
- 1.9K Japanese
- 234 Portuguese
How to find when the index last used
Environment:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
Question:
1. How to find when the given index is last used?
2. How to find if the given index is useful to keep it or not ? or find the useless indexes.
Scenario:
The are indexes with name startining with ‘IDX$$_ ‘ are exists on our production database.
Usually, when we use the recommendations of DBMS_AUTO_SQLTUNE, by running a SQL tuning task and the recommendation may have been to implement an index , at which time
Oracle may have used this prefix and created an index name with 'IDX$$_'
We would like to know if these indexes are really needed or it can be dropped.
Sivaprasad.S
Answers
-
SivaprasadS wrote: Environment: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production Question: 1. How to find when the given index is last used? 2. How to find if the given index is useful to keep it or not ? or find the useless indexes. Scenario: The are indexes with name startining with ‘IDX$$_ ‘ are exists on our production database. Usually, when we use the recommendations of DBMS_AUTO_SQLTUNE, by running a SQL tuning task and the recommendation may have been to implement an index , at which time
Oracle may have used this prefix and created an index name with 'IDX$$_' We would like to know if these indexes are really needed or it can be dropped. Sivaprasad.SCheers,
-
The main approach is to turn on monitoring for that index, however you can also query the saved plans.
If you have the appropriate pack licensed, you can query dba_hist_sql_plan where object_name=<index_name>. This will show any plans that have used that index.
Another thing to keep in mind, if the indexes are large in size you can make them invisible. In case they are needed,you can alter them as visible rather than rebuilding.
Paul
-
Maybe it's time to change name from Objects to (eg) Object-relational features.
Best regards
-
Before concluding indexes are 'not used' you should also determine if they support any foreign-key, primary-key or unique constraints.
If they are unique indexes then presumably there is a business rule (a unique constraint) that they enforce. Dropping such indexes would introduce a functional change.
Indexes that correspond to foreign-key constraints may be more subtle. If Oracle is using them to valdiate foreign-key constraints (such as verifying that a row in a parent table has no related rows in a child table), this usage is not reflected in the plans. If you drop such an index, the enforcement would still occur but it may take much longer and introduce additional locking.
-
A couple of other things to keep in mind:
Just because an index hasn't been used recently doesn't mean it isn't important. One obvious consequence is an index used by a period-end batch, it's no fun to have people tapping their feet and watching over your shoulder at 7:30PM December 31st... there's nothing wrong with invisibility or creating such an index when needed, you just need to know ahead of time.
The simple existence of an index may influence optimizer decisions, affecting performance.
There are some interesting blogs and books about these topics, I'd say look at index used Richard Footes Oracle Blog
and Indexing | Oracle Scratchpad to start.
