ITPub博客

首页 > Linux操作系统 > Linux操作系统 > (ZT)Introduction:(简介)

(ZT)Introduction:(简介)

原创 Linux操作系统 作者:zergduan 时间:2008-11-26 01:50:02 0 删除 编辑

链接:

Introduction:(简介)

有四种类型的Events:

                Immediate dumps

               Conditional dumps

               Trace dumps

               Events that change database behaviour

每一个事件都有一个号跟Oracle的错误信息是一样的.10046ORA-10046

每一个事件都有一个Level,可以是以下:

              范围110

              位标 0x01 0x02 0x04 0x08 0x10

              标识 0=off,1=on

              ID 对象ID(object id),内存地址(memory address)

要注意的是,Events在每一个版本之间都有所改变.有一些存在的事件可能存在争议性或者已经不可用了,往往这些事件号会由新的事件所替代掉.也要注意在当前的版本中message file不一定可以反映出Events.

很多Events都会影响数据库的行为,一些测试Events极有可能导致数据库DOWN.所以,在没有Oracle Support的前提下,最好不要在PRO系统上做Events操作.DEV系统上如果要做Events最好先做个数据库的全备份.

 

Enabling Events(Enable事件)

Events可以在Instance一级Enabled,主要是在INIT.ORA文件中做操作:

        event='event trace name context forever, level level';

(红色部分:event指事件号.level指定事件的级别)

  一次可以Enable多个事件,可以用以下两种方式:

1.  用一个冒号隔开

     event = "10248 trace name context forever, level 10:10249 trace name context forever, level 10"

2.  两个Events分开写

    event="10248 trace name context forever, level 10"

    event="10249 trace name context forever, level 10"#一些版本的Oracleevent要一样的大小写

Events也可以在Instance一级用ALTER SYSTEM命令来Enable:

      ALTER SYSTEM SET EVENTS 'event trace name context forever, level level';

 在Instance一级用以下Disable

      ALTER SYSTEM SET EVENTS 'event trace name context off';

  Events也可以在Session一级用ALTER SESSION命令来Enable:

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

  Session一级用以下命令Disable:

ALTER SESSION SET EVENTS 'event trace name context off';

 Events在其他的SessionORADEBUGEnable:

 在一个Process中实现Enable:

      ORADEBUG EVENT event TRACE NAME CONTEXT FOREVER, LEVEL level

 在某个进程中Enable:

      ORADEBUG SETORAPID 8(PID进程号)

      ORADEBUG EVENT event TRACE NAME CONTEXT FOREVER, LEVEL level

 以下命令Disable:

  ORADEBUG EVENT event TRACE NAME CONTEXT OFF

Session中实现Enable:

  ORADEBUG SESSION_EVENT event TRACE NAME CONTEXT FOREVER, LEVEL level

Session中实现Disable:

  ORADEBUG SESSION_EVENT event TRACE NAME CONTEXT OFF

Events也可以用DBMS_SYSTEM.SETEV包来实现EnableDisable

    (在做之前要先从V$session视图中获得SIDSerial#)

用以下方式:EXECUTE DBMS_SYSTEM.SET_EV(SID,Serial#,event,level, '')

EXECUTE dbms_system.set_ev (9,29,10046,8,'');

  Disable则将level改为0,: EXECUTE dbms_system.set_ev (9,29,10046,0,'');

Listing All Events:(列出所有可用的Events)

大部分的Events number的范围都在1000010999.可以用以下命令Dump出所有的信息

SET SERVEROUTPUT ON

DECLARE

   err_msg VARCHAR2(120);

BEGIN

   dbms_output.enable (1000000);

   FOR err_num IN 10000..10999

   LOOP

    err_msg := SQLERRM (-err_num);

    IF err_msg NOT LIKE '%Message '||err_num||' not found%' THEN

      dbms_output.put_line (err_msg);

    END IF;

  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, level 10';

Event 10128 - Dump Partition Pruning Information--Dump分区表信息

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

  Level参考

Level

Action

0x0001

Dump pruning descriptor for each partitioned object

0x0002

Dump partition iterators

0x0004

Dump optimizer decisions about partition-wise joins

0x0008

Dump ROWID range scan pruning information

9.0.1或者后面的版本,level 2后还需要建立如下的表:

CREATE TABLE kkpap_pruning
   (
   partition_count NUMBER,
   iterator VARCHAR2(32),
   partition_level VARCHAR2(32),
   order_pt VARCHAR2(12),
   call_time VARCHAR2(12),
   part# NUMBER,
   subp# NUMBER,
   abs# NUMBER
   );

Event 10200 - Dump Consistent Reads---Dump出一致读的信息
   ALTER SESSION SET EVENTS '10200 trace name context forever, level 1';
  Event 10201 - Dump Consistent Read Undo Application
   ALTER SESSION SET EVENTS '10201 trace name context forever, level 1';
  Event 10220 - Dump Changes to Undo Header--Dump出Undo头信息的改变
   ALTER SESSION SET EVENTS '10220 trace name context forever, level 1';
  Event 10221 - Dump Undo Changes--Dump Undo的改变
   ALTER SESSION SET EVENTS '10221 trace name context forever, level 7';
  Event 10224 - Dump Index Block Splits / Deletes--Dump索引块的Split和Delete信息
   ALTER SESSION SET EVENTS '10224 trace name context forever, level 1';
  Event 10225 - Dump Changes to Dictionary Managed Extents---Dump出在Row Cache中,字典管理的Extents的改变
   ALTER SESSION SET EVENTS '10225 trace name context forever, level 1';
  Event 10231--设置在做全表扫描时跳过损坏的数据块(在做EXP时,如果有坏块,
设置这个可以使EXP导出时跳过坏块,使得部分数据能可以使用)
   ALTER SYSTEM SET EVENTS '10231 trace name context forever,level 10';
  Event 10241 - Dump Remote SQL Execution--Dump远程SQL语句的执行信息
   ALTER SESSION SET EVENTS '10241 trace name context forever, level 1';
  Event 10246 - Trace PMON Process---只能在init.ora中做,不能用ALTER SYSTEM做
   event = "10246 trace name context forever, level 1"
  Event 10248 - Trace Dispatcher Processes---在init.ora中做(9iDump到udump目录中)
   event = "10248 trace name context forever, level 10"
  Event 10249 - Trace Shared Server (MTS) Processes---在init.ora中做(9iDump到udump目录中)
   event = "10249 trace name context forever, level 10"
  Event 10270 - Debug Shared Cursors
  event = "10270 trace name context forever, level 10"
  Event 10299 - Debug Prefetching---(参考http://www.juliandyke.com/)
   event = "10299 trace name context forever, level 1"
  Event 10357 - Debug Direct Path---(参考http://www.juliandyke.com/)
   ALTER SESSION SET EVENTS '10357 trace name context forever, level 1';
  Event 10390 - Dump Parallel Execution Slave Statistics--(参考http://www.juliandyke.com/)
   ALTER SESSION SET EVENTS '10390 trace name context forever, level level';
  Event 10391-Dump Parallel Execution Granule Allocation---
   (参考http://www.juliandyke.com/)
   ALTER SESSION SET EVENTS '10391 trace name context forever, level level';
  Event 10393 - Dump Parallel Execution Statistics--(参考http://www.juliandyke.com/)
   ALTER SESSION SET EVENTS '10393 trace name context forever, level 1';
  Event 10500 - Trace SMON Process--在init.ora中做
   event = "10500 trace name context forever, level 1"
  Event 10608 - Trace Bitmap Index Creation--跟踪二位图索引
   ALTER SESSION SET EVENTS '10608 trace name context forever, level 10';
  Event 10704 - Trace Enqueues--跟踪队列
   ALTER SESSION SET EVENTS '10704 trace name context forever, level 1';
  Event 10706 - Trace Global Enqueue Manipulation-(参考http://www.juliandyke.com/)
   ALTER SESSION SET EVENTS '10706 trace name context forever, level 1';
  Event 10708 - Trace RAC Buffer Cache--跟踪RAC的Buffer Cache
   ALTER SESSION SET EVENTS '10708 trace name context forever, level 10';
  Event 10710 - Trace Bitmap Index Access--(参考http://www.juliandyke.com/)
   ALTER SESSION SET EVENTS '10710 trace name context forever, level 1';
  Event 10711 - Trace Bitmap Index Merge Operation--
   ALTER SESSION SET EVENTS '10711 trace name context forever, level 1';
  Event 10712 - Trace Bitmap Index OR Operation--
   ALTER SESSION SET EVENTS '10712 trace name context forever, level 1';
  Event 10713 - Trace Bitmap Index AND Operation--
   ALTER SESSION SET EVENTS '10713 trace name context forever, level 1';
  Event 10714 - Trace Bitmap Index MINUS Operation--
   ALTER SESSION SET EVENTS '10714 trace name context forever, level 1';
  Event 10715 - Trace Bitmap Index Conversion to ROWIDs Operation--
   ALTER SESSION SET EVENTS '10715 trace name context forever, level 1';
  Event 10716 - Trace Bitmap Index Compress/Decompress--
   ALTER SESSION SET EVENTS '10716 trace name context forever, level 1';
  Event 10717 - Trace Bitmap Index Compaction--
   ALTER SESSION SET EVENTS '10717 trace name context forever, level 1';
  Event 10719 - Trace Bitmap Index DML--
   ALTER SESSION SET EVENTS '10719 trace name context forever, level 1';
  Event 10730 - Trace Fine Grained Access Predicates--
   ALTER SESSION SET EVENTS '10730 trace name context forever, level 1';
  Event 10731 - Trace CURSOR Statements--
   ALTER SESSION SET EVENTS '10731 trace name context forever, level level';
  Levels are:
  Level  Action
  1   Print parent query and subquery
  2    Print subquery only
  Event 10928 - Trace PL/SQL Execution--
   ALTER SESSION SET EVENTS '10928 trace name context forever, level 1';
  Event 10938 - Dump PL/SQL Execution Statistics--
   ALTER SESSION SET EVENTS '10938 trace name context forever, level 1';
  一些其他的Events:
  ALTER SESSION SET EVENTS 'immediate trace name flush_cache';--Flush the Buffer cache

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/386081/viewspace-503313/,如需转载,请注明出处,否则将追究法律责任。

下一篇: ORADEBUG
请登录后发表评论 登录
全部评论

注册时间:2008-03-12

  • 博文量
    77
  • 访问量
    751233