in, 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 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.
XMLACTIVE - Active HTML is default type.
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.
V$SQL_MONITORV$SQL_PLAN_MONITOR
For more information see:
Oracle Database 11g: Real-Time SQL MonitoringReal-Time SQL MonitoringDBMS_SQLTUNEMONITOR HintExplain Plan UsageDBMS_XPLAN : Display Oracle Execution PlansSQL trace, 10046, trcsess and tkprof in Oracle