Script to extract plans from AWR

I use this script all the time to get the plans for a given SQL statement I’ve identified from an AWR report:

getplans.sql

Recently I’ve found out that you can do something like this with the delivered awr script $ORACLE_HOME/rdbms/admin/awrsqrpt.sql

But, I like my version because it dumps out a text version of the plans and it shows me the last AWR snapshot that had the given plan.  This is helpful if you want to tell when the plan changed.

Here is how you run it:

SQL> @getplans j3h4j56k6j

Where j3h4j56k6j is a sql_id you’ve found in your AWR report.

– Bobby

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.

This entry was posted in Uncategorized. Bookmark the permalink.

One Response to Script to extract plans from AWR

  1. Pingback: Working on Performance Toolkit | Bobby Durrett's DBA Blog

Leave a Reply