株式会社インサイトテクノロジー 発行
http://www.insight-tec.com/jp
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ Vol.16
☆☆☆ おら！オラ！ Oracle −どっぷり検証生活− ☆☆☆
2000.08.02
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
他ではなかなか得られない、マニュアルを読んでもわからない、
そういったOracleに関する技術情報をお届けするメルマガです。
実際に検証した結果も交えてお伝えしていきます。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
<<目次>>
■Oracle検証生活・・・インデックスに関する検証 その８
■QAコーナー・・・インデックスのREBUILDについて
■お知らせ・・・○Oracle管理ツール ○連載情報
○書籍ご予約受付中 ○ＱＡについて
■編集者より
■■注意事項！！■■
本文中にテーブルが含まれていますので、お読みになる際はMSゴシック等、
等幅フォントをお使いただくことをお勧めします。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
▼ Oracle検証生活 ▼━━━━━━━━━━━━━━━━━━━━━━━━
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
＜インデックスに関する検証 その8＞ ペンネーム つけまい
--- インデックス利用の落とし穴
レンジ検索で全件検索よりも性能ダウン ---
前回、大量の削除処理によってインデックス内に空のブロックが数多く発生し、
空のブロックまでもがアクセスの対象となってしまい、そのことが起因してパ
フォーマンスが劣化してしまうという様子を見てきた。
今回は、これらのインデックスの構造上の問題と、SQLオプティマイザとの関係
について見て行く。
●SQLオプティマイザ
実行されたSQL文は、翻訳の過程で実行計画を作成する。その機能を指してオプ
ティマイザと呼んでいる。また、実行計画を作成する方法として
「ルール・ベース」と「コスト・ベース」の二通りがあり、それぞれの機能の
ことを「ルール・ベース・オプティマイザ（RBO）」
「コスト・ベース・オプティマイザ（CBO）」と呼んでいる。
これらの機能とインデックスの関係を簡単に説明すると、
RBO = 条件式にインデックスを用いれば、必ずインデックス検索を行う
CBO = 条件式にインデックスを用いても、ANALYZE情報を参照し、場合によって
はインデックス検索を行わない
つまり、テーブルに対してANALYZEしてあればCBO、していなければRBOとなる。
なお、「コスト・ベース・オプティマイザ」の「コスト」とは、Oracleブロッ
クにアクセスするI/O回数のことであり、実際にはこのI/O回数を基準にし、イ
ンデックス検索時のコストと、全件検索時（Full）のコストとを比較し、I/O回
数が少ない方を選び実行する。
テーブルTEST01（1万件（99万件削除））に対して、ANALYZEする前とANALYZEし
た後にそれぞれ同じSELECT文を発行した際の結果を、以下のURLに示す。
ANALYZE前 ⇒ http://www.insight-tec.com/html/reference/ref14c.html
ANALYZE後 ⇒ http://www.insight-tec.com/html/reference/ref16a.html
ANALYZE後の検索結果の＜検索1＞をご覧になればお分かりの通り、条件式にイ
ンデックスを用いているにも関わらず、インデックスに対するI/O回数が0になっ
ている。これは、先に説明したCBOがANALYZE情報を基に、1万件中の5000件（50%）
を検索するのであれば、インデックス検索よりも全件検索の方がコストが低い
と判断したからだ。つまり、インデックスキーが格納されているリーフ・ブロッ
クを一つ一つアクセスするよりも、Oracleの初期設定パラメータである
「db_file_multiblock_read_count」により、実レコードが格納されているOracle
ブロックを数ブロック単位（デフォルト8）で直接アクセスした方が速いと判断
したからだ。
厳密に言うと、Selectivity（参照性）を基に、上記のような判断を行っている。
Selectivityとは、インデックス検索を行うか全件検索を行うかを決定するため
に、全件検索の参照性を導き出すためのものである。
検索コストの多くは、このSelectivityを基に、参照性パーセンテージを求めて
導き出されている。
このSelectivityに関しては、別のシリーズの中で紹介する予定である。
上記の結果は、1万件のテーブルに対して50%に当たる5000件分の検索を行った
ものだ。
では、極端にレンジ検索の範囲を狭くして、1件目以下全てを検索するという
SELECT文を発行してみると・・・
検索結果 ⇒http://www.insight-tec.com/html/reference/ref16b.html
上記の条件式にインデックスを用いた検索結果で、インデックスに対してI/Oが
発生しているが、これは当然の結果と言えよう。先ほどの検索は全体の50%だっ
たので、CBOがインデックス検索よりも全件検索を行った方がコストが低いと判
断した結果だが、今回の検索は全体の0.01%にしか及ばないため、CBOがインデッ
クス検索を行った方がコストが低いと判断したからだ。
しかし、ここでも空のリーフ・ブロックに対するI/Oが発生していることに、お
気付きいただけただろうか。リーフ・ブロックには必ず昇順でキーが格納され
ているので、先頭のリーフ・ブロックの一番最初のキーを読むだけであれば、
ルート（1）、ブランチ（1）、リーフ（1）の3I/Oだけで済むはずなのだが、
ANALYZEを行う前と同様、12413回もの空のリーフ・ブロックに対するI/Oが発生
していることになる。
空のリーフ・ブロックをアクセスしてしまう様子
⇒ http://www.insight-tec.com/html/reference/ref15.html
これらの結果から、CBOはANALYZE情報を参照し、何レコード（ブロック）中の
何レコード（ブロック）をアクセスするのかといったCBO独自の基準を基に、イ
ンデックスを参照するか否かを判断している。しかし、インデックスの構造ま
では判断材料に入っていないため、Skew（偏る）してしまった、または密度が
低くなってしまったインデックスに対しては、必ずしも最良のコストを導き出
してくれるものではなくなってしまう。
今後も、このCBOの判断基準については、必ず検証する機会をつくって、読者の
方々に結果を報告する予定である。
読者の方々も、空いている時間を利用して、是非、この検証にチャレンジして
いただきたい。
もし、何らかの結果が得られたら、「つけまい」まで一報をいただきたい。
今回をもちまして、「つけまい」はつけまいの旅に出ます。
暑さが和らいだ頃に、必ずやパワーアップして戻ってまいる所存でございます。
それまでの間、どうぞ「つけまい」に充電期間を与えて下さい。
次回からは、「ちゃむ」の一番弟子である「モンキーターン」が、引き続き
インデックスに関する検証結果を報告していく予定です。
今後とも「おら！ オラ！ Oracle − どっぷり検証生活」に、皆様の熱いご声援
をお願い致します。皆様からのご声援、ご指示に支えられて、今後も質の高い
情報を提供していく予定です。
猛暑 茅ヶ崎にて
〜インデックスに関する検証 その8〜
by つけまい
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
▼ ＱＡコーナー ▼━━━━━━━━━━━━━━━━━━━━━━━━━
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
☆★Question１★☆
＞インデックスのREBUILDについて教えて下さい。
＞これは、現存するインデックス情報を元に新しいインデックスを別名で作成し、
＞古いインデックスを削除後、新しいインデックスの名前を元の名前に変更して
＞いると理解しているのですが間違いないでしょうか。
＞この場合、表領域としてはインデックス２つ分の容量が必要になるのでしょう
＞か。
☆★Answer＆Advice★☆
ご認識されている通りでよろしいかと思います。
REBUILDは、CREATE INDEXのようにテーブル対しての全件検索は行いません。現
在のINDEXを基にして作成し直すため、高速に処理が行えます。
しかし、その反面、INDEXが存在するテーブル・スペース上に、一時的ではあり
ますが2つのINDEXの領域が必要になります。つまり、REBUILDされる側の元々の
INDEXと、新規に作成されているINDEXの領域が必要になってしまいます。した
がって、REBUILDを行う際は、そのテーブル・スペースに十分な空きスペースが
存在していなければなりません。
ただし、TABLESPACEを指定して、REBUILDされるテーブル・スペースを変更して
いる場合は、その指定されたTABLESPACEに新しいINDEXが作成されるため、元の
表領域としては、２つ分の容量が必要という訳ではありません。
また、REBUILDする際は、せっかく再構築するのですから、INITIAL、NEXTも再
検討するべきでしょう。
さらに、REBUILDを行なっている最中でも、INDEX検索等は、元のINDEXに対して
行なわれるため、その間、全件検索になる訳ではありません。
実際には、REBUILD処理中に付けられる仮名を、元の名前に変更するほんの一瞬
だけINDEXが存在しないことがあるかもしれません。
以下に、REBUILD中のINDEXが、どのような名前（仮名）で作成されているかの
検証結果を掲載しておきます。
【検証結果】
ALTER INDEX TEST_IDX REBUILD ;
（TEST_IDXはUSERSに存在している）
----- REBUILD処理のために作成される索引の検索 -----
SELECT SEGMENT_NAME,TABLESPACE_NAME,SEGMENT_TYPE FROM USER_SEGMENTS ;
SEGMENT_NAME TABLESPACE_NAME SEGMENT_TYPE
------------ --------------- ------------
2.29198 USERS TEMPORARY
----- REBUILD処理のために作成された索引と同じ場所に
正規の索引名「TEST_IDX」が作成（RENAME）されている様子 -----
SELECT HEADER_FILE,HEADER_BLOCK FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = 'TEST_IDX' ;
HEADER_FILE HEADER_BLOCK
----------- ------------
2 29198
セグメント・ヘッダーが設定されているファイルのIDと、セグメント・ヘッダー
が設定されているブロックのIDがセットになってREBUILD処理中のセグメント名
（仮名）に割り当てられていた様子がうかがえる。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
▼ Oracle 管理ツール Performance Insight ▼━━━━━━━━━━━━
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Oracleを知り尽くしたメンバーが開発したOracleパフォーマンス監視ツール
の決定版。それがPerformance Insightです。インサイトテクノロジーの技術
者の知恵とノウハウがここに結集！
パフォーマンス監視だけでなくOracleを使用しているシステムの運用、管理、
そして開発にも役立つ機能がいっぱいです。
詳しくは以下のURLをご覧ください。
http://www.insight-tec.com/jp/products/products.html
また無料で試使用することも可能です。
是非お問い合わせください。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
▼ 連載情報 ▼━━━━━━━━━━━━━━━━━━━━━━━━━━━
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
＜雑誌連載：体験的・DBAのお仕事（DB Magazine（翔泳社）)＞
Oracleのエキスパートとして定評のある弊社のスタッフが執筆しております
連載記事です。Oracle関連業務に携わる方、是非ご一読ください。
９月号が発売中です。
http://www.insight-tec.com/jp/topics/magazine.html
上記のURLで各回のタイトルがご覧になれます。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
▼ 書籍ご予約受付中 ▼━━━━━━━━━━━━━━━━━━━━━━━
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
＜送料無料でお届けします＞
「Oracle8 プロフェッショナルテクニック」は大好評につき、現在在庫切れと
なっており、ご注文いただいた皆様にはたいへんご迷惑をおかけしております。
まもなく入荷する予定ですので、今しばらくお待ちください。
これからご注文いただく方も、入荷次第発送させていただくということで
予約注文を受付けておりますのでよろしくお願い申し上げます。
また弊社のHPよりお申し込みいただいた方に限り、送料無料でお届けします。
専門書としては異例の速さで増刷が決定するほどの好評をいただい
ている「Oracle8 プロフェッショナルテクニック」をぜひご活用ください。
http://www.insight-tec.com/jp/topics/books.html
ホームページより受付中です。お早めに！
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
▼ ＱＡについて ▼━━━━━━━━━━━━━━━━━━━━━━━━━
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
＜皆様からのＱＡを受付けております＞
皆様のＱＡにはできるだけ、お答えしたいと思っています。
すべてのＱＡにお答えすることはできないかもしれませんが、
適宜メルマガ内でとりあげていく予定ですので、是非ＱＡをお寄せください。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
▼ 編集者より ▼━━━━━━━━━━━━━━━━━━━━━━━━━━
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
最近茅ヶ崎にもスターバックスができた。わたしはフラぺチーノが好きなの
で、とってもうれしい。でも、アメリカでしかスターバックスに行ったこと
がなかったので、思わず英語で注文しようとしてしまった。その上、店員の
お姉さんがあまりに流暢な日本語（当然なんだけど）で話し掛けるので、驚
いてしばし思考がとまってしまい、なぜかたどたどしい日本語で注文するハ
メになってしまった....。 by UA
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
登録・解除は以下のURLで行うことができます。
http://www.insight-tec.com/jp/em/mail_magazine.html
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
＜おら！オラ！Oracle−どっぷり検証生活−＞
発行／編集：株式会社インサイトテクノロジー
http://www.insight-tec.com/jp
マガジンID：0000030093
本メールマガジンに掲載された記事を許可なく転載することを禁じます。
Copyright (c) 1996-2000, Insight Technology Inc., All rights reserved.
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Insight Technology, Inc
Japanese | English