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
No comments:
Post a Comment