[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