Demystifying Oracle SQL Monitor: A Comprehensive Guide

Introduction

Oracle Database's SQL Monitor is a powerful tool that provides deep insights into the execution of SQL statements, offering DBAs and developers crucial information for optimizing query performance. In this guide, we will delve into the various aspects of SQL Monitor, from understanding its default behavior to generating real-time and historical reports. Whether you're new to SQL Monitor or a seasoned user, this guide aims to provide valuable insights for effectively utilizing this tool.

Default Behavior and Forcing Monitoring

SQL Monitor automatically tracks SQL statements that run in parallel or consume at least 5 seconds of combined CPU and I/O time during a single execution. However, if you want to monitor specific SQL statements, you can include the `MONITOR` hint within the statement itself. For instance:

SQL

SELECT /*+ MONITOR */ count(*) from table

you can force monitoring by setting the event "sql_monitor" at the system level:

SQL

ALTER SYSTEM SET EVENTS 'sql_monitor [sql: sql-Id|sql: sql-id] force=true';

Adjusting the Monitoring Threshold

The default threshold for SQL Monitor is set at 5 seconds of combined CPU and I/O time. However, you can adjust this threshold by changing the underscore parameter `_sqlmon_threshold`. Keep in mind that increasing the threshold might cause monitored executions to be aged out of the SQL Monitor buffer faster. It's generally recommended to use underscore parameters sparingly and preferably at the session level to avoid undesirable side effects.

SQL Monitor reports in Oracle Database have evolved over time. In Oracle Database 11g, the information needed for generating reports was primarily available in the dynamic performance view V$SQL_MONITOR_*, and it wasn't persisted to disk. However, starting with Oracle Database 12c, SQL Monitor reports are stored in the data dictionary table DBA_HIST_REPORTS.

Generating Real-time SQL Monitor Reports

Generating real-time SQL Monitor reports can be accomplished through various means:

1. **Enterprise Manager (EM)**: Access the Performance Hub in the Performance drop-down menu, then click on the Monitored SQL tab to select and view monitored SQL statements.

2. **SQL Developer**: Navigate to the tools menu and select Real-Time SQL Monitor to view available monitored SQL statements.

3. **Command Line**: Use the PL/SQL functions `DBMS_SQLTUNE.REPORT_SQL_MONITOR` or `DBMS_SQL_MONITOR` to generate reports. For instance, in SQL*Plus:

SQL

SELECT dbms_sqltune.Report_sql_monitor(SQL_ID=>'&sql_id', TYPE=>'active') FROM dual;

Text sql Monitor report. 

SET LONG 1000000 

SET LONGCHUNKSIZE 1000000

SET LINESIZE 1000

SET PAGESIZE 0

SET TRIM ON

SET TRIMSPOOL ON

SET ECHO OFF

SET FEEDBACK OFF

select dbms_sqltune.report_sql_monitor(

  sql_id => '&v_sql_id.',

  type => 'TEXT',

  report_level => 'ALL',

  inst_id => '&v_instance.'

)  report

from dual;

REPORT_SQL_MONITOR_LIST (TEXT) (active from last 30 min)

SET LONG 1000000

SET LONGCHUNKSIZE 1000000

SET LINESIZE 1000

SET PAGESIZE 0

SET TRIM ON

SET TRIMSPOOL ON

SET ECHO OFF

SET FEEDBACK OFF

SELECT DBMS_SQL_MONITOR.report_sql_monitor_list(

  active_since_date => sysdate - 30/(24*60),

  type => 'TEXT',

  report_level => 'ALL') AS report

FROM dual;

select 

        dbms_sqltune.report_sql_monitor_list(

                active_since_date       => sysdate - 30 / (24*60),

                type                    => 'TEXT'

        ) text_line 

from    dual

;

REPORT_SQL_MONITOR_LIST (TEXT) (for specific sql_id).you will get start and end time in HTML report.

SET LONG 1000000

SET LONGCHUNKSIZE 1000000

SET LINESIZE 1000

SET PAGESIZE 0

SET TRIM ON

SET TRIMSPOOL ON

SET ECHO OFF

SET FEEDBACK OFF

SELECT DBMS_SQL_MONITOR.report_sql_monitor_list(

  sql_id => '&sql_id',

  type => 'TEXT',

  report_level => 'ALL') AS report

FROM dual;

Conclusion

Oracle SQL Monitor is an invaluable tool for optimizing query performance, providing detailed insights into SQL statement execution. Understanding its default behavior, forcing monitoring, and effectively generating real-time and historical reports are essential skills for database administrators and developers seeking to enhance their database performance. By leveraging the capabilities of SQL Monitor, you can identify bottlenecks, improve query efficiency, and ensure the smooth operation of your Oracle Database.