[20150402]关于SQL Monitoring.txt

--今天学习sql monitor,oracle从11g开始,能够记录运行事件较长的sql语句,我自己在工作中很少使用这个功能,前几天看别人在优化时使用,
-- 自己也学习学习,这个是学习笔记.

1.相关参数:
SCOTT@test> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SCOTT@test> @hide _sqlmon
NAME                       DESCRIPTION                                                        DEFAULT_VALUE  SESSION_VALUE          SYSTEM_VALUE
-------------------------- ------------------------------------------------------------------ -------------- ---------------------- ----------------------
_sqlmon_binds_xml_format   format of column binds_xml in [G]V$SQL_MONITOR                     TRUE           default                default
_sqlmon_max_plan           Maximum number of plans entry that can be monitored. Defaults to 2 TRUE           80                     80
                           0 per CPU

_sqlmon_max_planlines      Number of plan lines beyond which a plan cannot be monitored       TRUE           300                    300
_sqlmon_recycle_time       Minimum time (in s) to wait before a plan entry can be recycled    TRUE           60                     60
_sqlmon_threshold          CPU/IO time threshold before a statement is monitored. 0 is disabl TRUE           5                      5
                           ed

-- 参数_sqlmon_threshold=5s 控制运行时间超过5s以上的才记录.其它

2.相关视图:
v$sql_monitor

3.也可以通过提示monitor将监测语句加入.
select /*+ monitor */ * from emp ;

4.我写的一些脚本:

$ cat sm.sql

SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR (sql_id => '&&1', report_level => 'ALL',type=>'TEXT')
          AS report
  FROM DUAL;

$ cat smh.sql
spool aa.html
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR (sql_id => '&&1', report_level => 'ALL',type=>'HTML')
          AS report
                    FROM DUAL;
spool off
--host "C:\Program Files (x86)\Mozilla Firefox\firefox.exe" aa.html
host links aa.html