AWR report for specific SQL Query in Oracle

You have option to fetch the specific report for a particular SQL Query for check the performance of the SQL Query.
In this you can use the oracle_home\rdbms\admin directory which is having file AWRSQRPT.SQL script. When you connect with sysdba user and fetch the report by executing the Script (AWRSQRT.SQL) it will ask for SQL_ID input as parameter. So first you have to find the SQL id and then fetch the AWR report for particular SQL Query.

1. Fetch the SQL ID

set pagesize 100 linesize 132
col SQL_ID format a15
col sql_text format a60
SELECT sql_id,sql_TEXT
FROM V$SQL
WHERE AND SQL_TEXT like '%Select * from dual%';


2. Execute the AWR script and put the SQL_ID got in step first.

-- Connect with SQLPLUS
sqlplus / as sysdba
-- Run the following AWR Report and put the SQL_ID
@?/rdbms/admin/awrsqrpt.sql;

Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value for sql_id:

3. You will get the report.