DBMS_MONITOR包

為了使跟蹤更加靈活，oracle 10g後dbms_monitor就出現了，可以說它的靈活性是比以往的任何跟蹤工具都要強，它可以跟蹤指定的session，module或者整個資料庫，你可以用它來明確的指定是否開啟對繫結變數或者等待事件的追蹤，詳細的dbms_monitor過程和函式可以用desc dbms_monitor檢視或者檢視官方文件。

——desc檢視包的詳細資訊：

SYS@ORA11GR2>desc dbms_monitor

PROCEDURE CLIENT_ID_STAT_DISABLE

Argument Name Type In/Out Default?

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

CLIENT_ID VARCHAR2 IN

PROCEDURE CLIENT_ID_STAT_ENABLE

Argument Name Type In/Out Default?

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

CLIENT_ID VARCHAR2 IN

PROCEDURE CLIENT_ID_TRACE_DISABLE

Argument Name Type In/Out Default?

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

CLIENT_ID VARCHAR2 IN

PROCEDURE CLIENT_ID_TRACE_ENABLE

Argument Name Type In/Out Default?

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

CLIENT_ID VARCHAR2 IN

WAITS BOOLEAN IN DEFAULT

BINDS BOOLEAN IN DEFAULT

PLAN_STAT VARCHAR2 IN DEFAULT

PROCEDURE DATABASE_TRACE_DISABLE

Argument Name Type In/Out Default?

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

INSTANCE_NAME VARCHAR2 IN DEFAULT

PROCEDURE DATABASE_TRACE_ENABLE

Argument Name Type In/Out Default?

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

WAITS BOOLEAN IN DEFAULT

BINDS BOOLEAN IN DEFAULT

INSTANCE_NAME VARCHAR2 IN DEFAULT

PLAN_STAT VARCHAR2 IN DEFAULT

PROCEDURE SERV_MOD_ACT_STAT_DISABLE

Argument Name Type In/Out Default?

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

SERVICE_NAME VARCHAR2 IN

MODULE_NAME VARCHAR2 IN

ACTION_NAME VARCHAR2 IN DEFAULT

PROCEDURE SERV_MOD_ACT_STAT_ENABLE

Argument Name Type In/Out Default?

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

SERVICE_NAME VARCHAR2 IN

MODULE_NAME VARCHAR2 IN

ACTION_NAME VARCHAR2 IN DEFAULT

PROCEDURE SERV_MOD_ACT_TRACE_DISABLE

Argument Name Type In/Out Default?

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

SERVICE_NAME VARCHAR2 IN

MODULE_NAME VARCHAR2 IN DEFAULT

ACTION_NAME VARCHAR2 IN DEFAULT

INSTANCE_NAME VARCHAR2 IN DEFAULT

PROCEDURE SERV_MOD_ACT_TRACE_ENABLE

Argument Name Type In/Out Default?

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

SERVICE_NAME VARCHAR2 IN

MODULE_NAME VARCHAR2 IN DEFAULT

ACTION_NAME VARCHAR2 IN DEFAULT

WAITS BOOLEAN IN DEFAULT

BINDS BOOLEAN IN DEFAULT

INSTANCE_NAME VARCHAR2 IN DEFAULT

PLAN_STAT VARCHAR2 IN DEFAULT

PROCEDURE SESSION_TRACE_DISABLE

Argument Name Type In/Out Default?

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

SESSION_ID BINARY_INTEGER IN DEFAULT

SERIAL_NUM BINARY_INTEGER IN DEFAULT

PROCEDURE SESSION_TRACE_ENABLE

Argument Name Type In/Out Default?

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

SESSION_ID BINARY_INTEGER IN DEFAULT

SERIAL_NUM BINARY_INTEGER IN DEFAULT

WAITS BOOLEAN IN DEFAULT

BINDS BOOLEAN IN DEFAULT

PLAN_STAT VARCHAR2 IN DEFAULT

——查詢生成trace檔案的名稱

SYS@ORA11GR2>SELECT c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace

2 FROM v$process a, v$session b, v$parameter c, v$instance d

3 WHERE a.addr = b.paddr

4 AND b.audsid = userenv('sessionid')

5 AND c.name = 'user_dump_dest';

TRACE

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

/u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_ora_24482.trc

/u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_ora_10479.trc

/u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_ora_10495.trc

SYS@ORA11GR2>

——此時，trace檔案還沒有建立

SYS@ORA11GR2>!ls /u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_ora_24482.trc

ls: /u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_ora_24482.trc: No such file or directory

——Enable生成跟蹤檔案

SYS@ORA11GR2>exec dbms_monitor.session_trace_enable

PL/SQL procedure successfully completed.

——此時，trace檔案已經生成

SYS@ORA11GR2>!ls /u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_ora_24482.trc

/u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_ora_24482.trc

——執行一條sql語句

SYS@ORA11GR2>select count(*) from all_objects;

測試的時候最好不要統計這個檢視，因為它涉及到的基本很多，生成trace檔案很長

COUNT(*)

----------

85023

——Disable生成跟蹤檔案

SYS@ORA11GR2>exec dbms_monitor.session_trace_disable

PL/SQL procedure successfully completed.

——檢視生成的trace檔案

[oracle@wang trace]$ ls ORA11GR2_ora_24482.trc

ORA11GR2_ora_24482.trc

[oracle@wang trace]$ pwd

/u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace

[oracle@wang trace]$

——利用tkprof工具格式化trace檔案，格式化後生成的檔名字為monitor.tkf

[oracle@wang trace]$ tkprof ORA11GR2_ora_24482.trc monitor.tkf

TKPROF: Release 11.2.0.4.0 - Development on Wed Oct 12 22:59:04 2016

驗證：

[oracle@wang trace]$ ls monitor.tkf

monitor.tkf

[oracle@wang trace]$

——最後參看trace檔案：

省略…………………………………….

*******************************************************************

SQL ID: 9tz4qu4rj9rdp Plan Hash: 453053801

select count(*)

from

all_objects

call count cpu elapsed disk query current rows

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

Parse 1 0.05 0.06 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 2 0.35 0.33 0 15014 0 1

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

total 4 0.40 0.39 0 15014 0 1

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS

Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation

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

1 1 1 SORT AGGREGATE (cr=19033 pr=0 pw=0 time=424275 us)

85023 85023 85023 FILTER (cr=19033 pr=0 pw=0 time=393443 us)

86997 86997 86997 HASH JOIN (cr=1079 pr=0 pw=0 time=94053 us cost=301 size=9566480 card=86968)

92 92 92 INDEX FULL SCAN I_USER2 (cr=1 pr=0 pw=0 time=117 us cost=1 size=368 card=92)(object id 47)

86997 86997 86997 HASH JOIN (cr=1078 pr=0 pw=0 time=61164 us cost=299 size=9218608 card=86968)

92 92 92 INDEX FULL SCAN I_USER2 (cr=1 pr=0 pw=0 time=139 us cost=1 size=2024 card=92)(object id 47)

86997 86997 86997 TABLE ACCESS FULL OBJ$ (cr=1077 pr=0 pw=0 time=30456 us cost=298 size=7305312 card=86968)

4006 4006 4006 TABLE ACCESS BY INDEX ROWID IND$ (cr=886 pr=0 pw=0 time=12785 us cost=2 size=8 card=1)

5074 5074 5074 INDEX UNIQUE SCAN I_IND1 (cr=104 pr=0 pw=0 time=2903 us cost=1 size=0 card=1)(object id 41)

2818 2818 2818 HASH JOIN (cr=3836 pr=0 pw=0 time=122382 us cost=2 size=24 card=1)

2889 2889 2889 INDEX RANGE SCAN I_OBJAUTH1 (cr=3836 pr=0 pw=0 time=8633 us cost=2 size=11 card=1)(object id 62)

2940 2940 2940 FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=6560 us cost=0 size=1300 card=100)

1 1 1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=31 us cost=0 size=26 card=1)

772 772 772 HASH JOIN (cr=1728 pr=0 pw=0 time=61347 us cost=2 size=24 card=1)

1321 1321 1321 INDEX RANGE SCAN I_OBJAUTH1 (cr=1728 pr=0 pw=0 time=3160 us cost=2 size=11 card=1)(object id 62)

811 811 811 FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=1901 us cost=0 size=1300 card=100)

1 1 1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=39 us cost=0 size=26 card=1)

1 1 1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=23 us cost=0 size=26 card=1)

67 67 67 NESTED LOOPS (cr=1340 pr=0 pw=0 time=3245 us cost=2 size=24 card=1)

93 93 93 INDEX RANGE SCAN I_OBJAUTH1 (cr=1340 pr=0 pw=0 time=2464 us cost=2 size=11 card=1)(object id 62)

67 67 67 FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=196 us cost=0 size=13 card=1)

0 0 0 NESTED LOOPS (cr=3415 pr=0 pw=0 time=13433 us cost=8 size=78 card=1)

0 0 0 NESTED LOOPS (cr=3415 pr=0 pw=0 time=13167 us cost=8 size=78 card=3)

0 0 0 NESTED LOOPS (cr=3415 pr=0 pw=0 time=12951 us cost=5 size=68 card=1)

1312 1312 1312 NESTED LOOPS (cr=2454 pr=0 pw=0 time=10128 us cost=4 size=57 card=1)

1312 1312 1312 MERGE JOIN CARTESIAN (cr=1796 pr=0 pw=0 time=8217 us cost=3 size=53 card=1)

656 656 656 INDEX RANGE SCAN I_OBJ5 (cr=1796 pr=0 pw=0 time=5156 us cost=3 size=40 card=1)(object id 40)

1312 1312 1312 BUFFER SORT (cr=0 pr=0 pw=0 time=2898 us cost=0 size=1300 card=100)

1312 1312 1312 FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=1467 us cost=0 size=1300 card=100)

1312 1312 1312 INDEX RANGE SCAN I_USER2 (cr=658 pr=0 pw=0 time=1405 us cost=1 size=4 card=1)(object id 47)

0 0 0 INDEX RANGE SCAN I_OBJAUTH1 (cr=961 pr=0 pw=0 time=1381 us cost=1 size=11 card=1)(object id 62)

0 0 0 INDEX RANGE SCAN I_DEPENDENCY1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=3)(object id 106)

0 0 0 TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=0 pr=0 pw=0 time=0 us cost=3 size=10 card=1)

1 1 1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=45 us cost=0 size=26 card=1)

0 0 0 NESTED LOOPS (cr=1996 pr=0 pw=0 time=4227 us cost=3 size=76 card=2)

28 28 28 NESTED LOOPS (cr=1996 pr=0 pw=0 time=3881 us cost=3 size=25 card=1)

609 609 609 TABLE ACCESS BY INDEX ROWID TRIGGER$ (cr=1257 pr=0 pw=0 time=2694 us cost=2 size=14 card=1)

623 623 623 INDEX UNIQUE SCAN I_TRIGGER2 (cr=634 pr=0 pw=0 time=656 us cost=1 size=0 card=1)(object id 163)

28 28 28 INDEX RANGE SCAN I_OBJAUTH1 (cr=739 pr=0 pw=0 time=843 us cost=1 size=11 card=1)(object id 62)

0 0 0 FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=82 us cost=0 size=26 card=2)

1 1 1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=26 us cost=0 size=26 card=1)

0 0 0 NESTED LOOPS (cr=734 pr=0 pw=0 time=3122 us cost=8 size=78 card=1)

0 0 0 NESTED LOOPS (cr=734 pr=0 pw=0 time=3075 us cost=8 size=78 card=3)

0 0 0 NESTED LOOPS (cr=734 pr=0 pw=0 time=3018 us cost=5 size=68 card=1)

278 278 278 NESTED LOOPS (cr=509 pr=0 pw=0 time=2350 us cost=4 size=57 card=1)

278 278 278 MERGE JOIN CARTESIAN (cr=378 pr=0 pw=0 time=1939 us cost=3 size=53 card=1)

139 139 139 INDEX RANGE SCAN I_OBJ5 (cr=378 pr=0 pw=0 time=1222 us cost=3 size=40 card=1)(object id 40)

278 278 278 BUFFER SORT (cr=0 pr=0 pw=0 time=652 us cost=0 size=1300 card=100)

258 258 258 FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=301 us cost=0 size=1300 card=100)

278 278 278 INDEX RANGE SCAN I_USER2 (cr=131 pr=0 pw=0 time=304 us cost=1 size=4 card=1)(object id 47)

0 0 0 INDEX RANGE SCAN I_OBJAUTH1 (cr=225 pr=0 pw=0 time=357 us cost=1 size=11 card=1)(object id 62)

0 0 0 INDEX RANGE SCAN I_DEPENDENCY1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=3)(object id 106)

0 0 0 TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=0 pr=0 pw=0 time=0 us cost=3 size=10 card=1)

1 1 1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=30 us cost=0 size=26 card=1)

1 1 1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=45 us cost=0 size=26 card=1)

1 1 1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=19 us cost=0 size=26 card=1)

0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=2 size=68 card=2)

0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=2 size=21 card=1)

0 0 0 TABLE ACCESS BY INDEX ROWID TABPART$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=10 card=1)

0 0 0 INDEX UNIQUE SCAN I_TABPART_OBJ$ (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 595)

0 0 0 INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=11 card=1)(object id 62)

0 0 0 FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=2)

0 0 0 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)

1 1 1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=55 us cost=0 size=26 card=1)

1 1 1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=35 us cost=0 size=26 card=1)

0 0 0 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)

1 1 1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=55 us cost=0 size=26 card=1)

0 0 0 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)

1 1 1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=20 us cost=0 size=26 card=1)

0 0 0 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)

54 54 54 VIEW (cr=0 pr=0 pw=0 time=103 us cost=2 size=13 card=1)

54 54 54 FAST DUAL (cr=0 pr=0 pw=0 time=28 us cost=2 size=0 card=1)

0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=2 size=21 card=1)

0 0 0 INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=8 card=1)(object id 62)

0 0 0 FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)

0 0 0 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)

0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=2 size=21 card=1)

0 0 0 INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=8 card=1)(object id 62)

0 0 0 FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)

0 0 0 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)

0 0 0 SORT GROUP BY NOSORT (cr=0 pr=0 pw=0 time=0 us cost=6 size=35 card=1)

0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=6 size=105 card=3)

0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=3 size=69 card=3)

0 0 0 INDEX UNIQUE SCAN I_OLAP_CUBES$ (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)(object id 1019)

0 0 0 TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=0 pr=0 pw=0 time=0 us cost=3 size=30 card=3)

0 0 0 INDEX RANGE SCAN I_DEPENDENCY1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=3)(object id 106)

0 0 0 INDEX RANGE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=12 card=1)(object id 36)

0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=2 size=29 card=1)

0 0 0 INDEX SKIP SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=20 card=1)(object id 47)

0 0 0 INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us cost=1 size=9 card=1)(object id 39)

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

---------------------------------------- Waited ---------- ---

SQL*Net message to client 3 0.00 0.00

SQL*Net message from client 3 0.00 0.00

SQL*Net break/reset to client 2 0.00 0.00

*******************************************************************

注意：使用DBMS_MONITOR時，請確保在完成操作時禁用跟蹤；否則，將會跟蹤滿足指定條件的每個會話。

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

使用dbms_monitor包這對某個使用者的跟蹤：

exec dbms_monitor.session_trace_enable(156,3588,TRUE,FALSE);

【等同於exec dbms_monitor.session_trace_disable(156,3588);】

第一個引數:SID

第二個引數：SERIAL＃

第三個引數：用於等待(預設為TRUE)，

第四個引數：用於繫結變數(預設為FALSE)。

【最佳化】DBMS_MONITOR包之生成跟蹤檔案

相關文章