Oracle Events(個人蔘考資料)

來源:互聯網
上載者:User
oracle|參考
Oracle Internal Events:

Introduction:(簡介)

有四種類型的Events:

                Immediate dumps

               Conditional dumps

               Trace dumps

               Events that change database behaviour

每一個事件都有一個號跟Oracle的錯誤資訊是一樣的.如10046和ORA-10046

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

              範圍1到10

              位標 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"#一些版本的Oracle,event要一樣的大小寫

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在其他的Session用ORADEBUG來Enable:

 在一個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包來實現Enable和Disable

    (在做之前要先從V$session視圖中獲得SID和Serial#)

用以下方式: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的範圍都在10000到10999.可以用以下命令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

 

資料來源http://www.juliandyke.com/

以上為個人在做DBA時的一些參考資料.

 

 


相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。