[20150402]關於SQL Monitoring.txt

[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