Generate the AWR report for specific SQL Query in Oracle

You can obtain a specific report for an SQL query to check its performance. To do this, use the AWRSQRPT.SQL script located in the oracle_home\rdbms\admin directory. When connected as the sysdba user, running the script will prompt you for a SQL_ID. First, find the SQL ID, then generate the AWR report for that particular SQL query.

1. Fetch the SQL ID from SQL Query in Oracle

select * from v$sql;

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 format -- > HTML
-- Enter the number of days value --> 2 --days

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

3. You will get the report.