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包之生成跟踪文件