┏┏┏┏┏━━━━━━━━━━━━━━━━━━━━━━━━…・・ ┏━
┏┏┏┏┛ 2002.07.31 ┏┛┛
┏┏┏┛ ☆おら！オラ！Oracle -どっぷり検証生活-★ ┏┛┛┛
┏┏┛ ┏┛┛┛┛
┏┛・・…━━━━━━━━━━━━━━━━Vol.115━…・・┏┛┛┛┛┛
◇目次◇
■Oracle検証生活・・・共有プール領域に関する検証 その３
■Ｑ＆Ａコーナー
■お知らせ・・・○ＱＡについて
■編集者より
・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・
■□注意事項□■
本文中にテーブルが含まれていますので、お読みになる際はMSゴシッ
ク等、等幅フォントをお使いただくことをお勧めします。
・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・・
┏─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━┓
●【 Oracle 検証生活 】 ●
┗─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━┛
＜共有プールに関する検証 その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 -------------------
以上 原っぱをサンダルで走るとハチに刺される茅ヶ崎にて
◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆
一緒に検証してみませんか？
http://www.insight-tec.com/jp/html/company/career.html
◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆◆
┏─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━┓
●【 Ｑ＆Ａコーナー 】 ●
┗─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━┛
☆★Question１★☆
負荷の高いSQL文を確認するSQL文?(DISK_READSが大きいSQL文の取得)
*************************************************************
1 select sql_text
2 ,executions
3 ,disk_reads
4 ,buffer_gets
5 from v$sqlarea
6 where decode(executions ,0,disk_reads,disk_reads/executions)
7 > (select avg(decode(executions,0,disk_reads
8 ,disk_reads/executions))
9 + stddev(decode(executions,0,disk_reads
10 ,disk_reads/executions))
11 from v$sqlarea)
12* and parsing_user_id != 0
SQL> /
上記SQLで、and parsing_user_id != 0 の意味は、
sysが実行したSQLは除きたい、と言うことでしょうか？
そうであれば、この条件を付ける意図が今一つ理解できませんので、ご教授願
います。
現実的には、sysが実行するSQLでDISK_READSが大きい物はほとんどないと考え
られるのでこの条件の有無はあまり問題ではないと思います。が、万一、sys
が実行するSQLでDISK_READSが大きい物があった場合、当然、その原因を調べ
る必要があるので、この条件ははずすべきではないかと思うのですが、いかが
でしょう？
DISK_READSの閾値を作る方（副問い合わせ）ではsysが実行しているSQLも考慮
にいれているようですし。
☆★Answer＆Advice★☆
ご指摘のとおり、「and parsing_user_id != 0 の意味は、」SYSユーザが発
行したSQLは「取りあえず、見ないでおく」ための一文です。
その心は！？
１．SQLの調査をする場合多くはSYSユーザで実行するだろうから、「自分が発
行したSQLはいちいち見なくていいよ。」
２．SYSが発行するSQL文で負荷の高いSQLはほとんどないだろうし、仮に存在
した場合、一般的にはアプリケーション開発者の立場からは手を加えにく
いだろうから、「取り敢えず対象からは外しておこう。」
と、いうところです。
いずれにしても、SYSが発行したSQL文も負荷の高いSQLの検索対象になるので、
「SYSユーザが発行したSQL文は必ずしも負荷が高くない。」わけではありま
せん。
SYSユーザが発行したSQLはまったく関係ないと思われた方がいらっしゃったら、
ごめんなさい。
┏─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━┓
●【 ＱＡについて 】 ●
┗─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━┛
＜皆様からのＱＡを受付けております＞
皆様のＱＡにはできるだけ、お答えしたいと思っています。
すべてのＱＡにお答えすることはできないかもしれませんが、
適宜メルマガ内でとりあげていく予定ですので、是非ＱＡをお寄せください。
┏─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━┓
●【 編集者より 】 ●
┗─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━─━┛
朝、いつものように歩いていたら後ろから自転車に追い越されたんです。い
つものことだし、あまり気にならなかったんですが・・。ヒラヒラが目に飛
び込んできたんですっ！そうなんです。後ろ襟から値札らしき紙がお姉さん
の髪と一緒になびいていたんです。言ってあげようと思っても、相手は自転
車・・。追いつくこともできずにお姉さんは行ってしまいました。あのまま
電車に乗ったのかな、っと今日一日気になってしかたがないんですよねぇ。
by TI
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
登録・解除は以下のURLで行うことができます。
http://www.insight-tec.com/jp/html/ora3/ora3.html
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
＜おら！オラ！Oracle－どっぷり検証生活－＞
発行／編集：株式会社インサイトテクノロジー
http://www.insight-tec.com
マガジンID：0000030093
本メールマガジンに掲載された記事を許可なく転載することを禁じます。
Copyright (c) 1996-2002, Insight Technology, Inc. All rights reserved.
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
