株式会社インサイトテクノロジー 発行
http://www.insight-tec.com/jp
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ Vol.15
☆☆☆ おら！オラ！ Oracle −どっぷり検証生活− ☆☆☆
2000.07.26
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
他ではなかなか得られない、マニュアルを読んでもわからない、
そういったOracleに関する技術情報をお届けするメルマガです。
実際に検証した結果も交えてお伝えしていきます。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
<<目次>>
■Oracle検証生活・・・インデックスに関する検証 その７
■QAコーナー・・・データを全件削除してもスピードが徐々に遅くなるのは？
■お知らせ・・・○Oracle管理ツール ○連載情報
○書籍ご予約受付中 ○ＱＡについて
■編集者より
■■注意事項！！■■
本文中にテーブルが含まれていますので、お読みになる際はMSゴシック等、
等幅フォントをお使いただくことをお勧めします。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
▼ Oracle検証生活 ▼━━━━━━━━━━━━━━━━━━━━━━━━
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
＜インデックスに関する検証 その7＞ ペンネーム つけまい
--- インデックス利用の落とし穴
レンジ検索で全件検索よりも性能ダウン ---
前回、インデックスに対して大量の削除処理を行った結果、全件検索よりもイ
ンデックス検索の方がはるかに遅くなってしまうという様子を見てきた。
今回は、この不可解な現象について検証結果を基に、Oracleが空のブロックを
アクセスしてしまう様子を見ていく。
●インデックスは物持ちがいい？
この不可解な結果を裏付けるために、下記のINDEX_STATSの内容及びTREEDUMPを
見ていただきたい。
●TEST01（1万件（99万件削除））のINDEX_STATSの内容
ANALYZE INDEX TEST01 VALIDATE STRUCTURE ;
索引が分析されました。
SELECT HEIGHT,BLOCKS,LF_ROWS,LF_BLKS,BR_ROWS,BR_BLKS FROM INDEX_STATS ;
HEIGHT BLOCKS LF_ROWS LF_BLKS BR_ROWS BR_BLKS
--------- --------- --------- --------- --------- ---------
3 51200 10000 13793 13792 112
●TEST01（1万件（99万件削除））のTREEDUMPの内容（縮小後）
ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME TREEDUMP LEVEL 3539' ;
----- begin tree dump
branch: 0x2000003 33554435 (0: nrow: 111, level: 2)
branch: 0x20000a3 33554595 (-1: nrow: 120, level: 1)
leaf: 0x2000004 33554436 (-1: nrow: 0)
leaf: 0x2001b2c 33561388 (0: nrow: 0)
leaf: 0x2000d98 33557912 (1: nrow: 0)
leaf: 0x2002c34 33565748 (2: nrow: 0)
leaf: 0x200036b 33555307 (3: nrow: 0)
leaf: 0x2001b2d 33561389 (4: nrow: 0)
leaf: 0x2000d99 33557913 (5: nrow: 0)
:
:
branch: 0x200145f 33559647 (98: nrow: 107, level: 1)
leaf: 0x2000988 33556872 (-1: nrow: 0)
leaf: 0x2002a7a 33565306 (0: nrow: 0)
:
:
leaf: 0x2002a81 33565313 (16: nrow: 0)
leaf: 0x200145d 33559645 (17: nrow: 0)
leaf: 0x2002a82 33565314 (18: nrow: 0)
leaf: 0x2000d41 33557825 (19: nrow: 0)
leaf: 0x200337b 33567611 (20: nrow: 3)
leaf: 0x2001a8f 33561231 (21: nrow: 7)
leaf: 0x200337c 33567612 (22: nrow: 7)
:
:
leaf: 0x2000d97 33557911 (125: nrow: 7)
leaf: 0x200344d 33567821 (126: nrow: 6)
leaf: 0x2001b2b 33561387 (127: nrow: 7)
leaf: 0x200344e 33567822 (128: nrow: 7)
leaf: 0x2000368 33555304 (129: nrow: 7)
----- end tree dump
まず始めに着目してもらいたいのが、LF_BLKSと BR_ROWS及びBR_BLKSである。
99万件削除したにも関わらず100万件の時と値が全く変わっていないことだ。
●TEST01（100万件）のINDEX_STATSの内容
HEIGHT BLOCKS LF_ROWS LF_BLKS BR_ROWS BR_BLKS
--------- --------- --------- --------- --------- ---------
3 51200 1000000 13793 13792 112
また、実在するリーフ・ブロックの数を表した項目がLF_BLKSであるが、この値
13793がインデックスへのI/O回数13106に近いことも頭に入れておいてもらいた
い。
TEST01（1万件（99万件削除））に対する検索結果
⇒ http://www.insight-tec.com/jp/em/mm/ref14_03.html
次に、TREEDUMPである。表示上、全体のごく一部しか掲載できなかったが、先
頭のリーフ・ブロックから0が続いている。この0が、実際には12413ブロック目
まで続いている。そして、0以外のリーフ・ブロックは1380ブロック存在する。
つまり、12413 + 1380がLF_BLKSの値13793と一致することになる。
以上のことから、インデックスへのI/O回数13106を検証すると、以下の様にな
る。
13106 = ルート(1) + ブランチ(1) + 5000件分のリーフ(691) + 0のリーフ(12413)
（）内の数字はインデックスへのI/O回数を表している
この計算式を、もっと解りやすくしたものを、以下のURLに示す。
http://www.insight-tec.com/jp/em/mm/ref15_01.html
この結果は、インデックスシリーズの冒頭でも述べた様に、削除され空になっ
たブロックがあっても、実際には切り離さずに、いつまでも保持し続けている
ことを実証したものと言えよう。しかも、その空になったブロックまでアクセ
スの対象になっていることを証明する、まさに驚きの結果である。このことは、
「インデックスの構造上の問題」と前述しているが、いくらパフォーマンスを
優先した結果から生まれた構造上の問題とは言え、あまりにもお粗末な構造で
はないだろうか？例えば、「年度が変わった時点で、一昨年のデータを全て削
除する」などといった運用を行っているユーザは少なくないはずだ。このよう
な運用を行っているシステムのDBAが、この構造上の問題を知らずに、削除後、
REBUILDなどのメンテナンスを施していなかった場合、パフォーマンスを上げる
ためのインデックスが、逆にパフォーマンスを低下させてしまう危険性がある
ことを、十分に理解してもらいたい。
次回は、これらのインデックスの構造とSQLオプティマイザとの関係について見
ていく。
茅ヶ崎にて
〜インデックスに関する検証 その7〜
by つけまい
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
▼ ＱＡコーナー ▼━━━━━━━━━━━━━━━━━━━━━━━━━
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
☆★Question１★☆
>自分が、担当するシステムで次の手順で行う処理があります。
>１．ワークのテーブルから前回のデータを全件削除する。
>２．伝票テーブルから、ワークのテーブルにデータをコピーする。
> （指定する条件により、1,000件〜300,000件ぐらい）
> （伝票のテーブルは、インデックスのレンジ検索）
>３．ワークのテーブルをインデックスで検索し、帳票を出力する。
> （インデックスのレンジ検索）
>上記のシステムを運用していると、始めは満足の行くスピードが出るのですが
>２〜３ヶ月ぐらい後では耐えがたきスピードになります。
>ワークのテーブルを再作成すると最初のスピードに戻ります。
>インデックス・空ブロックのアクセスが原因でしょうか？
>自分は、テーブルが０件になった時点で、初期状態からの挿入と同じだと思っ
>ていたのですが・・・。
☆★Answer＆Advice★☆
「メルマガVol.14〜15（今回）」をご覧いただければ、削除したはずのデータ
（インデックス）に対してI/Oが発生してしまい、そのことが起因してパフォー
マンスが悪くなってしまうという様子がうかがえるかと思います。
テーブル中のデータを全て削除したとしても、インデックスの構造は変わるこ
となく、実際にはリーフ・ブロック中にデータが残ったままになってしまいま
す。
今回の現象は「１．ワークのテーブルから前回のデータを全件削除する。」と
いう処理を、truncateではなくdeleteを行なっているのではないでしょうか？
deleteであれば、メルマガの通りパフォーマンスが悪化する原因はインデック
スの構造上の問題で、空のリーフ・ブロックが残ってしまうためです。truncate
であれば、インデックスのリーフ・ブロックも切り捨てられるので、初期状態
からの挿入と同じ扱いになります。ただし、アプリケーションによっては、ト
ランザクションの性質上、truncateでは要件に満たせないこともあり、delete
を行なうケースも多々あると思います。そのようなときは、定期的に
alter index <インデックス名> rebuild または drop index and create index
を行なう必要があります。
「ワークのテーブルを再作成すると最初のスピードに戻ります。」とあります
が、これは、テーブルを再作成することに付随して、インデックスを再作成し
ているからでしょう。
ご自分で一番納得するためには、「２〜３ヶ月ぐらい後では絶えがたきスピー
ド」になったときのインデックスのダンプを取得してみてください。リーフ・
ブロックの数は多く、1ブロック中に格納されているキーの数が非常に少ないは
ずです。それを取得した後、alter index <インデックス名> rebuildを実行し
てみてください。その後、インデックスのダンプを取得すれば、リーフ・ブロッ
クの数が少なく、1ブロック中に格納されているキーの数が多くなっているのが
確認できると思います。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
▼ 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