Real-TimeSQLMonitoringusingDBMS_SQLTUNE(三)

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF

SPOOL /host/report_sql_detail.htm
SELECT DBMS_SQLTUNE.report_sql_detail(
  sql_id       => '526mvccm5nfy4',
  type         => 'ACTIVE',
  report_level => 'ALL') AS report
FROM dual;
SPOOL OFF

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

Active HTML Reports Offline

As mentioned previously, by default Active HTML available in 11gR2 require a download of java script 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 use the BASE_PATH parameter 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/sqlmon
cd /var/www/html/sqlmon
wget --mirror --no-host-directories --cut-dirs=1 http://download.oracle.com/otn_software/emviewers/scripts/flashver.js
wget --mirror --no-host-directories --cut-dirs=1 http://download.oracle.com/otn_software/emviewers/scripts/loadswf.js
wget --mirror --no-host-directories --cut-dirs=1 http://download.oracle.com/otn_software/emviewers/scripts/document.js
wget --mirror --no-host-directories --cut-dirs=1 http://download.oracle.com/otn_software/emviewers/sqlmonitor/11/sqlmonitor.swf

When calling functions in the DBMS_SQLTUNE package, I use the BASE_PATH parameter 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 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF

SPOOL /host/report_sql_monitor.htm
SELECT DBMS_SQLTUNE.report_sql_monitor(
  sql_id       => '526mvccm5nfy4',
  type         => 'ACTIVE',
  report_level => 'ALL',
  base_path    => 'http://192.168.0.4/sqlmon') AS report
FROM 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.

For more information see: