SQL monitoring queries

SQL monitoring

=================

SQL> SHOW PARAMETER statistics_level

*Note :Required the STATISTICS_LEVEL parameter to be set to 'TYPICAL' or 'ALL', and the 

CONTROL_MANAGEMENT_PACK_ACCESS parameter set to 'DIAGNOSTIC+TUNING'.

NAME      TYPE VALUE

------------------------------------ ----------- ------------------------------

statistics_level      string TYPICAL

SQL> SHOW PARAMETER control_management_pack_access

NAME      TYPE VALUE

------------------------------------ ----------- ------------------------------

control_management_pack_access      string DIAGNOSTIC+TUNING

Monitored statements can be identified using the V$SQL_MONITOR

===============================================================

SET LINESIZE 200

COLUMN sql_text FORMAT A80

SELECT sql_id, status, sql_text

FROM   v$sql_monitor

WHERE  username = 'HABIB';

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.dname

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

=====================================================================

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         => 'HTML',

  report_level => 'ALL') AS report

FROM dual;

SPOOL OFF

Output for each available TYPE are:

===================================

HTML

TEXT

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 use the BASE_PATH parameter in the function call to identify 

their location.

REPORT_SQL_MONITOR_LIST

=========================

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_list.htm

SELECT DBMS_SQLTUNE.report_sql_monitor_list(

  type         => 'HTML',

  report_level => 'ALL') AS report

FROM dual;

SPOOL OFF

REPORT_SQL_DETAIL

==================

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

Views

======

V$SQL_MONITOR

V$SQL_PLAN_MONITOR