Hi, We are using version 11.2.0.4 of oracle. We got recommendation from team to remove indexes from few of the staging table which were there since long back and they are adding major overhead to the DML's and not of much benefit to SELECT queries as because mostly stage tables are being queried to scan all of the records from them. And in some cases due to wrong join cardinality estimation optimizer was favoring these indexes over FTS. And these indexes are also not used for data integrity check too like any primary-foreign key relationship etc. Now during this exercise , as we retain ~40 days worth of AWR data so, we used to scan dba_hist_sql_plan and dba_hist_active_sess_history and search for the exact index usage and the related query from dba_hist_sqltext+gv$sqlarea and used to check/verify manually how they will behave with absence of the index. Then we take decision to make them invisible for some days and then drop them finally. But once we failed in this approach as because , one of the query got missed during this exercise as because it was executing very fast(using one of the index in stage table) with very minimal DB resource so was not getting logged into the AWR/ASH views, and we got into a issue in production system and then endup making that index visible.
So my question is , is there any other way by which we can see/validate all the impacted queries whoever is using the index without missing?
Note- We perform this verification on production as because on test environment the plan were not exactly same as production and data volume also not exactly same, so there are always chances of missing something.