Oracle – Configuring AWR Reports

oracle

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?

Best 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)

    • Related Question