in Tim Hall’s article<\/a>. There’s plenty of information that you can read through by googling Oracle Index Monitoring and that was just one of the first examples I found.<\/p>\nHowever, there are a few problems with this approach.<\/p>\n
1) As mentioned in Tim’s article, you need to have the facility enabled for long enough to be sure that indexes aren’t used. Even if you leave it enabled for a month or so, who is to say that a particular index isn’t vital to the performance of a year-end routine? Perhaps unlikely, but it would be a shame to find out after you’ve dropped the index.<\/p>\n
2) Index monitoring simply isn’t granular enough. All it will tell me is that an index has been used but I have no idea of how often it’s been used. I suppose you could get round this by switching index monitoring on and off frequently and seeing which indexes only appear to be used occasionally.<\/p>\n
3) Related to the low granularity, an index may not be used by your application at all but a DBA or developer happens to run a one-off query, which happens to use the index. I know that an index was used, but how do I know whether that use was significant or just an anomaly? The information ‘YES’ doesn’t contain much value.<\/p>\n
There are other approaches. Maybe we could look at V$SQL_PLAN and V$SQL_PLAN_STATISTICS or use a login trigger to capture Stored Outlines?<\/p>\n
One approach that sprung to mind for me was something that I heard Jonathan Lewis mention at the UKOUG Northern Server Tech day. One of the things I like about Jonathan’s presentations is that there are always useful nuggets of information scattered through the main narrative.<\/p>\n
He mentioned an event that could be set to capture all the execution plans into a trace file – event 10132 – dump plan after compilation. I searched for information on this event on Metalink and the wider web and there isn’t too much kicking around, so I thought I’d mention it here. Eventually we’re going to set the event at the instance level, so that we can capture all of the plans and look for indexes that don’t appear in the trace file. Before we do that, I have an SR open with Oracle Support to make sure that they’re happy with us setting this event. For the example here, I’ll just set it at the session level and executed a single new statement.<\/p>\n
livdwhtst01:[oracle]:sqlplus testuserSQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 21 12:12:42 2006Copyright (c) 1982, 2005, Oracle. All rights reserved.Enter password:Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit ProductionWith the Partitioning and Data Mining optionsSQL> create table test as select * from dba_objects;Table created.SQL> create unique index test_ix on test(object_id);Index created.SQL> exec dbms_stats.gather_table_stats('TESTUSER', 'TEST');PL\/SQL procedure successfully completed.SQL> alter session set events '10132 trace name context forever, level 1';Session altered.SQL> select count(*) from test; COUNT(*)---------- 106242SQL> show parameter user_dump_destNAME TYPE------------------------------------ --------------------------------VALUE------------------------------user_dump_dest string\/ora\/admin\/MIDDWHT3\/udump\nSQL> exit\nDisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit ProductionWith the Partitioning and Data Mining options<\/code><\/pre>\nThe trace file will be in user_dump_dest for this session-level example. Let’s take a look. <\/p>\n
livdwhtst01:[oracle]:pwd\/ora\/admin\/MIDDWHT3\/udumplivdwhtst01:[oracle]:ls -ltra | tail-rw-r----- 1 oracle dba 644 Jul 21 07:25 middwht3_ora_8618.trc-rw-rw-rw- 1 oracle dba 1561 Jul 21 08:26 middwht3_ora_16958.trc-rw-r----- 1 oracle dba 32723 Jul 21 08:52 middwht3_ora_26349.trc-rw-r----- 1 oracle dba 646 Jul 21 11:37 middwht3_ora_17201.trc-rw-r----- 1 oracle dba 109697 Jul 21 11:59 middwht3_ora_21441.trc-rw-r----- 1 oracle dba 59396 Jul 21 12:06 middwht3_ora_22680.trc-rw-r----- 1 oracle dba 42243 Jul 21 12:16 middwht3_ora_23474.trc-rw-r----- 1 oracle dba 646 Jul 21 12:18 middwht3_ora_24621.trcdrwxrwxr-x 2 oracle dba 224256 Jul 21 13:14 .-rw-r----- 1 oracle dba 21537 Jul 21 13:15 middwht3_ora_9429.trc\nlivdwhtst01:[oracle]:cat middwht3_ora_9429.trc\n\/ora\/admin\/MIDDWHT3\/udump\/middwht3_ora_9429.trc\nOracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production\nWith the Partitioning and Data Mining options\nORACLE_HOME = \/ora\/product\/10.2.0\nSystem name: SunOS\nNode name: livdwhtst01\nRelease: 5.9\nVersion: Generic_118558-17\nMachine: sun4us\nInstance name: MIDDWHT3\nRedo thread mounted by this instance: 1\nOracle process number: 13\nUnix process pid: 9429, image: oracle@livdwhtst01 (TNS V1-V3)\n\n*** 2006-07-21 13:14:59.446\n*** ACTION NAME:() 2006-07-21 13:14:59.445\n*** MODULE NAME:(SQL*Plus) 2006-07-21 13:14:59.445\n*** SERVICE NAME:(SYS$USERS) 2006-07-21 13:14:59.445\n*** SESSION ID:(89.10029) 2006-07-21 13:14:59.445\n\nsql_id=7b2twsn8vgfsc.\nCurrent SQL statement for this session:\nselect count(*) from test\n\n============\nPlan Table\n============\n--------------------------------------+-----------------------------------+\n| Id | Operation | Name | Rows | Bytes | Cost | Time |\n--------------------------------------+-----------------------------------+\n| 0 | SELECT STATEMENT | | | | 328 | |\n| 1 | SORT AGGREGATE | | 1 | | | |\n| 2 | TABLE ACCESS FULL | TEST | 104K | | 328 | 00:00:04 |\n--------------------------------------+-----------------------------------+\nPredicate Information:\n----------------------\n\nContent of other_xml column\n===========================\n db_version : 10.2.0.1\n parse_schema : TESTUSER\n plan_hash : 3467505462\n Outline Data:\n \/*+\n BEGIN_OUTLINE_DATA\n IGNORE_OPTIM_EMBEDDED_HINTS\n OPTIMIZER_FEATURES_ENABLE('10.2.0.1')\n ALL_ROWS\n OUTLINE_LEAF(@\"SEL$1\")\n FULL(@\"SEL$1\" \"TEST\"@\"SEL$1\")\n END_OUTLINE_DATA\n *\/\n\nOptimizer environment:\n optimizer_mode_hinted = false\n optimizer_features_hinted = 0.0.0\n parallel_execution_enabled = true\n parallel_query_forced_dop = 0\n parallel_dml_forced_dop = 0\n parallel_ddl_forced_degree = 0\n\n[Lots of other parameter values snipped out here ]<\/i><\/b>\n\n _force_rewrite_enable = false\n _optimizer_star_tran_in_with_clause = true\n _optimizer_complex_pred_selectivity = true\n _gby_hash_aggregation_enabled = true\nQuery Block Registry:\n*********************\nSEL$1 0x7b01b6a8 (PARSER) [FINAL]\nOptimizer State Dump: call(in-use=19392, alloc=49080), compile(in-use=59056, alloc=107040)<\/code><\/pre>\nYou’ll also see the SQL statement that results from the show parameter user_dump_test instruction (I’ve stripped most of the output to keep it shorter) <\/p>\n
\nsql_id=gz3s8nmsh6s8c.\nCurrent SQL statement for this session:\nSELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,:\"SYS_B_00\",:\"SYS_B_01\",:\"SYS_B_02\",:\"SYS_B_03\",\n\n:\"SYS_B_04\",:\"SYS_B_05\",:\"SYS_B_06\",:\"SYS_B_07\",:\"SYS_B_08\",:\"SYS_B_09\", \n\n:\"SYS_B_10\",:\"SYS_B_11\", :\"SYS_B_12\") TYPE,DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM \n\nFROM V$PARAMETER WHERE UPPER(NAME) LIKE UPPER(:\"SYS_B_13\") ORDER BY NAME_COL_PLUS_SHOW_PARAM,ROWNUM\n\n============\nPlan Table\n============\n---------------------------------------+-----------------------------------+\n| Id | Operation | Name | Rows | Bytes | Cost | Time |\n---------------------------------------+-----------------------------------+\n| 0 | SELECT STATEMENT | | | | 2 | |\n| 1 | SORT ORDER BY | | 1 | 371 | 2 | 00:00:01 |\n| 2 | COUNT | | | | | |\n| 3 | HASH JOIN | | 1 | 371 | 1 | 00:00:01 |\n| 4 | FIXED TABLE FULL | X$KSPPI | 1 | 81 | 0 | |\n| 5 | FIXED TABLE FULL | X$KSPPCV| 100 | 28K | 0 | |\n---------------------------------------+-----------------------------------+\nPredicate Information:\n----------------------\n3 - access(\"X\".\"INDX\"=\"Y\".\"INDX\")\n3 - filter((TRANSLATE(\"KSPPINM\",'_','#') NOT LIKE '#%' OR \"KSPPSTDF\"='FALSE' OR BITAND(\"KSPPSTVF\",5)>0))\n4 - filter((\"X\".\"INST_ID\"=USERENV('INSTANCE') AND UPPER(\"KSPPINM\") LIKE UPPER(:SYS_B_13) AND \n TRANSLATE(\"KSPPINM\",'_','#') NOT LIKE '##%'))\n\nContent of other_xml column\n===========================\n db_version : 10.2.0.1\n parse_schema : TESTUSER\n plan_hash : 245223518\n Outline Data:\n \/*+\n BEGIN_OUTLINE_DATA\n IGNORE_OPTIM_EMBEDDED_HINTS\n OPTIMIZER_FEATURES_ENABLE('10.2.0.1')\n ALL_ROWS\n OUTLINE_LEAF(@\"SEL$88122447\")\n MERGE(@\"SEL$641071AC\")\n OUTLINE(@\"SEL$1\")\n OUTLINE(@\"SEL$641071AC\")\n MERGE(@\"SEL$07BDC5B4\")\n OUTLINE(@\"SEL$2\")\n OUTLINE(@\"SEL$07BDC5B4\")\n MERGE(@\"SEL$4\")\n OUTLINE(@\"SEL$3\")\n OUTLINE(@\"SEL$4\")\n FULL(@\"SEL$88122447\" \"X\"@\"SEL$4\")\n FULL(@\"SEL$88122447\" \"Y\"@\"SEL$4\")\n LEADING(@\"SEL$88122447\" \"X\"@\"SEL$4\" \"Y\"@\"SEL$4\")\n USE_HASH(@\"SEL$88122447\" \"Y\"@\"SEL$4\")\n END_OUTLINE_DATA\n *\/<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"A common requirement cropped up this week. A new Data Warehouse has just gone live and is still in the ‘do we have the right indexes here’ phase. In this case, the suspicion is that there are too many indexes in a specific schema and that a number of them may just be a waste… Continue reading Monitoring Index Usage<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1030","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1570,"url":"http:\/\/orcldoug.com\/blog\/2010\/02\/28\/statistics-on-partitioned-tables-part-5\/","url_meta":{"origin":1030,"position":0},"title":"Statistics on Partitioned Tables – Part 5","date":"February 28, 2010","format":false,"excerpt":"Actually, before looking at any recent features, let me introduce one more aspect of the existing aggregation approach used by Oracle. The examples used to date have been based on INSERTing new rows into subpartitions and, although that's the approach used for some of our tables and will suit some\u2026","rel":"","context":"With 18 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":901,"url":"http:\/\/orcldoug.com\/blog\/2005\/11\/19\/another-interesting-lewis-tip\/","url_meta":{"origin":1030,"position":1},"title":"Another Interesting Lewis Tip","date":"November 19, 2005","format":false,"excerpt":"Updated 24th November 2005I wouldn't normally update an existing entry but there are some very interesting and important comments on this entry, particularly from Jonathan Lewis and Howard Rogers, that I'd recommend you take the time to read. Jonathan also mentions it on his website. There's much more in the\u2026","rel":"","context":"With 15 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":938,"url":"http:\/\/orcldoug.com\/blog\/2005\/09\/29\/html-db\/","url_meta":{"origin":1030,"position":2},"title":"HTML DB","date":"September 29, 2005","format":false,"excerpt":"Today I had one of those minor buzzes of excitement when I started to play around with Oracle's hosted HTML DB environment.Without wanting to go into my current feelings about work, you could say things are a little quiet. I'm trying to find things to do but I've joined just\u2026","rel":"","context":"With 2 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1011,"url":"http:\/\/orcldoug.com\/blog\/2006\/07\/04\/whats-a-data-warehouse-dba\/","url_meta":{"origin":1030,"position":3},"title":"What’s a ‘Data Warehouse DBA’?","date":"July 4, 2006","format":false,"excerpt":"I've seen that question asked often in forums and mail groups. It's easy to understand why there's so much confusion because, for most DBAs, if you've seen one database, you've seen them all. Or rather, you haven't seen any of them. What I mean is that every database that lands\u2026","rel":"","context":"With 14 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1602,"url":"http:\/\/orcldoug.com\/blog\/2010\/05\/07\/statistics-on-partitioned-tables-part-6d-copy_table_stats-a-light-bulb-moment\/","url_meta":{"origin":1030,"position":4},"title":"Statistics on Partitioned Tables – Part 6d – COPY_TABLE_STATS – A Light-bulb Moment","date":"May 7, 2010","format":false,"excerpt":"I'm pretty self-concious of the amount of waffle that surrounds any technical content here, so let's get the technical bit out of the way first, then the waffling can come later ...I finally tracked down the mistake I didn't make in part 6a, but thought I'd identified and fixed in\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1321,"url":"http:\/\/orcldoug.com\/blog\/2007\/09\/24\/11g-and-direct-path-reads\/","url_meta":{"origin":1030,"position":5},"title":"11g and direct path reads","date":"September 24, 2007","format":false,"excerpt":"Curioser and curioser ....I've been thinking about this on and off today and I really can't understand the behaviour I'm seeing. In fact, it's a good job it was a busy day or it might have sent me round the twist \ud83d\ude09 I've been playing around a little tonight, trying\u2026","rel":"","context":"With 27 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1030","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=1030"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1030\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=1030"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=1030"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=1030"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}