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