Find the most used indexes
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 document with their definition, I checked which indexes were actively used since adding the new indexes.
I come up with this query:
select distinct object_owner,object_name from dba_hist_sql_plan where plan_hash_value in (select plan_hash_value from
(
/* Day after adding new indexes */
with snapshot as (select min(snap_id) snap_id from dba_hist_snapshot where begin_interval_time>=to_date('27-MAY-2021','DD-MON-YYYY')),
/* Plans using new indexes */
plans as (select plan_hash_value from dba_hist_sql_plan where object_name like '%\_ANJO\_%' escape '\')
/* Plans using new indexes after my intervention */
select plan_hash_value, sum(executions_delta) execs from dba_hist_sqlstat where snap_id> (select snap_id from snapshot)
and plan_hash_value in (select plan_hash_value from plans)
group by plan_hash_value
having sum(executions_delta)>10
))
and object_name like '%\_ANJO\_%' escape '\'
order by 1,2;
I’ve limited the search to the indexes I added (having _ANJO_ in their name).
Surprise, all of the indexes I added were actively used. Hopefully they will add them again for good.