ZT Oracle EVENT用法參考列表

asword發表於2009-02-01
Oracle
SUCH AS 10046[@more@]

事件說明例子
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 StatisticsDump排序的統計資訊ALTER SESSION SET EVENTS '10032 trace name context forever, level 10';
Event 10033 - Dump Sort Intermediate Run Statistics排序過程中，記憶體排序區和臨時表空間的互動情況ALTER SESSION SET EVENTS '10033 trace name context forever, level 10';
Event 10045 - Trace Free List Management OperationsFREELIST的操作ALTER SESSION SET EVENTS '10045 trace name context forever, level 1';
Event 10046 - Enable Statement Trace跟蹤SQL,有執行計劃,邦定變數和等待的統計資訊,level 12最詳細。ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
LEVEL定義如下：
1:SQL 語句，執行計劃和執行狀態
4:1的內容加上繫結變數資訊
8:1的資訊加上等待事件資訊
12:1＋4＋8
Event 10053 - Dump Optimizer Decisions在分析SQL語句時,Dump出最佳化器所做的選擇,級別level 1最詳細ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
LEVEL定義如下：
1：狀態和估算資訊
2：只顯示估算資訊
Event 10060 - Dump PredicatesDUMP SQL語句中的斷語資訊。需要在需要DUMP的使用者下建立以下表
CREATE TABLE kkoipt_table
(c1 INTEGER,
c2 VARCHAR2(80));
斷語資訊會寫入該表
CREATE TABLE kkoipt_table
(c1 INTEGER,
c2 VARCHAR2(80));
斷語資訊會寫入該表		ALTER SESSION SET EVENTS '10060 trace name context forever, level 1';
Event 10065 - Restrict Library Cache Dump Output for State Object Dumps限制物件狀態DUMP的時候LIBRARY CACHE資訊的詳細程度ALTER SESSION SET EVENTS '10065 trace name context forever, level level';
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 ChangesHWM的改變ALTER SESSION SET EVENTS '10081 trace name context forever, level 1';
Event 10104 - Dump Hash Join StatisticsHASH JOIN的統計資訊ALTER SESSION SET EVENTS '10104 trace name context forever, level 10';
Event 10128 - Dump Partition Pruning Information調整資訊ALTER SESSION SET EVENTS '10128 trace name context forever, level level';
Level取值：
1 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 ReadsDUMP一致讀的資訊ALTER SESSION SET EVENTS '10200 trace name context forever, level 1';
Event 10201 - Dump Consistent Read Undo ApplicationDUMP一致性讀涉及UNDO資訊的內容ALTER SESSION SET EVENTS '10201 trace name context forever, level 1';
Event 10220 - Dump Changes to Undo HeaderDump出Undo頭資訊的改變ALTER SESSION SET EVENTS '10220 trace name context forever, level 1';
Event 10221 - Dump Undo ChangesDump Undo的改變ALTER SESSION SET EVENTS '10221 trace name context forever, level 7';
Event 10224 - Dump Index Block Splits / Deletes索引塊的分裂和D刪除資訊ALTER SESSION SET EVENTS '10224 trace name context forever, level 1';
Event 10225 - Dump Changes to Dictionary Managed ExtentsDUMP欄位管理的擴充套件變化ALTER SESSION SET EVENTS '10225 trace name context forever, level 1';
Event 10231全表掃描時跳過壞塊，在有壞塊的情況下做資料拯救時很有用ALTER SYSTEM SET EVENTS '10231 trace name context forever,level 10';
Event 10241 - Dump Remote SQL Execution遠端SQL語句的執行資訊ALTER SESSION SET EVENTS '10241 trace name context forever, level 1';
Event 10246 - Trace PMON Process跟蹤PMON程式只能修改引數，不能用ALTER SYSTEM
event = "10246 trace name context forever, level 1"
Event 10248 - Trace Dispatcher Processes跟蹤DISPATCHER的工作情況 event = "10248 trace name context forever, level 10"
Event 10249 - Trace Shared (MTS) Processes-跟蹤共享伺服器的工作情況event = "10249 trace name context forever, level 10"
Event 10270 - Debug Shared Cursors跟蹤共享CURSORS的情況event = "10270 trace name context forever, level 10"
Event 10299 - Debug Prefetching跟蹤表資料塊和索引資料塊的PREFETCHINGevent = "10299 trace name context forever, level 1"
Event 10357 - Debug Direct Path　ALTER SESSION SET EVENTS '10357 trace name context forever, level 1';
Event 10390 - Dump Parallel Execution Slave Statistics跟蹤並行操作中的SLAVE的狀態ALTER SESSION SET EVENTS '10390 trace name context forever, level 1;
Event 10391-Dump Parallel Execution Granule Allocation跟蹤並行操作的粒度ALTER SESSION SET EVENTS '10391 trace name context forever, level 2';
Event 10393 - Dump Parallel Execution Statistics跟蹤並行操作的狀態（每個SLAVE單獨列出狀態）ALTER SESSION SET EVENTS '10393 trace name context forever, level 1';
Event 10500 - Trace SMON Process跟蹤SMON程式event = "10500 trace name context forever, level 1"
Event 10608 - Trace Bitmap Index Creation跟蹤BITMAP索引建立的詳細過程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跟蹤全域性鎖的使用情況ALTER SESSION SET EVENTS '10706 trace name context forever, level 1';
Event 10708 - Trace RAC Buffer Cache跟蹤RAC環境下的BUFFER CACHEALTER SESSION SET EVENTS '10708 trace name context forever, level 10';
Event 10710 - Trace Bitmap Index Access跟蹤點陣圖索引的訪問情況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跟蹤點陣圖索引minus操作ALTER SESSION SET EVENTS '10714 trace name context forever, level 1';
Event 10715 - Trace Bitmap Index Conversion to ROWIDs Operation跟蹤點陣圖索引轉換ROWID操作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跟蹤點陣圖索引列的DML操作（引起點陣圖索引改變的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跟蹤CURSOR的語句情況ALTER SESSION SET EVENTS '10731 trace name context forever, level level';
LEVEL定義
1 Print parent query and subquery
2 Print subquery only
Event 10928 - Trace PL/SQL Execution跟蹤PL/SQL執行情況ALTER SESSION SET EVENTS '10928 trace name context forever, level 1';
Event 10938 - Dump PL/SQL Execution Statistics跟蹤PL/SQL執行狀態。使用前需要執行rdbms/admin下的tracetab.sqlALTER SESSION SET EVENTS '10938 trace name context forever, level 1';
flush_cache重新整理BUFFER CACHEALTER SESSION SET EVENTS 'immediate trace name flush_cache';
DROP_SEGMENTS手工刪除臨時段。當這些臨時段無法自動清除的時候可以手工清除alter session set events 'immediate trace name DROP_SEGMENTS level ts#+1';
ts#是指要刪除臨時段的表空間的ts#

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/82387/viewspace-1016542/，如需轉載，請註明出處，否則將追究法律責任。

相關文章