oracle查询dumpfile,Oracle Dump File 大全

[Block Dump][Disk + Buffer]

alter system checkpoint;

alter system dump datafile block ;

alter system dump datafile block min block max ;

[Buffer Only]

alter session set events 'immediate set_tsn_p1(5)'; -- TS#+1

alter session set events 'immediate buffer(16777741)'; -- RDBA(decimal)

[Flush Cache]

alter session set events 'immediate trace name flush_cache';

[Heap Dump]alter session set events 'immediate heapdump (n)';

Level 1: Top PGA

Level 2: Top SGA (Shared Pool)

Level 4: Top UGA

Level 8: Current call (CGA)

Level 16: User call (CGA)

Level 32: Large pool

Level 64: Streams pool

Level 128: Java pool

Level 1025: PGA with contents

Level 2050: SGA with contents

Level 4100: UGA with contents

Level 8200: Current call with contents

Level 16400: User call with contents

Level 32800: Large pool with contents

Level 65600: Streams pool with contents

Level 131200: Java pool with contents

[Sub Heap Dump]

alter session set events 'immediate heapdump_addr (address=n [, level = 2])';

n はサブ・ヒープ ds のアドレスとなります。

1. ds フィールドの値を見ます。

2. 値を 10 進数( decimal )に直します。

3. HEAPDUMP_ADDR イベントの address パラメータに、その値を指定します。

4. チャンクの内容をダンプする (with contents) には、 2 以上のレベルを指定します。

[Library Cache Dump]alter session set events 'immediate library_cache (n)';

Level 1: ライブラリ･キャッシュ統計

Level 2: ハッシュ･テーブルのサマリ

Level 4: 各 LCO の Partial 情報 ( オブジェクト・ハンドル )

Level 8: 各 LCO の More 情報 ( オブジェクト・ハンドル + ヒープ 0)

Level 16: 各 LCO の Most 情報 ( オブジェクト・ハンドル +ヒープ 0+ 子カーソル)

Level 32: ヒープ ･ダンプを 追加 ( レベル 8,16 との組み合わせで使用 )

Level 64: 子カーソルの情報を追加 ( レベル 4 または 8 との組み合わせで使用 )

Level 2048: ダンプ時に LCO の Mutex を獲得しない

Level 8192: 各 LCO の Least 情報 ( オブジェクト・ハンドルの一部 )

[System State Dump]alter session set events 'immediate systemstate (n)';

Level 1 Very basic process information only

Level 10 Most common level - includes state object trees for all processes.

Includes "interesting" RAC resources in the trace (in RAC only).

Level 11 In RAC level 11 includes a dump of any resources not on the freelist

just after the "BUSY GLOBAL CACHE ELEMENTS" section.

Level + 256 Adding 256 to the level will try to dump short stack info for each

process when the enhancement described in Note:3797523.8 is present.

*** IMPORTANT ***

Short stacks are produced reasonably quickly on Solaris and Linux but

on other platforms including short stacks in dumps can take a very

long time. Hence it is advisable to test if the overhead is acceptable

before using this option.

[All Node System State Dump on RAC]

alter session set events 'immediate systemstate_global (11)';

[Hang Analyze]alter session set events 'immediate hanganalyze (n)';

Level 1 Very minimal output

Level 2 Minimal output

Level 3 Dump only processes thought to be in a hang <<< Most common level

Level 4 Dump leaf nodes in wait chains

Level 5 Dump all processes involved in wait chains (can be a lot)

Level 6 Dump errorstacks of processes involved in wait chains (can be high overhead)

Level 10 Dump all processes (not a good idea)

[Event Dump]alter session set events 'immediate eventdump(system)';

alter session set events 'immediate eventdump(process)';

alter session set events 'immediate eventdump(session)';

oradebug setmypid

oradebug eventdump session // セションレベルで設定されたすべてのイベントが出力されます。

[Error Stack Dump]level 1 errorstack とcall stack

level 2 level 1 の内容に加え、 process state

level 3 level 2 の内容に加え、コンテキスト領域 (カーソルダンプ )

[argument]

alter system set events '600 {errarg: "723" } heapdump(536870925)';

alter system set events '600 {errarg:arg1="kglLock-bad-byd"} library_cache(10)';

alter system set events '600 {errarg:arg1="kslgetl:1"} heapdump(2)';

alter system set events '600 {errarg:arg1="kksfbc-wrong-kkscsflgs"| "kglLock-bad-byd" } library_cache(11)';

[call stack]

alter system set events '7445 {callstack:fprefix=kgl|fprefix=kgh} heapdump(2)';

alter system set events '4031 {callstack:fname=kmgsb_parse_io_pool_query} heapdump(2)';

[disable]

alter system set events '7445 off : 4031 off';

[SQL Trace]Level 1 通常の SQL_TRACE

Level 4 SQL_TRACE とバインド変数

Level 8 SQL_TRACE とwait events

Level 12 SQL_TRACE とバインド変数と wait events

[10046]

alter session set tracefile_identifier='10046';

alter session set timed_statistics = true;

alter session set statistics_level=all;

alter session set max_dump_file_size = unlimited;

alter session set events '10046 trace name context forever,level 12';

-- 対処のクエリあるいは処理を実行します --

exit;

カレントセッションを終了せず、以下のようにトレース設定を解除できます :

alter session set events '10046 trace name context off';

[process]

alter system set events 'sql_trace{process: orapid=20}';

alter system set events 'sql_trace{process: ospid=6137}';

alter system set events 'sql_trace{process: 23234}';

alter system set events 'sql_trace{process: pname=pmon}'

alter system set events 'sql_trace{process: pname=ora}';

alter system set events 'sql_trace{process: pname=m00}';

alter system set events 'sql_trace{process: pname=cjq}';

alter system set events 'sql_trace{process: pname=s00}';

alter system set events 'sql_trace{process: pname=dw | pname=dm} level=12';

[SQL ID]

alter session set events 'sql_trace[sql: g3yc1js3g2689]';

alter session set events 'sql_trace[sql: g3yc1js3g2689 | 7ujay4u33g337]'

[disable]

alter system set events 'sql_trace off';

alter system set events 'sql_trace {process : pname = dw | pname = dm} off';

[Optimizer][10053]

alter session set tracefile_identifier='10053';

alter session set timed_statistics = true;

alter session set statistics_level=all;

alter session set max_dump_file_size = unlimited;

alter session set events '10053 trace name context forever,level 1';

-- 対処のクエリあるいは処理を実行します --

exit;

カレントセッションを終了せず、以下のようにトレース設定を解除できます :

alter session set events '10053 trace name context off';

// 10053 は以下のトレースをすべて設定する場合相当する

alter session set events 'trace[RDBMS.SQL_OPTIMIZER]';

alter session set events 'trace[RDBMS.SQL_Transform]';

alter session set events 'trace[RDBMS.SQL_MVRW]';

alter session set events 'trace[RDBMS.SQL_VMerge]';

alter session set events 'trace[RDBMS.SQL_Virtual]';

alter session set events 'trace[RDBMS.SQL_APA]';

alter session set events 'trace[RDBMS.SQL_Costing]';

alter session set events 'trace[RDBMS.SQL_Parallel_Optimization]';

alter session set events 'trace[RDBMS.SQL_Plan_Management]';

[trace]

alter session set events 'trace[sql_optimizer.*] disk=highest, memory=low';

alter session set events 'trace[SQL_Transform]';

alter session set events 'trace[SQL_APA]';

alter session set events 'trace[SQL_Costing]';

alter session set events 'trace[SQL_PlanManagement]';

[Enqueue]alter session set events 'immediate trace name enqueues level 3';