dbms_xplan.display_awr tips Oracle Tips by Steve Karam June 21, 2015

 

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.

---