The dbms_xplan.display_awr procedure van be very helpful in diagnosing
Oracle performance issues. See these important notes on
tracking the history of execution plans for a SQL statement.
Using the ASH tables, I was able to go through different times of day and
compare reports from times where performance was good, with those times when
performance was bad.
The ASH components allowed me to compare SQL_IDs between different times of
day to see which queries seemed to be going through dramatic changes. Once a
SQL_ID was found that experienced such a dramatic change, I used
DBMS_XPLAN.DISPLAY_AWR to find all the different iterations of the query.
In case you’ve never used it, DBMS_XPLAN.DISPLAY_AWR is very useful. It
allows you to input only a SQL_ID, and Oracle will show you the explain plans
for that SQL_ID as recorded in the AWR. For instance:
select * from TABLE(dbms_xplan.display_awr(’93djdy6ss3’));
The next step is to look through the results to find any inconsistencies
large enough to result in huge differences in performance.
Follow the link for more information on the
dbms_xplan utility.
