Real-time SQL monitoring using Dbms_sqltune

Real-time SQL monitoring reports is available from three locations:

• Enterprise Manager-click the ' Performance ' tab, then the ' SQL monitoring ' link at the bottom-right of the page to Displa y the "monitored SQL executions" screen. Click the SQL_ID interest to display the SQL monitoring report.
• SQL Developer-available from the "Tools > Monitor SQL" menu.
• Dbms_sqltune package.

In this article I'll demonstrate the use of the package to DBMS_SQLTUNE display SQL monitoring reports without using Enterprise Manager or SQL Developer. This article have been updated to include additional functionality introduced in Oracle 11g Release 2.

• Introduction
• MONITOR Hint
• Report_sql_monitor
• Report_sql_monitor_list
• Report_sql_detail
• Active HTML Reports Offline
• Views

Related articles.

• Explain Plan Usage
• Dbms_xplan:display Oracle Execution plans
• SQL Trace, 10046, trcsess and Tkprof in Oracle

Introduction

Oracle 11g automatically monitors SQL statements if they is run in parallel, or consume 5 or more seconds of CPU or I/O I n a single execution. This allows resource intensive SQL to be monitored as it's executing, as well as giving access to detailed information AB Out queries once they is complete.

SQL monitoring requires STATISTICS_LEVEL the parameter to BES set to ' typical ' or ' all ', and the CONTROL_MANAGEMENT_PACK_ACCESS parameter set to ' Diagnostic+tu NING '.

sql> conn/as sysdbaconnected.sql> SHOW PARAMETER statistics_levelname     TYPE VALUE-----------------------------------------------------------------------------statistics_level     string typicalsql> SHOW PARAMETER control_management_pack_accessname     TYPE VALUE-----------------------------------------------------------------------------Control_management_pack_ Access     string diagnostic+tuningsql>

MONITOR Hint

The MONITOR hint switches on SQL monitoring for statements This would not otherwise initiate it.

SELECT/*+ MONITOR */D.dname, Wm_concat (e.ename) as Employeesfrom   emp e       JOIN Dept D on e.deptno = D.deptnogroup B Y D.dnameorder by D.dname;

Report_sql_monitor

The REPORT_SQL_MONITOR function is used to return a SQL monitoring report for a specific SQL statement. The SQL statement can is identified using a variety of parameters, but it'll typically be identified using the SQL_ID par Ameter.

The function can accept many optional parameters, shown here, but most of the time you'll probably only use the Followin G.

• SQL_ID-The of the SQL_ID query of interest. When NULL (the default) of the last monitored statement is targeted.
• SQL_EXEC_ID-When SQL_ID the is specified, the SQL_EXEC_ID indicates the individual execution of interest. When NULL (the default) the most recent execution of the statement targeted by the is SQL_ID assumed.
• REPORT_LEVEL-The amount of information displayed in the. The basic allowed values is ' NONE ', ' basic ', ' typical ' or ' all ', but the information displayed can is modified further by adding (+) or subtracting (-) Named report sections (eg. ' BASIC +plan +binds ' or ' All-plan '). This was similar to the Dbms_xplan output can be tailored in the later releases. I almost always use the ' all '.
• TYPE-The format used to display the report (' TEXT ', ' HTML ', ' XML ' or ' ACTIVE '). The ' ACTIVE ' setting is new to Oracle 11g Release 2 and displays the output using HTML and Flash, similar to the the-the-it is shown in Enterprise Manager.
• SESSION_ID-Targets A subset of queries based on the specified SID. Use of the current SYS_CONTEXT(‘USERENV‘,‘SID‘) session.

The report accesses several dynamic performance views, so you'll most likely access it from a privileged user, or a user Granted the SELECT_CATALOG_ROLE role.

To see the IT in action, first we do sure we have a monitored statement to work with.

CONN scott/tigerselect/*+ MONITOR */D.dname, Wm_concat (e.ename) as Employeesfrom   emp e       JOIN Dept D on e.deptno = D.deptnogroup by D.dnameorder by D.dname;

Monitored statements can be identified using the V$SQL_MONITOR view. This view is present in Oracle 11g Release 1, but have additional columns in Oracle 11g Release 2, making it much more use Ful. It contains an entry for each execution monitored, so it can contain multiple entries for individual SQL statements.

conn/as sysdba--11gr1select sql_id, Statusfrom v$sql_monitor; sql_id STATUS--------------------------------526mvccm5nfy4 done (all ROWS) sql>--11gr2set linesize 200COLUMN sql_ Text FORMAT a80select sql_id, status, sql_textfrom v$sql_monitorwhere username = ' SCOTT '; sql_id STATUS sql_text------------------------------------------------------------------------------ ----------------------------------526mvccm5nfy4 Done (all ROWS) SELECT/*+ MONITOR */D.dname, Wm_concat (e.ename) as E Mployees from EMP e JOIN Dept D on e.deptno = d . Deptno GROUP by D.dname ORDER by D.dnamesql> 

/blockquote>

Once SQL_ID The is identified, we can generate a report using the REPORT_SQL_MONITOR function.

SET LONG 1000000SET longchunksize 1000000SET linesize 1000SET PAGESIZE 0SET TRIM ONSET trimspool ONSET ECHO OFFSET Feedbac K offspool/host/report_sql_monitor.htmselect dbms_sqltune.report_sql_monitor (  sql_id       = ' 526mvccm5nfy4 ',  type         = ' HTML ',  report_level = ' all ') as Reportfrom dual; SPOOL OFF

Examples of the output for each available is TYPE displayed below.

• TEXT
• Html
• Xml
• Active-active HTML available in 11gR2 requires a download of Javascript libraries and a Flash movie from an Oracle WEBSI TE, so must is used on a PC connected to the Internet, unless you download the relevant libraries and use the BASE_PATH parame ter in the function call to identify their location.

Report_sql_monitor_list

THE  report_sql_monitor_list  function was added in Oracle 11g Release 2 to generate a summary screens, similar to, and the "monitored SQL Executions" page of Enterprise Manager. There is a number of parameters to filer the content of the "the Report" (shown here), but most of the time you'll probably O nly use the  TYPE  and  report_level  parameters, similar to those in the   report_sql_monitor  function. The query below shows how the function can be used.

SET LONG 1000000SET longchunksize 1000000SET linesize 1000SET PAGESIZE 0SET TRIM ONSET trimspool ONSET ECHO OFFSET Feedbac K offspool/host/report_sql_monitor_list.htmselect dbms_sqltune.report_sql_monitor_list (  type         = ' HTML ' ,  report_level = ' all ') as Reportfrom dual; SPOOL OFF

Examples of the output for each available is TYPE displayed below.

• TEXT
• Html
• Xml
• Active-active HTML isn't currently supported, but the parameter list, specifically BASE_PATH the, suggest it'll be suppor Ted in the future.

Report_sql_detail

Although not documented as part of real-time SQL monitoring, the REPORT_SQL_DETAIL function added in Oracle 11g Release 2 Returns a re Port containing SQL monitoring information. Once again, it has several parameters (shown here), but you'll probably only use a subset of them to target specific SQL Statements, as shown below.

SET LONG 1000000SET longchunksize 1000000SET linesize 1000SET PAGESIZE 0SET TRIM ONSET trimspool ONSET ECHO OFFSET Feedbac K offspool/host/report_sql_detail.htmselect dbms_sqltune.report_sql_detail (  sql_id       = ' 526mvccm5nfy4 ',  type         = ' ACTIVE ',  report_level = ' all ') as Reportfrom dual; SPOOL OFF

Examples of the output for each available is TYPE displayed below.

• Xml
• Active-active HTML is the default type.

Active HTML Reports Offline

As mentioned previously, by default Active HTML available in 11gR2 require a download of Javascript libraries and a Flash Movie from an Oracle website, so must is used on a PC connected to the Internet. An alternative-to-download the relevant files to a HTTP server on your network (or local machine) and use the c2/> parameter to reference those files rather than the Oracle website.

To show this I'll create a new directory under a HTTP server on my network and download the relevant files to it.

mkdir-p/var/www/html/sqlmoncd/var/www/html/sqlmonwget--mirror--no-host-directories--cut-dirs=1 Http://download.oracle.com/otn_software/emviewers/scripts/flashver.jswget--mirror--no-host-directories-- Cut-dirs=1 Http://download.oracle.com/otn_software/emviewers/scripts/loadswf.jswget--mirror-- No-host-directories--cut-dirs=1 Http://download.oracle.com/otn_software/emviewers/scripts/document.jswget-- Mirror--no-host-directories--cut-dirs=1 http://download.oracle.com/otn_software/emviewers/sqlmonitor/11/ sqlmonitor.swf 

When calling functions in the package DBMS_SQLTUNE , I use BASE_PATH the parameter with the value of ' Http://192.168.0.4/sqlmon ' so th E Active report would use the local copies of the files, rather than accessing them from the Internet.

SET LONG 1000000SET longchunksize 1000000SET linesize 1000SET PAGESIZE 0SET TRIM ONSET trimspool ONSET ECHO OFFSET Feedbac K offspool/host/report_sql_monitor.htmselect dbms_sqltune.report_sql_monitor (  sql_id       = ' 526mvccm5nfy4 ',  type         = ' ACTIVE ',  report_level = ' All ',  base_path    = ' Http://192.168.0.4/sqlmon ') As Reportfrom dual; SPOOL OFF

Views

The SQL monitoring functionality accesses a number of existing views, but both new dynamic performance views has been Adde D specifically as part of it.

• V$sql_monitor
• V$sql_plan_monitor

For more information see:

• Oracle Database 11g:real-time SQL Monitoring
• Real-time SQL monitoring
• Dbms_sqltune
• MONITOR Hint
• Explain Plan Usage
• Dbms_xplan:display Oracle Execution plans
• SQL Trace, 10046, trcsess and Tkprof in Oracle

Real-time SQL monitoring using Dbms_sqltune