I recently had to analyze a system in respect of index utilization. The application has dozens of indexes and we have to find out, which ones are really needed and which ones are candidates for removal. <\/p>\n
There is a nice feature, called Index Monitoring which switches a flag if the index is used.<\/p>\n
ALTER INDEX schema.index MONITORING USAGE;<\/pre>\nInternally, a recursive SQL Statement is executed for every parsing of a SQL:<\/p>\n
update object_usage set flags=flags+1 where obj#=:1 and bitand(flags, 1)=0;<\/pre>\nTo check whether the index was used, you can query the view v$object_usage. Unfortunately, the view definition contains a where clause to list only indexes of the current schema. <\/p>\n
\r\n where io.owner# = userenv('SCHEMAID')<\/pre>\nTherefore, you can create a new view or just omit the limitation from the where clause:<\/p>\n
create or replace view V$ALL_OBJECT_USAGE \r\n (OWNER,\r\n INDEX_NAME, \r\n TABLE_NAME, \r\n MONITORING, \r\n USED, \r\n START_MONITORING, \r\n END_MONITORING) \r\n as \r\n select u.name, io.name, t.name, \r\n decode(bitand(i.flags, 65536), 0, 'NO', 'YES'), \r\n decode(bitand(ou.flags, 1), 0, 'NO', 'YES'), \r\n ou.start_monitoring, \r\n ou.end_monitoring \r\n from sys.user$ u, sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou \r\n where i.obj# = ou.obj# \r\n and io.obj# = ou.obj# \r\n and t.obj# = i.bo#\r\n and u.user# = io.owner#<\/pre>\nHowever, what is less known is that the DBMS_STATS can in some cases manipulate these flags. The issue is tracked with “Bug 6798910 – DBMS_STATS or EXPLAIN PLAN trigger index usage monitoring”. If you are using 10.2.0.4 and you are gathering stats manually with “CASCADE=>TRUE”, then the monitoring flag is set to “YES”. Obviously, in this case you can not identify candidate indexes for removal.<\/p>\n","protected":false},"excerpt":{"rendered":"
I recently had to analyze a system in respect of index utilization. The application has dozens of indexes and we have to find out, which ones are really needed and which ones are candidates for removal. There is a nice feature, called Index Monitoring which switches a flag if the index is used. ALTER INDEX […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[13,47,5],"tags":[],"class_list":["post-651","post","type-post","status-publish","format-standard","hentry","category-10g","category-bugs","category-oracle-database"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/651","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/comments?post=651"}],"version-history":[{"count":9,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/651\/revisions"}],"predecessor-version":[{"id":1071,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/651\/revisions\/1071"}],"wp:attachment":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/media?parent=651"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/categories?post=651"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/tags?post=651"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}