Configuring Oracle AWR reports

Posted on

Question :

Is it possible to configure Oracle so that AWR reports contain more that the top ten SQL queries by elapsed time and also include the explain plan for those queries?

Answer :

You can use DBA_HIST_SQL_PLAN view to get the execution plan for SQL statement. In order to include more SQLs in your report you have to change AWR setting as shown below.

BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHORT_SETTINGS(
RETENTION => 7200,
INTERVAL =>60 ,
TOPNSQL =>10 , --Here you can define the number of SQL to collect at each AWR snapsho
DBID => 123661118);
END;
/

You can get the SQL_ID from the AWR reports and can get their execution plan as shown below:

SQL>select plan_table_output from table (dbms_xplan.display_awr('&SQLID'));

Another way is to use Oracle supplied script called awrsqrpt.sql which can be found under $ORACLE_HOME/rdbms/admin directory.
During the execution of the script it asks for the SQL_ID for which you want to get the execution plan.

References:

  • MODIFY_SNAPSHOT_SETTINGS Procedures
  • DBA_HIST_SQL_PLAN
  • How to Control the Number of SQL Statements and other information displayed in AWR Report (Doc ID 1357637.1)

  • Leave a Reply

    Your email address will not be published. Required fields are marked *