Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

unused indexes

Posted on 2012-04-13
8
Medium Priority
?
334 Views
Last Modified: 2012-04-26
I have 1200 out of 1500 indexes in 10g that are never been used within 4 weeks and most of the processings have been passed. What should I do with them.


Thanks
0
Comment
Question by:dba1234
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 400 total points
ID: 37844309
If you are sure they are not necessary, maybe drop them?

I would document them in the event you ned to recreate them.

After you drop indexes, keep monitoring performance to see if you might need to recreate one or more.
0
 
LVL 32

Expert Comment

by:awking00
ID: 37844315
What kind of "processings have been passed"?
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 800 total points
ID: 37844340
also note, some indexes exist for the purpose of enforcing constraints.  So your users might not be querying from them but the indexes were used at the time of insert, update or delete

if you're using monitoring,  the recursive action of looking at the index for enforcement of constraints is not captured.
Only direct action.


these will be monitored

select * from table where indexed_column = x;
delete from table where indexed_column = y;
update table set some_column = 1234 where indexed_column = z;

this will not be monitored

delete myparent table;
index lookup of children via fk (hopefully your fk's are indexed);

you'll get partial monitoring here...
emp has two indexes, one on name and a unique index on empid
update emp set name='sdstuber' where empid = 1234;

empid index will be monitored,  the update on the name index will not be
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:dba1234
ID: 37844375
Processings means our monthly and weekly jobs.
0
 

Author Comment

by:dba1234
ID: 37844422
sdstuber, except monitoring what's the other way to monitor indexes. and how can I make sure which indexes are being used in the delete statement as you said.
delete myparent table;
index lookup of children via fk (hopefully your fk's are indexed
0
 
LVL 23

Assisted Solution

by:David
David earned 400 total points
ID: 37844497
Just to confirm you're not thinking of removing eBS (APPS) indexes.....  

It's my opinion that without a predicate, the delete table statement would require a full table scan.  With child tables present, and a referential integrity constraint (foreign key), I'd expect the PK to be used.

Do you wish to drop related child rows, or leave them?
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 800 total points
ID: 37844522
you can't, at least not in any kind of "good" way

you could turn on tracing for the entire instance, then grep through the trace files for index access .
That would work, but tracing every statement of every session isn't really a viable option for most systems.

you could audit every statement, collecting action and table, then join those results to dba_constraints and dba_indexes to try to capture all of the non-monitored actions.   possible, but a lot of work.  You might get false positives doing that too.  probably preventable, but off hand I'm not positive it's completely reliable.

rather than trying to find them all,  I'd try targetting a few that might be problematic.  
The drop and see who complains method is drastic but might be the most efficient.  Especially if you have high confidence they are not used and can easily rebuild them if/when you are wrong.
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 400 total points
ID: 37844529
What do your monthly and weekly jobs do? If they are merely inserting, updating, and/or deleting records in database tables, that doesn't mean any queries might not be using the indexes.
0

Featured Post

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup

704 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