Solved

unused indexes

Posted on 2012-04-13
8
325 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
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 100 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 31

Expert Comment

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

Assisted Solution

by:sdstuber
sdstuber earned 200 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
 

Author Comment

by:dba1234
ID: 37844375
Processings means our monthly and weekly jobs.
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: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 100 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 73

Assisted Solution

by:sdstuber
sdstuber earned 200 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 31

Assisted Solution

by:awking00
awking00 earned 100 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

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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 …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.

744 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

9 Experts available now in Live!

Get 1:1 Help Now