AWR report for specific SQL query in oracle

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 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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.