共有プール領域に関する検証 その３
＜共有プールに関する検証 その3＞ ペンネーム ダーリン
– V$LATCH と 全件検索 -－
前回は、V$SQLAREA表から負荷の高いSQLを見つける方法を紹介した。
今回は、V$SQLAREAからすこし脱線して、V$LATCH表をみてみよう。
さて、今回のテーマは、前回お話した「全件検索が問題となる一例」として
“ラッチ”を見てみよう。
メモリ上に存在するデータに対してアクセスする際に、レスポンスを左右する
要因のひとつに”ラッチ”という処理があることはご存知のとおりである。
ここでいうラッチは、”CACHE BUFFER CHAINS”で、これに関する値はV$LATCH表
で見ることが出来る。
“CACHE BUFFER CHAIN” ラッチは、SQL文が取得しようとするデータがデータ
ベースバッファ上に存在しているかどうかを、サーチする際に取得される一種
の”ロック”であり、このラッチ（ロック）の回数が上昇するほど、データベー
スバッファから大量のデータが取得されていることが予想される。ここで着目
したいのは、V$LATCH表の”GETS”の値と”MISSES”の値である。
“GETS”はラッチの取得回数、”MISSES”はその名のとおり、ラッチを取得しよう
として失敗した回数である。
ラッチの取得に失敗した場合、再度ラッチの取得を試みるため、スピンカウン
トの回数分これを繰り返す。それでもラッチが取得できない場合は、ここで
“ラッチタイムアウト”が発生し、スリープしてしまう。
（この忙しい最中にあろうことか、”寝てしまう”のである。）
では、”ラッチミス”が発生する様子を見てみよう。
CACHE BUFFER CHAIN LATCH の状況を確認するSQL文
1 select name 2 ,to_char(gets,'99999999999999990') GETS 3 ,to_char(misses,'99999999999999990') MISSES 4 from v$latch 5* where name='cache buffers chains' SQL> / NAME GETS MISSES --------------------- ------------ -------------- cache buffers chains 46201 0 SQL> / NAME GETS MISSES --------------------- ------------ -------------- cache buffers chains 46467 0 SQL>
上記は、単独のSESSIONからSCOTT.T_EMPに対してSELECT文を繰り返し発行して
いる場合の状況である。
（文末参照（※））
SELECTが繰り返されるたびにラッチを取得し、その際にラッチの取得には失敗
していないことがわかる。
では、複数SESSION(今回は 4 SESSION)から同一オブジェクト(SCOTT.T_EMP)に
対してSELECT文を発行した場合はどうだろうか。
SQL> / NAME GETS MISSES --------------------- ------------ -------------- cache buffers chains 201857 252 SQL> / NAME GETS MISSES --------------------- ------------ -------------- cache buffers chains 212433 287 SQL> / NAME GETS MISSES --------------------- ------------ -------------- cache buffers chains 226029 340 SQL>
“GET_MISS”の値が上昇していることから、ラッチの取得に失敗していること
がわかる。複数SESSIONから同一オブジェクトにアクセスが集中するとこのよ
うなことが起こるのである。
これはとりもなおさず、同じHASH_BACKETS上に存在する、別のオブジェクトの
データブロックへのアクセスへも影響を及ぼすことになる。
同一オブジェクトへのアクセス集中は、DISK上でのみ発生する問題ではないの
だ。
DISK上のアクセス集中はDISKのストライピングなどによる対処を考えるが、メ
モリ上で発生する競合を解消する手段はどのような方法が考えられるだろうか。
そう、まずは全件検索の解消だろう。
ラッチに関して、全件検索処理時と、INDEX検索時の違いがどの程度あるかを
見てみよう。
なお、上記のようにラッチの”GETS”や”MISSES”の値を累計値で表示すると経過
時間ごとの違いがわかりにくいため、単位時間あたりの差分で表示するように
工夫してみた。
（文末参照（※））
************************************************************
:
I=[14] GETS=[68384] MISSES=[719]
I=[15] GETS=[72622] MISSES=[582]
I=[16] GETS=[65487] MISSES=[368]
I=[17] GETS=[16322] MISSES=[121]
I=[18] GETS=[8362] MISSES=[33]
I=[19] GETS=[7483] MISSES=[39]
I=[20] GETS=[8120] MISSES=[32]
I=[21] GETS=[7963] MISSES=[43]
I=[22] GETS=[7306] MISSES=[23]
:
************************************************************
上記のカウンタの”17″までは、INDEXなしで全件検索を行っていたときの、そ
して”18″以降は、EMPNOカラムにINDEXを作成したあとのラッチのGET/MISSの
値である。
メモリ上のロック（上記のGETの値）が1/10になっていると見れば、かなり有
効に思われるが、いかがだろうか。
たとえディスクへのアクセスが発生していない場合でも、メモリ上の競合を防
ぐためにも不要な全件検索は避けるべきである。
ちなみに、上記のINDEXをUNIQUE INDEXにすると、
************************************************************
:
I=[23] GETS=[7410] MISSES=[13]
I=[24] GETS=[7058] MISSES=[37]
I=[25] GETS=[22976] MISSES=[76]
I=[26] GETS=[4172] MISSES=[1]
I=[27] GETS=[3977] MISSES=[1]
I=[28] GETS=[3759] MISSES=[0]
I=[29] GETS=[3940] MISSES=[0]
I=[30] GETS=[3840] MISSES=[3]
I=[31] GETS=[3730] MISSES=[0]
I=[32] GETS=[4390] MISSES=[0]
:
************************************************************
さらにラッチの”GETS”も”MISSES”も少なく出来る。(カウンタの”26″以降）
INDEXの作成は、そのオブジェクトへアクセスするSQL文のレスポンスに影響
を与えることはもちろんのこと、ラッチの取得にも影響を与えるのである。
ひいては、そのオブジェクトには関連しないSQLのレスポンスにも影響を与え
ることになる。
お分かりだろうか、”CAHCE BUFFER CHAIN”のラッチが取得された場合、その
間、同一オブジェクトのみならず、同一HASH BACKETS上に存在するその他の
オブジェクトに対するアクセスも”ラッチミス”となってしまうのである。
INDEXを作成することで、全件検索が解消され該当SQLのレスポンスが向上す
るとともに、並行しているトランザクションも効率よく処理されるようにな
る。
全件検索”はた迷惑”。
次回は、V$SQLAREAにもどってみよう。
参照(※)
今回検証用のテーブルT_EMP表を作成した。
また、検証中のindexの作成は以下のとおり行った。
connect scott/tiger SQL> CREATE TABLE T_EMP AS SELECT * FROM EMP; index の作成 SQL> CREATE INDEX IDX_EMP ON T_EMP(EMPNO); unique index の作成(上記のindexをdropの後作成) SQL> CREATE UNIQUE INDEX IDX_EMP ON T_EMP(EMPNO);
「同一オブジェクトへのSELECT文発行スクリプト」
(今回の検証では、以下のスクリプトを最大4つのSESSIONで同時に実行した。)
--- POPSQL START -----------------
CONNECT scott/tiger
LOOP(I=0;I<100000;I++)
SQL select EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
from t_emp where EMPNO = I;
LET SCNT = SEL_CNT
MESSAGE I=[I] TIME=[TIME] SEL_CNT=[SCNT] OERR=[OERR]
ENDLOOP
EXIT
--- POPSQL END -------------------
「ラッチの単位時間あたりの”差分”を確認するスクリプト」
--- POPSQL START -----------------
CONNECT sys/manager as sysdba
LOOP(I=0;I<100000;I++)
SAMPLE select name
,to_char(gets,'99999999999999990') GET1
,to_char(misses,'99999999999999990') MISS1
from v$latch
where name='cache buffers chains';
SLEEP 10
SAMPLE select name
,to_char(gets,'99999999999999990') GET2
,to_char(misses,'99999999999999990') MISS2
from v$latch
where name='cache buffers chains';
LET GET = GET2[0]-GET1[0]
LET MISS = MISS2[0]-MISS1[0]
PRINT I=[I] GETS=[GET] MISSES=[MISS]
ENDLOOP
EXIT
--- POPSQL END -------------------
以上 原っぱをサンダルで走るとハチに刺される茅ヶ崎にて