Quick note to myself. If I am tuning a query by its SQL_ID I use these scripts:
sqlstat.sql – shows execution history of SQL_ID from AWR
getplans.sql – shows all plans for SQL_ID from AWR
vsqlarea.sql – shows current executions of SQL_ID from V$ view
extractplansqlid.sql – new script to pull current plan of SQL_ID from V$ views
I mainly mine the history from the AWR but I have been looking at V$ view information more recently, especially after putting in a fix to a slow query plan.