Generate AWR report for a particular SQL

Once you have identified that a particular SQL is causing issues, you can generate the AWR report for a particular SQL between two specific snap_id's for further analysis. SQL "awrsqrpt.sql" is located under $ORACLE_HOME/rdbms/admin

$ORACLE_HOME/rdbms/admin/awrsqrpt.sql

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 66397
Begin Snapshot Id specified: 66397

Enter value for end_snap: 66398
End   Snapshot Id specified: 66398

Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value for sql_id: 8p6wy9fr8m0sn
SQL ID specified:  8p6wy9fr8m0sn

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrsqlrpt_2_66397_66398.html.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name awrsqlrpt_2_66397_66398.html

awrsqlrpt_2_66397_66398.html => This report gives lot of good information about that particular SQL including the Plan Statistics and EXECUTION PLAN