SQL Monitor in Oracle 11gR2

SQL Monitor in Oracle 11gR2

SQL monitor active reports can be generated directly from EM live UI while viewing a detailed SQL monitor report. There (see save/send e-mail buttons on the top right of that page), the SQL monitor detail page can be either saved or sent by e-mail as an active report. 
Alternatively, the active report can be directly produced using command line by invoking the PL/SQL procedure dbms_sqltune.report_sql_monitor() using "active" as the report type. For example, the following SQL*Plus script shows how to generate an active report for the statement that was monitored last by Oracle:

The REPORT_SQL_MONITOR function 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 using the SQL_ID parameter.

Monitored statements can be identified using the V$SQL_MONITOR view. 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.

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

-- Find which query / user is monitored:

SET LINESIZE 200
COLUMN sql_text FORMAT A80

SELECT sql_id, status, sql_text
FROM   v$sql_monitor
WHERE  username = 'HR';

Identify which sql_id to be reported. Once the SQL_ID is identified, we can generate a report using the REPORT_SQL_MONITOR function.

-- Report the monitored sql_id/ session_id

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 /u04/reports/report_sql_monitor.txt;

SELECT DBMS_SQLTUNE.report_sql_monitor(
  sql_id       => '526mvccm5nfy4',
  type         => 'TEXT',
  report_level => 'ALL') AS report
FROM dual;
SPOOL OFF

OR

SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR
            ( sql_id         => 'a71hfjg8576s2', 
              session_id     => 1234,
              session_serial => 12,
              report_level   => 'all',
              type           => 'ACTIVE' )
  FROM dual;


The resulting file report_sql_monitor.txt must be edited to remove the header (first line in the file) and the last line (the spool off). 

Note:

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

•TEXT
•HTML
•XML
•ACTIVE 

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 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 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 500
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF

SPOOL /u04/reports/report_sql_detail.htm
SELECT DBMS_SQLTUNE.report_sql_detail(
  sql_id       => '526mvccm5nfy4',
  type         => 'ACTIVE',
  report_level => 'ALL') 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_MONITOR
•V$SQL_PLAN_MONITOR

Check the below link from Oracle for more details...

Real-Time SQL Monitoring