Real-Time SQL Monitoring using DBMS_SQLTUNE, dbmssqltune

Real-Time SQL Monitoring using DBMS_SQLTUNE, dbmssqltune

Real-Time SQL Monitoring reports are available from three locations:

• Enterprise Manager-Click the "Performance" tab, then the "SQL Monitoring" link at the bottom-right of the page to display the "Monitored SQL Executions" screen. ClickSQL_IDOf interest to display the SQL monitoring report.
• SQL Developer-Available from the "Tools> Monitor SQL" menu.
• DBMS_SQLTUNE package.

In this article I will demonstrate the use ofDBMS_SQLTUNEPackage to display SQL monitoring reports without using Enterprise Manager or SQL Developer. This article has 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 are run in parallel, or consume 5 or more seconds of CPU or I/O in a single execution. this allows resource intensive SQL to be monitored as it is executing, as well as giving access to detailed information about queries once they are complete.

SQL monitoring requiresSTATISTICS_LEVELParameter to be set to 'category' or 'all', andCONTROL_MANAGEMENT_PACK_ACCESSParameter set to 'Diagnostic + tuning '.

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

TheMONITORHint switches on SQL monitoring for statements that wocould not otherwise initiate it.

SELECT /*+ 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;

REPORT_ SQL _MONITOR

TheREPORT_SQL_MONITORFunction is used to return a SQL monitoring report for a specific SQL statement. The SQL statement can be identified using a variety of parameters, but it will typically be identified usingSQL_IDParameter.

The function can accept must optional parameters, shown here, but most of the time you will probably only use the following.

• SQL_ID-SQL_IDOf the query of interest. When NULL (the default) the last monitored statement is targeted.
• SQL_EXEC_ID-WhenSQL_IDIs specified,SQL_EXEC_IDIndicates the individual execution of interest. When NULL (the default) the most recent execution of the statement targeted bySQL_IDIs assumed.
• REPORT_LEVEL-The amount of information displayed in the report. the basic allowed values are 'none', 'Basic ', 'typical' or 'all', but the information displayed can be modified further by adding (+) or subtracting (-) named report sections (eg. 'Basic + PLAN + BINDS 'or 'all-plan '). this is similar to the way DBMS_XPLAN output can be tailored in the later releases. I almost always use '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 way it is shown in Enterprise Manager.
• SESSION_ID-Targets a subset of queries based on the specified SID. UseSYS_CONTEXT('USERENV','SID')For the current session.

The report accesses several dynamic performance views, so you will most likely access it from a privileged user, or a user grantedSELECT_CATALOG_ROLERole.

To see it in action, first we make sure we have a monitored statement to work.

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 usingV$SQL_MONITORView. this view was present in Oracle 11g Release 1, but has additional columns in Oracle 11g Release 2, making it much more useful. 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 employees                                  FROM   emp e                                         JOIN dept d ON e.deptno = d.deptno                                  GROUP BY d.dname                                  ORDER BY d.dnameSQL>

OnceSQL_IDIs identified, we can generate a report usingREPORT_SQL_MONITORFunction.

SET LONG 1000000SET LONGCHUNKSIZE 1000000SET LINESIZE 1000SET PAGESIZE 0SET TRIM ONSET TRIMSPOOL ONSET ECHO OFFSET FEEDBACK 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 availableTYPEAre displayed below.

• TEXT
• HTML
• XML
• ACTIVE-Active HTML available in 11gR2 requires a download of Javascript libraries and a Flash movie from an Oracle website, so must be used on a PC connected to the internet, unless you download the relevant libraries and useBASE_PATHParameter in the function call to identify their location.

REPORT_ SQL _MONITOR_LIST

TheREPORT_SQL_MONITOR_LISTFunction was added in Oracle 11g Release 2 to generate a summary screen, similar to that on the "Monitored SQL Executions" page of Enterprise Manager. there are a number of parameters to filer the content of the report (shown here), but most of the time you will probably only useTYPEAndREPORT_LEVELParameters, similar to those inREPORT_SQL_MONITORFunction. 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 FEEDBACK 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 availableTYPEAre displayed below.

• TEXT
• HTML
• XML
• ACTIVE-Active HTML is not currently supported, but the parameter list, specificallyBASE_PATH, Suggest it will be supported in future.

REPORT_ SQL _DETAIL

Although not specified ented as part of Real-Time SQL Monitoring,REPORT_SQL_DETAILFunction added in Oracle 11g Release 2 returns a report containing SQL monitoring information. once again, it has several parameters (shown here), but you will 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 FEEDBACK 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 availableTYPEAre displayed below.

• XML
• ACTIVE-Active HTML is default type.

Active HTML Reports Offline

As mentioned previusly, by default Active HTML available in 11gR2 require a download of Javascript libraries and a Flash movie from an Oracle website, so must be used on a PC connected to the internet. an alternative to this is to download the relevant files to a HTTP server on your network (or local machine) and useBASE_PATHParameter to reference those files rather than the Oracle website.

To show this I will 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 inDBMS_SQLTUNEPackage, I useBASE_PATHParameter with the value of "http: // 192.168.0.4/sqlmon" so the active report will 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 FEEDBACK 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 two new dynamic performance views have been added 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

What are the two differences between local dynamic SQL Execution and DBMS_ SQL package execution? What is the DBMS_ SQL package?

Execute immeidiate and DBMS_ SQL locally are executed using SQL statements.
The former is convenient and easy to understand .. It is applicable to some simple SQL statements that are not particularly long and do not need to be executed repeatedly.
The latter is more efficient in multiple cyclic executions .. However, it is more complicated to use and hard to understand .. However, DBMS_ SQL supports ultra-long SQL statements and is better than execute immeidiate in complex dynamic SQL statements.
Execute immeidiate has a limit on the length of the executed SQL statement. If it is too long, it will not work ..

Error CS0246: the type or namespace name "SqlConnection" cannot be found (is the using command or assembly reference missing ?)

Connect to the database.
Add
Using System. Data. SqlClient; just reference it.