Sqlmonitor statement tuning

What statements are being monitored

• By default, a SQL statement that either runs in parallel or has consumed at least 5 seconds of combined CPU and I/O time in a single execution will be monitored.
• It is also possible to force monitoring to occur for any SQL statement by simply adding the MONITOR hint to the statement: SELECT /*+ MONITOR */ sysdate from dual;
• If you can’t modify the SQL statement you can still force monitoring by setting the event “sql_monitor” with a list of SQL_IDs for the statements you want to monitor at the system level: ALTER SYSTEM SET EVENTS 'sql_monitor [sql: 5hc07qvt8v737|sql: 9ht3ba3arrzt3] force=true';

Generate a historical report for a single SQL Statement manually

As of Oracle version 12c you can also create historical reports. To manually generate a persisted SQL Monitor report for a single SQL statement, you will first need to find its REPORT_ID and then use the PL/SQL function DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL to extract the report.

Step 1: Get the report_id:

SELECT report_id
FROM dba_hist_reports
WHERE dbid = 3043771278
AND component_name = 'sqlmonitor'
AND report_name = 'main'
AND period_start_time BETWEEN
To_date('26/11/2018 13:30:00','DD/MM/YYYY HH24:MI:SS')
AND
To_date('26/11/2018 14:30:00','DD/MM/YYYY HH24:MI:SS')
AND key1 = 'dhwbdhak0qahg';

 REPORT_ID
----------
    105340
    105343
    105345
    105344

Note that you will get a REPORT_ID for every AWR snapshot in the choosen period.

Step 2: Generate the report

SET echo ON
SET trimspool ON
SET TRIM ON
SET pages 0
SET linesize 32767
SET LONG 10000000
SET longchunksize 1000000
spool statement_history.html
 
SELECT dbms_auto_report.Report_repository_detail(rid=>105345, TYPE=>'active')
FROM dual;

Generate a historical report for all monitored SQL Statements manually

• Starting in Oracle Database 12c, you can use the perfhubrpt.sql script, in the $ORACLE_HOME/rdbms/admin directory, to generate a PerfHub for a given time period, which will include SQL Monitor reports for all of the monitored SQL statements during that period.
• The script will prompt you for the report level (default is typical but better to use all), the database id (default is the database you are on), instance number (default is the instance you are on) and the time period you are interested in.
• The output of the perfhubrpt.sql is an html file that is an historical view of the EM performance hub for the specified time period.

SQL> @?/rdbms/admin/perfhubrpt.sql 

DATE_FMT
--------------------------------------------------------------------------------
mm/dd/yyyy hh24:mi:ss

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~                   PERFHUB ACTIVE REPORT                              ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~  This script will generate PerfHub active report according to        ~
~  users input.  The script will prompt users for the                  ~
~  following information:                                              ~
~     (1) report level: basic, typical or all                          ~
~     (2) dbid                                                         ~
~     (3) instance id                                                  ~
~     (4) selected time range                                          ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Specify the report level
~~~~~~~~~~~~~~~~~~~~~~~~
*  Please enter basic, typical or all
*  Report level "basic" - include tab contents but no further details
*  Report level "typical" - include tab contents with details for top
*                           SQL statements
*  Report level "all" - include tab contents with details for all SQL
*                       statements

Please enter report level [typical]: all

Specify the database ID -- accept default

Specify the Instance Number -- accept default

Please enter start time [11/27/2018 08:32:58]: 11/26/2018 13:30:00

Please enter end time [11/27/2018 08:37:58]: 11/26/2018 14:30:00

Please enter report name [perfhub_ht_11261430.html]: -- accept default

Generating report perfhub_ht_11261430.html ...

Report written to perfhub_ht_11261430.html

You can then query the report and drill down to individual SQL statements. It is generated by default in $ORACLE_HOME/bin