END LOOP;

END;

/

在UNIX系统下message文件在底下目录$ORACLE_HOME/rdbms/mesg/oraus.msg

在NT系统下message文件在底下目录$ORACLE_HOME/rdbms/mesg/oraus.msg

Listing Enabled Events(列出Enabled Events)

  用以下命令列出在当前Session已经Enabled Events:

           SET SERVEROUTPUT ON

           DECLARE

               l_level NUMBER;

           BEGIN

               FOR l_event IN 10000..10999

               LOOP

                         dbms_system.read_ev (l_event,l_level);

                         IF l_level > 0 THEN

                          dbms_output.put_line (''''Event ''''||TO_CHAR (l_event)||

                               '''' is set at level ''''||TO_CHAR (l_level));

                         END IF;

                END LOOP;

              END;

             /

常用Events的参考:(红色的为最常用的而且对DBA比较有用的Events)

Event 10013 - Monitor Transaction Recovery------在Startup时跟踪事务恢复

           ALTER SESSION SET EVENTS ''''10013 trace name context forever, level 1'''';

Event 10015 - Dump Undo Segment Headers----在事务恢复后做Dump回退段头信息

           ALTER SESSION SET EVENTS ''''10015 trace name context forever, level 1'''';

Event 10032 - Dump Sort Statistics----Dump排序的统计信息,level 10是最详细的

            ALTER SESSION SET EVENTS ''''10032 trace name context forever, level 10'''';

Event 10033 - Dump Sort Intermediate Run Statistics—level 10(不明白)

              ALTER SESSION SET EVENTS ''''10033 trace name context forever, level 10'''';

Event 10045 - Trace Free List Management Operations—跟踪Freelist

             ALTER SESSION SET EVENTS ''''10045 trace name context forever, level 1'''';

Event 10046 - Enable SQL Statement Trace---跟踪SQL,有执行计划,邦定变量和等待的统计信息,level 12最详细

           ALTER SESSION SET EVENTS ''''10046 trace name context forever, level 12'''';

级别level参考如下图:

Level

Action

1

Print SQL statements, execution plans and execution statistics

4

As level 1 plus bind variables

8

As level 1 plus wait statistics

12

As level 1 plus bind variables and wait statistics

Event 10053 - Dump Optimizer Decisions---在分析SQL语句时,Dump出优化器所做的选择,级别level 1最详细

            ALTER SESSION SET EVENTS ''''10053 trace name context forever, level 1'''';

参考level:

Level

Action

1

Print statistics and computations

2

Print computations only

Event 10060 - Dump Predicates---(参考http://www.juliandyke.com/)

Event 10065 - Restrict Library Cache Dump Output for State Object Dumps-(参考http://www.juliandyke.com/)

Event 10079 - Dump SQL*Net Statistics---Dump SQL*NeT的统计信息

            ALTER SESSION SET EVENTS ''''10079 trace name context forever, level 2'''';

Event 10081 - Trace High Water Mark Changes—跟踪HWM的改变

            ALTER SESSION SET EVENTS ''''10081 trace name context forever, level 1'''';

Event 10104 - Dump Hash Join Statistics—Dump HASH JOIN的统计信息,用level 10

             ALTER SESSION SET EVENTS ''''10104 trace name context forever,

上一页  [1] [2] [3] [4]  下一页