I use this script all the time to get the plans for a given SQL statement I’ve identified from an AWR report:
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.