Auditing Index Usage for Slow Queries

You have two main options, depending on when the slow run occurred. Use DBA AWR SQL Execution Plan History for past runs, or DBA SGA SQL Execution Plan History for recent runs currently in memory. Both retrieve the plan history for a particular SQL_ID.

The ‘Plan Hash Value’ is an identifier for one particular execution plan. A change indicates that the optimizer chose a different path (e.g., stopping using an index and starting a full table scan), even if the SQL text itself hasn’t changed.

If ‘Is Bind Sensitive’ is ‘Y’, it means the DB might consider a different explain plan for different bind values (Adaptive Cursor Sharing). For EBS transactional SQL, the path usually shouldn’t change, so ‘Y’ often indicates ‘instable’ SQL where the optimizer is struggling to find the best path.