Hi,
This article is more of the rest of the story from March’s Lucky Breaks. (Click here to see this blog). I want to share a real consulting gig where I used index monitoring with great success.
Index Monitoring
Index monitoring was introduced in Oracle9. Prior to Oracle9, it was difficult to know if Oracle was using a particular index or not. Oracle9 solved this with its background MMON monitoring process. MMON does a variety of things (such as collecting real-time statistics for tables and other objects), including monitoring explain plans for the appearance of index names. MMON does its work outside the Oracle database, in that it does not run SQL to perform these monitoring tasks. It gathers this information very rapidly and without a lot of overhead.
If you have index monitoring turned on for an index or a group of indexes, and a SELECT type query (includes any sub queries) makes reference to one of these, then the ‘USED’ column in V$_OBJECT_USAGE is tripped to a ‘YES’. It is this simple.
I don’t care to know where the index was used, but this just takes a little more detective work using the same techniques. Some folks do care where the index is being used. You can use this same technique and multiple runs to track where the indexes are being used.
I have advocated putting comments into SQL, giving the SQL a name. This name can then include the app and maybe even the program that the SQL originated from. I plan a future article/blog post where you can then track this SQL easily…anyway…I’ve not been in a shop that does this but I think it has merit.
Toad has support for index monitoring. Using Database -> Monitor -> Index Monitor, you can turn on/off index monitoring via Toad.
I also have scripts that turn on/off this monitoring. Ask me for them and for the report below if you have the need!
You simply select or use group select and click on the green ‘go’ button to start the monitoring of indexes.
NOTE: running statistics on an index trips its monitor to ‘YES’ so make sure you are not monitoring indexes when you run statistics (come in here and use the red ‘x’ button to stop the monitoring).
select to_char(sysdate,'mm/dd/yy hh:mi:ss') Date_Time from dual;
select 'Table Count = ' || count(*) from user_tables;
select 'Index Count = ' || count(*) from v$object_usage;
Select 'Indexes Used = ' || count(*) from v$object_usage where USED = 'YES';
select 'Indexes NOT Used = ' || count(*) from v$object_usage where USED = 'NO';
This SQL produces the report below. You can run it in Toad or any SQL processing machine.
Note that in Oracle12, these columns have been moved to <USER_ ALL_ DBA_>INDEXES. I have not adjusted my scripts yet. Adjust them for me, send them back! Get your name noted in my course guide [:)]
Ok, this is an example from my class. We can see from the time the index monitoring was turned on, that three indexes were accessed and there are 23 indexes across 27 tables. It also does the math for you on the unused indexes.
My real-world example had quite a bit more information! I was nervous. As you may have noticed, I usually run things against EMP and DEPT tables, the simple SCOTT schema that has come with Oracle since at least Version 4 (yes, the password used to be Tiger and yes Bruce Scott wrote the original SQL*Plus, then called UFI (User Friendly Interface) and yes his cat’s name was ‘Tiger’ [:)]).
I was nervous because I didn’t think turning on index monitoring across a production system would be very intrusive, but what if it was? I really had no idea.
The client had an issue with their nightly batch job running too long. So, I arrived and we turned index monitoring on for their application. I was relieved the next morning when I arrived and they were not upset with me because the batch job was still running or something like that. It didn’t take a noticeable difference in time!
I got lucky [:)]
We ran the report. They had 110 tables and well over 1100 indexes! …of which…only 97 were being used in their day-to-day use of the database.
SO…drop half the indexes, the batch job (DML operations) will run twice as fast!
When you update a table…this is a simplistic example but it’s true…
- Oracle locks the block
- Oracle makes a before image of the block (now called rollback segments)
- Oracle actually makes the data change (assumes you will commit…commits are quick…rollbacks have to undo the changes!!!)
- Oracle journals the changes (if you have journaling turned on; most production systems do)
- Unlocks the table.
Five operations for a table. For an index…the number of transactions is more like seven or more…because Oracle has to lock the index leaf block where the change is currently and the other block where it is going to…indexes are maintained in sequence…so…a change to an index is a delete/add operation.
So, when you have 11 indexes per table, you are doing, like, 80 to 100 transactions PER row, if the change affects most of the indexes. Oracle maintains indexes whether they are used or not.
Using this technique, you can start making lists. Use the database! Then you can use SQL creating SQL to create ‘drop and create scripts’ for you. You probably need the day-to-day and weekly indexes being used. Monthly indexes…maybe. Quarterly and year-end indexes…probably not. If you have a lot of indexes, as did this client, you will find many are not being used at all. This is because Oracle’s cost-based optimizer is making better decisions on whether to use an index or not, etc. I want to blog on clustering factor…a useful statistic Oracle uses to decide if the index is useful for a query or no. All other indexes are there because of the way we used to create indexes (the old rule-based optimizer coding tricks and just plain willy/nilly). Write scripts to create then drop quarterly and yearly indexes. Why maintain a group of indexes all year long to use them in one run once a year or four runs every three months?
I’m not sure what the client did. They were pleased I found the issue with their batch performance nightly job but they had no idea they had so many indexes and, using my skills and report, they had no idea so few were being used!
Dan Hotka
Author/Instructor/Oracle Expert
Start the discussion at forums.toadworld.com