ユニークインデックスvs非ユニークインデックスcbc latch
6711 ワード
テストの目的
単純なテストにより,等値述語クエリを行う場合のcache buffers chains latchとconsistent getsの2つの指標に対する一意インデックスと非一意インデックスの影響を比較した.
テストの結論:
テーブルのカラムが一意のインデックスを作成する条件に合致する場合は、cache buffers chains latchの競合を回避するために、一意のインデックスを作成することが望ましい.唯一のインデックスはcache buffers chains latch競合を減らすだけでなく、論理読みをある程度減らすことができます.
ユニークインデックス等値述語クエリーでは、ルートノード、ブランチノード、リーフノード、データブロックは共有モードでcache buffers chains latch(cbc latchと略称)を1回取得し、cbc latchの保護の下でbufferを読み取り、共有モードbuffer pinを加えるだけです.
一意のインデックス・アクセスでデータ・ブロックを読み込むには、次の手順に従います.
上記の読み取り方式はoracleに単独の統計量で体現されています:consistent gets-examination、consistent gets-examinationの値も最終的にconsistent getsにまとめられます.
非一意インデックス等値述語クエリーでは、ルートノード、ブランチノードは共有cbc latchを1回だけ取得し、リーフノード、データブロックは排他モードで2回cbc latchを取得する必要があります.ルートノードとブランチノードがcbc latchを1回しか取得できないのは、oracleが最適化されているため、ルートノードとブランチノードの読み取り量がリーフノードよりはるかに大きく、cbc latchの競合を引き起こす可能性が高いため、oracleはノードとブランチノードとの操作に対して、共有cbc latchを取得してbuffer pinを加えない方法を使用しています.
リーフ・ブロックとデータ・ブロックの一般的なアクセス手順:
上記の読み取り方式はoracleに単独の統計量で体現されています:consistent gets.
上記の手順から分かるように、一意インデックスのコードパスは非一意インデックスよりも短く、保持するcbc latchのモードは共有モードであり、一意インデックスではなく排他モードのcbc latchを保持する必要がある(これはbufferにbuffer pinを追加するためである).テーブルのカラムが一意インデックスの作成条件に合致する場合は、cbc latchによる競合を最小限に抑えるために一意インデックスを作成することをお勧めします.
テストプロセス
具体的なテスト例を見てみましょう.
create table test as select * from dba_ocache bjects;
シーンが一意でないインデックスの下で、cbc latchの保有回数
object_id上に一意でないインデックスを作成する
create index tt on test(object_id);
インデックスのblevelは1、すなわちルートブロックとブランチブロックが1つです.
execute snap_latch.start_snap
select object_name from test where object_id=46;
select object_name from test where object_id=46;
select object_name from test where object_id=46;
select object_name from test where object_id=46;
select object_name from test where object_id=46;
select object_name from test where object_id=46;
select object_name from test where object_id=46;
select object_name from test where object_id=46;
select object_name from test where object_id=46;
select object_name from test where object_id=46;
execute snap_latch.end_snap
---------------------------------
Latch waits:- 05-Aug 13:17:21
Interval:- 0 seconds
---------------------------------
Latch Gets Misses Sp_Get Sleeps Im_Gets Im_Miss Holding Woken Time ms
----- ---- ------ ------ ------ ------- ------- ------- ----- -------
cache buffers chains 71 0 0 0 0 0 0 0 .0
上のコードは同じSQLに対して10回クエリーをしました.その間のセッションcache buffers chains latchの統計量(インクリメンタル)が印刷されます.Cache buffers chainsの取得回数は71回であった.
前述したように、ルートノード、ブランチノード(ここでブランチノードとノードは同じ)のcbc latchは毎回1回しか持たない.リーフノードとデータブロックノードは,読み出しごとに2回持つ必要がある.
それでは1回SQLを実行して、必要なcbc latch数は:【ルートノード】1+【リーフブロック】2+【データブロック】2=5
10回実行:5*10=50回
しかし、私たちが見たcbc latchは71回も持っていて、21回も多くなっています.実は21回のうち20回は、リーフノードが生成したものです.非一意インデックスなので、ORACLEはリーフブロックから最初の適合値を取り出して表に戻してこのレコードを取り出した後、次のインデックスentryが記録に合致するかどうかを確認しないため、インデックスブロックをもう一度読み取る必要があります.ただし、唯一のインデックスはこの問題はありません.この点は、論理読取数によっても検証できます.
execute snap_my_stats.start_snap
select object_name from test where object_id=46;
select object_name from test where object_id=46;
select object_name from test where object_id=46;
select object_name from test where object_id=46;
select object_name from test where object_id=46;
select object_name from test where object_id=46;
select object_name from test where object_id=46;
select object_name from test where object_id=46;
select object_name from test where object_id=46;
select object_name from test where object_id=46;
execute snap_my_stats.end_snap
---------------------------------
Session stats - 05-Aug 13:25:07
Interval:- 0 seconds
---------------------------------
Name Value
---- -----
consistent gets 40
consistent gets from cache 40
consistent gets from cache (fastpath) 30
consistent gets - examination 10
40回の論理読み、SQL毎の論理読みが4,4=1回のルートブロック+2回のインデックスブロック+1回のデータブロックが一意のインデックスであれば、1回のインデックスブロックを読み込むだけでOKです.consistent gets-examinationは10で、SQL実行が10回行われたため、インデックスルートブロック(ブランチブロック)が10回読み込まれた.
シーン2の一意のインデックスの下で、cbc latchの保有回数
object_id上に一意でないインデックスを作成する
drop index tt;
create unique index tt on test(object_id);
----- ，
---------------------------------
Latch waits:- 05-Aug 13:27:11
Interval:- 0 seconds
---------------------------------
Latch Gets Misses Sp_Get Sleeps Im_Gets Im_Miss Holding Woken Time ms
----- ---- ------ ------ ------ ------- ------- ------- ----- -------
cache buffers chains 31 0 0 0 0 0 0 0 .0
【ルートノード】1+【リーフブロック】1+【データブロック】1=3
10回、合計30回のcbc latch取得を実行します.余分な一度は???
cbc latchの取得回数は前のシーンより71-31=40回減少した.これは素晴らしい!
同一論理読み出し数:
---------------------------------
Session stats - 05-Aug 13:27:46
Interval:- 0 seconds
---------------------------------
Name Value
---- -----
consistent gets 30
consistent gets from cache 30
consistent gets - examination 30
1回のSQLの論理読み出し数:
1次ルートブロック+1次インデックスブロック+1次データブロック=3
10回実行された論理読みは、10*3=30です.
一意のインデックスであるため、リーフブロックを1回クエリーした後、インデックスブロックに戻ってcheckする必要はありません.論理読み出し数も前のシーンより40-30=10回少ない.
魏興華[email protected]沃趣科技高級データベース専門家、プロジェクトマネージャー会社のウェブサイト:http://www.woqutech.com杭州市浜江区長河街道浜安路1190号3棟智匯領地科学技術園A区A棟10階1004室(31053)