{"id":490,"date":"2021-06-09T09:43:21","date_gmt":"2021-06-09T07:43:21","guid":{"rendered":"https:\/\/anjo.pt\/keyword-oracle\/?p=490"},"modified":"2021-06-09T09:43:24","modified_gmt":"2021-06-09T07:43:24","slug":"find-the-most-used-indexes","status":"publish","type":"post","link":"https:\/\/anjo.pt\/keyword-oracle\/2021\/06\/09\/find-the-most-used-indexes\/","title":{"rendered":"Find the most used indexes"},"content":{"rendered":"\n<p>At a customer I was asked to check for missing indexes and add them. Some days later the application automatically dropped them. The customer come back to me and asked to check <strong>which indexes were the most important<\/strong>.<\/p>\n\n\n\n<p>As maybe not all my added indexes were needed and used, instead of just sending (again) the document with their definition, I checked <strong>which indexes were actively used since adding the new indexes<\/strong>.<\/p>\n\n\n\n<p>I come up with this query:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nselect distinct object_owner,object_name from dba_hist_sql_plan where plan_hash_value in (select plan_hash_value from \n(\n\/* Day after adding new indexes *\/\nwith snapshot as (select min(snap_id) snap_id from dba_hist_snapshot where begin_interval_time&gt;=to_date(&#039;27-MAY-2021&#039;,&#039;DD-MON-YYYY&#039;)),\n\/* Plans using new indexes *\/\nplans as (select plan_hash_value from dba_hist_sql_plan where object_name like &#039;%\\_ANJO\\_%&#039; escape &#039;\\&#039;)\n\/* Plans using new indexes after my intervention *\/\nselect plan_hash_value, sum(executions_delta) execs from dba_hist_sqlstat where snap_id&gt; (select snap_id from snapshot)\n  and plan_hash_value in (select plan_hash_value from plans)\n  group by plan_hash_value\n  having sum(executions_delta)&gt;10\n))\nand object_name like &#039;%\\_ANJO\\_%&#039; escape &#039;\\&#039; \norder by 1,2;\n<\/pre><\/div>\n\n\n<p>I&#8217;ve limited the search to the indexes I added (having _ANJO_ in their name).<\/p>\n\n\n\n<p>Surprise, all of the indexes I added were actively used. Hopefully they will add them again for good.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>At a customer I was asked to check for missing indexes and add them. Some days later the application automatically dropped them. The customer come back to me and asked to check which indexes were the most important. As maybe not all my added indexes were needed and used, instead of just sending (again) the [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,13],"tags":[],"class_list":{"0":"post-490","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-oracle","7":"category-performance","8":"czr-hentry"},"_links":{"self":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/490","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/comments?post=490"}],"version-history":[{"count":1,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/490\/revisions"}],"predecessor-version":[{"id":491,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/490\/revisions\/491"}],"wp:attachment":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/media?parent=490"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/categories?post=490"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/tags?post=490"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}