オラクルが提供する
待機イベントとは本?に?特です。どんなDBMSでも待機現象をこんなに詳細に報告してくれる製品はありません。
これはオラクルの性能問題を分析かつ解決すべき私たちには大きいプレゼントである同時?みであります。まるで初?の運?者にポルシェを?えるのと同じだと言えないでしょうか。ポルシェのような車を走らせようとしたらまず丈夫な運?わざが必要です。
待機イベント分析も同?です。正確な分析のためにはオラクルのア?キテクチャ?に?する相?な知識が必要です。これが待機イベント分析の技法がより?範?に使用されうことに邪魔となっています。
待機イベント分析で1つ?をつけなければならないのが大?イベントだけでは意味のある情報を引き出し難しいということです。?連のあるほかの情報が必要である場合がたくさんあります。ティパックではこれを
知能的な待機イベントの分析と呼びます。例えをあげてみればこのような追加的な情報が必要です。
- cache buffers chainsラッチで競合が?生する場合、おもにどんなブロックで?生しているかわかる？
- row cache objectsラッチで競合が?生する場合、おもにどんなディクショナリ?オブジェクトなのかわかる？
- db file sequential readのようなI/O待機いの場合、どんなセグメントのどのような種類のブロックで?生するかわかる？
- ラッチ、ロック（Enqueue）、library cache lock、library cache pin、row cache lock、Mutexなどの同期化客?で競合が?生する場合、ホルダ?情報および?連の客?の情報が得られる？
これから?回にわたって待機イベントをもっと知能的に分析するためにどんなデ?タを追加的に分析する必要があらのかいくつかの例を見るつもりです。
1. ホルダ?を突き止め！ オラクルでの競合は次の六つ程度の同期化客?により?生します。
- ラッチ
- ロック
- Library cache lock
- Library cache pin
- Row cache lock
- Mutex
個?の客?が何を意味しているのかすでにわかっている方?も多いはずなので、詳細な?明は足らないと思います。
同期化客?の種類別に提供されるビュ?が違いですから、ホルダ?を突き止めるときも違うビュ?を?索しなければなりません。幸いに最近バ?ジョンのオラクルでは
V$SESSIONビィ?のBLOCKING_SESSION、BLOCKING_INSTANCEなどの列から大部分の?況でホルダ?情報が提供されます。大?有?な情報だと言えます。しかし、待機イベントによっては?純にホルダ?を知るだけでは不足である場合もあります。正確にどんなオブジェクトで、どんなモ?ドで?生しているのかなどの情報が必要である場合もあります。
これから公開する情報はたぶん（！）ホルダ?を突き止める方法を今までのどんな文書より一番?範?で完璧にまとめていることであるはずです。
1.1 ラッチのホルダ? ラッチホルダ?は
V$LATCHHOLDERビュ?から突き止められます。
-- ラッチホルダ?
select h.pid, h.sid, h.laddr, h.name, h.gets
from v$latchholder h, v$session_wait s
where s.sid = &sid
and s.p1raw = h.laddr;
ラッチはとても短い時間に獲得されうため、上のような?純なクエリだけでは欲しい結果を得ることができない場合がたくさんあります。（もちろんオラクルのバグなどによって特定のラッチを長い間持っている場合もあります）
この時に使用できるのがプロファイル方法です。ティパックは
1)スナップショット、2)プロファイル2つの方法を使用しています。この中で、プロファイルとは短い時間に記?され、消えてしまう値を可能な限りよく取り出して、要約する方法を意味します。
V$LATCHHOLDERビュ?も次のようにプロファイリングできます。
-- ラッチホルダ?プロファイリング
TPACK@ukja1021> select /*+ ordered use_nl(x) */
2 x.sid, x.name, count(*)
3 from
4 (select /*+ no_merge */ level from dual connect by level <= 10000) t1,
5 (select /*+ no_merge */ h.pid, h.sid, h.laddr, h.name, h.gets
6 from v$latchholder h, v$session_wait s
7 where s.sid = &sid
8 and s.p1raw = h.laddr) x
9 group by x.sid, x.name
10 ;
old 7: where s.sid = &sid
new 7: where s.sid = 138
SID NAME COUNT(*)
---------- ------------------------------ ----------
134 shared pool 33
134 library cache 308
1.3 ロックのホルダ?ロックホルダ?はV$LOCKビュ?で十分です。
-- ロックホルダ?
select
h.sid, -- ホルダ?SID
h.type, -- ロックタイプ
h.id1, -- ID1
h.id2, -- ID2
h.lmode,
t.name,
t.id1_tag,
t.id2_tag,
t.description
from v$lock h, v$lock w, v$lock_type t
where w.sid = { waiter_sid }
and h.id1 = w.id1
and h.id2 = w.id2
and h.lmode > 0
and h.block > 0
and h.type = t.type
;
1.3 Library cache lockのホルダ?Library cache lockとはLCO(Library Cache Object)を保護するシステムロックです。競合が?生すればlibrary cache lock待機イベントを持ちます。Library cache lockのホルダ?はX$KGLLKビュ?を通じて突き止められます。
-- Library cache lock ホルダ?
select
(select sid from v$session where saddr = k.kgllkuse) as sid, -- ホルダ?SID
k.kglhdnsp, -- 客?の種類
k.kglnaobj, -- 客?名（SQL文章や表、プロシ?ジャ名など）
decode(k.kgllkmod, 3, '3(X)', 2, '2(S)', 1, '1(N)', k.kgllkmod) as lkmode
from x$kgllk k
where k.kgllkhdl = { v$session_wait.p1raw }
and k.kgllkmod > 0
;
1.4 Library cache pinのホルダ?Library cache pinとはカ?ソルやプロシ?ジャの?行を保護するシステムロックです。競合が?生すればlibrary cache pin待機イベントを待ちます。Library cache pinホルダ?はX$LGLPNビュ?を通じて突き止められます。
-- Library cache pin ホルダ?
select
(select sid from v$session where saddr = n.kglpnuse) as sid,
o.kglnaobj,
o.kglhdnsp,
decode(n.kglpnmod, 3, '3(X)', 2, '2(S)', 1, '1(N)', n.kglpnmod) as lkmode
from x$kglpn n, x$kglob o
where n.kglpnhdl = { v$session_wait.p1raw }
and n.kglpnmod > 0
and o.kglhdadr = n.kglpnhdl
;
1.5 Row cache lockのホルダ?Row cache lockとはディクショナリ?オブジェクトを保護するロックです。競合が?生すればrow cache lock待機イベントを待ちます。Row cache lockのホルダ?はV$ROWCACHE_PARENTビュ?から突き止められます。
-- Row cache lock ホルダ?
select
(select sid from v$session where saddr = h.saddr) as sid, -- ホルダ?SID
h.cache_name, -- ディクショナリ?オブジェクトの種類
h.lock_mode,
h.inst_lock_type
from v$rowcache_parent h, v$rowcache_parent w, v$session s
where h.address = w.address
and w.saddr = s.saddr
and s.sid = { waiter_sid }
and h.lock_mode > 0
;
1.6 Mutexのホルダ?Mutexのホルダ?は
V$MUTEX_SLEEP_HISTORYビュ?を通じて突き止められます。
-- Mutexのホルダ?
select * from (
select
blocking_session as sid, -- ホルダ?SID
(select kglnaobj from x$kglob
where kglnahsh = mutex_identifier
and rownum = 1) as obj_name, -- オブジェクト命(11gで追加)
mutex_type, -- Mutexのタイプ
location, -- Mutexを獲得した位置、すなわち何のためにMutexを獲得しようとしているのか？
sleeps,
gets,
to_char(sleep_timestamp,'yyyy/mm/dd hh24:mi:ss') as sleep_timestamp
from v$mutex_sleep_history
where requesting_session = session_id
order by sleep_timestamp desc
) where rownum <= 1 ;
11gからは
MUTEX_IDENTIFIERという有?なコラムが追加されMutex競合の分析がもっと易くなりました。
上の情報からわかるようにホルダ?の?索も大事ものの、もっと具?的にどんな情報で、どんあ客?で問題が?生しているのかを把握することも重要です。このような理由から、文書化されていないV$ビュ?やX$ビュ?を?索するしかありません。
ティパックでは
Session Detail Reportを通じて上の情報を取り出すことができます。ほぼ同じクエリを使用します。
Library cache pin競合の例で?明してみます。
-- とても長いSQL文章を作ります。正確にいえばハ?ドパ?スの時間が長いSQL文章です。
-- http://sites.google.com/site/ukja/sql-scripts-1/j-m/make_long
TPACK@ukja1021> @make_long
select count(*) from TPACK_REPORT_PARAMS, TPACK_REPORT_CONDITIONS,
TPACK_REPORT_JOB_HIST, TPACK_REPORT_SESSION_TEMP, TPACK_FUNCTION_NAMES,
...
-- セッション＃１で上のクエリを行います。（私のテスト環境で２分５０秒くらいかかります）
TPACK@ukja1021> exec dbms_application_info.set_client_info('session1');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.39
TPACK@ukja1021> @long_parse
....
-- セッション＃１と同時にセッション＃２で同じクエリを行います。
TPACK@ukja1021> exec dbms_application_info.set_client_info('session2');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04
TPACK@ukja1021> @long_parse
...
セッション＃２はセッション＃１がハ?ドパ?スを行なう間、待機?態になります。セッション＃２に?して
Session Detail Reportを?集して待機イベントを詳細に分析します。
-- セッション＃２のSID
TPACK@ukja1021> col sid new_value sid
TPACK@ukja1021> select sid from v$session where client_info = 'session2';
SID
----------
138
1 row selected.
Elapsed: 00:00:00.14
-- セッション＃２のSession Detail Report
TPACK@ukja1021> col name format a30
TPACK@ukja1021> col value format a45
TPACK@ukja1021> set pages 200
TPACK@ukja1021> set long 10000000
TPACK@ukja1021> select * from table(tpack.session_detail(&sid, 'wait_detail'));
old 1: select * from table(tpack.session_detail(&sid, 'wait_detail'))
new 1: select * from table(tpack.session_detail( 138, 'wait_detail'))
NAME VALUE
------------------------------ ---------------------------------------------
SID 138
Serial# 711
SPID 3724
Program sqlplus.exe
Process 5364:5208
Module SQL*Plus
SQL ID
Child No
SQL Text
Status ACTIVE
Blocking Instance 1
Blocking Session 134
Event library cache pin
Seq# 4137
P1(P1raw) 970230240(39D489E0)
P2(P2raw) 970230240(36A9AB34)
P3(P3raw) 970230240(000000C8)
Seconds in wait 10
State WAITING
Wait Event library cache pin
Holder SID 134
Namespace CURSOR
Object select count(*) from TPACK_SGA_STAT, TPACK_SG
A_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK
_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TP
ACK_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT,
TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_ST
AT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA
_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK_
SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK_S
GA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPAC
K_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, T
PACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_D
UMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_
HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP,
TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_
DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK
_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPA
CK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUM
P, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HE
AP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TP
ACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DU
MP, TPACK_HEAP_DUMP_SUM, TPACK_HEAP_DUMP_SUM,
TPACK_HEAP_DUMP_SUM, TPACK_HEAP_DUMP_SUM, TP
AC
Holding Mode 3(X)
NamespaceとObject情報からlibrary cache pin待機に?するより正確な分析が可能です。Oracle 11gではlibrary cache pinではなくてMutexに?する競合が?生します。
TPACK@ukja1106> select * from table(tpack.session_detail(&sid, 'wait_detail'));
old 1: select * from table(tpack.session_detail(&sid, 'wait_detail'))
new 1: select * from table(tpack.session_detail( 127, 'wait_detail'))
NAME VALUE
------------------------------ ---------------------------------------------
SID 127
Serial# 1642
SPID 2656
Program sqlplus.exe
Process 5364:5208
Module SQL*Plus
SQL ID
Child No
SQL Text
Status ACTIVE
Blocking Instance 1
Blocking Session 139
SQL Exec Start
Event cursor: pin S wait on X
Seq# 631
P1(P1raw) 3859422310(00000000E60A1C66)
P2(P2raw) 3859422310(00000000008B0000)
P3(P3raw) 3859422310(0000000000050256)
Seconds in wait 0
State WAITING
Wait Event cursor: pin S wait on X
Holder SID 139
Mutex Type Cursor Pin
Location
Target Object select count(*) from TPACK_SGA_STAT, TPACK_SG
A_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK
_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TP
ACK_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT,
TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_ST
AT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA
_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK_
SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPACK_S
GA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, TPAC
K_SGA_STAT, TPACK_SGA_STAT, TPACK_SGA_STAT, T
PACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_D
UMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_
HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP,
TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_
DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK
_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPA
CK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUM
P, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HE
AP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DUMP, TP
ACK_HEAP_DUMP, TPACK_HEAP_DUMP, TPACK_HEAP_DU
MP, TPACK_HEAP_DUMP_SUM, TPACK_HEAP_DUMP_SUM,
TPACK_HEAP_DUMP_SUM, TPACK_HEAP_DUMP_SUM, TP
AC
Last Sleep Time 2010/06/29 16:03:51
Gets 1
Sleeps 611
重要なのは
待機現象が?生するとき適切なビュ?から適切なデ?タを?索することができるかということです。ティパックはその中核心的なデ?タを自動に?集してくれるだけです。
以前のポスト
- 「ティパック」性能問題をトラブルシュ?ティングする２つのフレ?ム
- 「ティパック」oradebug
Posts
