Home › Discussions › Questions & Answers › Auditing Index Usage for Slow Queries

Auditing Index Usage for Slow Queries

Avatar photoCustomer Questions & Answers 5 replies

We have a handful of critical reports that sporadically run very slowly. We need to check their execution plan history to see if they suddenly started skipping indexes. Which Blitz Report should I use?

5 replies

  • Support Enginatics

    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.

  • Avatar photoCustomer

    What does a change in the ‘Plan Hash Value’ indicate about the query execution?

  • Support Enginatics

    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.

  • Avatar photoCustomer

    If the DBA AWR SQL Performance Summary shows a high value for ‘Is Bind Sensitive’, what should I check?

  • Support Enginatics

    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.

  • You must be logged in to reply to this post.