Oracle B-Tree Index Internals:Rebuilding The Truth(4)
マイミクさんから教えてもらったのですが、 you tubeで千手観音という中国の素敵な演舞を見ました。 美輪明宏さんが号泣されたということもうなづける内容でした。 なんとメンバー全員が視聴覚になんらかの障害を持っているとか! http://www.youtube.com/watch?v=1sh6RzzMrcw (ご覧になる時は是非音声込みで) 引き続きRichard FooteさんのIndex Rebuildに関するプレゼン資料を ご紹介します。(今回で最終回です) 前回までの発行はこちら 1.導入、B-tree Indexの基礎、Clustering Factor、Fast Full Scanの説明 2.Indexブロック分割、PCTFREE、PCTUSED、FREELISTの説明 3.Index削除領域の再利用について、遅延ブロッククリーンアウトの説明 ※残り2回の予定でしたが、ちょっと無理目に押し込んで今回で最終回にしました。 ※Index用語に慣れていない方は、先にoracle解説のコーナー(前号までも含む)で 知らない用語について確認されるといいかもしれません。 ■ askTomでのIndex Rebuildに関するdiscussion http://asktom.oracle.com/pls/asktom/ ※今回は引用PDFを紹介している適切なサイトが無いので、 その資料について触れているaskTomのdiscussionをリンクとして ご紹介します。 ■ 引用PDFファイル http://www.actoug.org.au/Downloads/oracle_index_internals.pdf ___________________________________  ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ 記事本文 ___________________________________  ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ◆ Index Height - Rebuild Factor ? . The simple answer is no . Most index rebuilds do not result in a height reduction . If the pct_used is high, rebuild is pointless . If index creeps over height boundary, rebuild is still pointless as: - Instead of reading root, now read root + branch resulting in - just 1 additional cached I/O . Index eventually will grow anyways . Rebuilding an index purely because of its height is yet another myth ◆ Conditions for Rebuilds . Large free space (generally 50%+), which indexes rarely reach, and . Large selectivity, which most index accesses never reach, and . Response times are adversely affected, which rarely are. . Note requirement of some free space anyways to avoid insert and subsequent free space issues . Benefit of rebuild based on various dependencies which include: . Size of index . Clustering Factor . Caching characteristics . Frequency of index accesses . Selectivity (cardinality) of index accesses . Range of selectivity (random or specific range) . Efficiency of dependent SQL . Fragmentation characteristics (does it effect portion of index frequently used). . I/O characteristics of index (serve contention or I/O bottlenecks) . The list goes on and on …. ◆ Other Rebuild Issues To Consider . More efficient index structures can reduce stress on buffer cache. Harder to formulate but requires consideration . If you have the resources and you have the appropriate maintenance window, then the cost vs. benefit equation more favorable to rebuild . Benefit maybe low but perhaps so is the relative cost . Rebuild or Coalesce ? ◆ Index Coalesce . More efficient, less resource intensive, less locking issues than rebuild option . Can significantly reduce number of leaf blocks in some scenarios . Requires sum of free space to exceed 50% + pctfree in consecutive leaf blocks . However, as generally needs excessive 50%+ freespace for rebuild to be effective . Does not reduce index height alter index bowie_idx coalesce; ◆ Summary . The vast majority of indexes do not require rebuilding . Oracle B-tree indexes can become “unbalanced” and need to be rebuilt is a myth . Deleted space in an index is “deadwood” and over time requires the index to be rebuilt is a myth . If an index reaches “x” number of levels, it becomes inefficient and requires the index to be rebuilt is a myth . If an index has a poor clustering factor, the index needs to be rebuilt is a myth . To improve performance, indexes need to be regularly rebuilt is a myth ___________________________________  ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ 英語の解釈 ※自然な語順で解釈する癖をつけるために敢えて不自然な日本語に なっているところがあります。 ___________________________________  ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ◆ Index Height - Rebuild Factor ? indexの高さはrebuildの要因になるか? . The simple answer is no シンプルな答えはnoです。 . Most index rebuilds do not result in a height reduction ほとんどのindex rebuildはindexの高さを減らしません。 . If the pct_used is high, rebuild is pointless もし、pct_usedが高い場合、rebuildは無意味です。 . If index creeps over height boundary, rebuild is still pointless as: もし、Indexが現在のIndexの高さの限界に近づいている場合、 rebuildは以下のようにやはり無意味です - Instead of reading root, now read root + branch resulting in just 1 additional cached I/O rootブロックを読む代わりに、rootブロックとブランチブロックを 読むことになります。結果として1回のキャッシI/Oが追加されるだけです。 - Index eventually will grow anyways Indexはどちらにしてもいずれ成長します(高さが上がります)。 . Rebuilding an index purely because of its height is yet another myth 単純にIndexの高さに伴ってrebuildを実施することは やはり誤った考えです。 ◆ Conditions for Rebuilds Rebuildの条件 . Large free space (generally 50%+), which indexes rarely reach, and 大きな空き領域(通常50%以上) (Indexがこの値に他することはほとんどありません) そして。。。 . Large selectivity, which most index accesses never reach, and 高い選択性(ほとんどのIndexアクセスが決して満たしません) そして。。。 . Response times are adversely affected, which rarely are. 応答時間が悪影響している場合(あまりないですが) . Note requirement of some free space anyways to avoid insert and subsequent free space issues 心に留めてください、空き領域の要件は とにかく挿入や後続する空き領域問題を避けるためであることを (心に留めてください) . Benefit of rebuild based on various dependencies which include: Rebuildの利点は以下を含む様々な依存関係があります - Size of index Indexサイズ - Clustering Factor クラスタリングファクター - Caching characteristics キャッシング特性 - Frequency of index accesses Indexのアクセス頻度 - Selectivity (cardinality) of index accesses Indexアクセスの選択性(カーディナリティ) - Range of selectivity (random or specific range) 選択性の範囲(不規則または特定の範囲) - Efficiency of dependent SQL 依存SQLの効率 - Fragmentation characteristics (does it effect portion of index frequently used) フラグメンテーション特性 (Indexで頻繁に利用される部分に影響するかどうか?) - I/O characteristics of index (serve contention or I/O bottlenecks) IndexのI/O特性(I/O競合またはI/Oボトルネックを助ける) - The list goes on and on …. この一覧は延々と続いきます。。。 ◆ Other Rebuild Issues To Consider 考慮すべきその他のRebuild問題 . More efficient index structures can reduce stress on buffer cache. Harder to formulate but requires consideration より効率の良いIndex構造はバッファキャッシュへの圧迫を軽減します 明確に説明するのは難しいですが、考慮が要求されます。 . If you have the resources and you have the appropriate maintenance window, then the cost vs. benefit equation more favorable to rebuild もし資源を持っていて、適切なメンテナンス時間を持っているなら、 費用対利益の定式の結果はよりRebuildしやすい値になるでしょう。 - Benefit maybe low but perhaps so is the relative cost 利益は少ないかもしれませんが、関連するコストも低いかもしれません。 . Rebuild or Coalesce ? Rebuild または 結合? ◆ Index Coalesce Indexの結合 . More efficient, less resource intensive, less locking issues than rebuild option Rebuildに比べ、より効率的で、より少ないリソース集中で、 競合問題をより少なくします . Can significantly reduce number of leaf blocks in some scenarios 場合によっては多くのリーフブロックを減らすことができます。 . Requires sum of free space to exceed 50% + pctfree in consecutive leaf blocks 隣り合ったリーフブロックのpctfreeの空き領域の合計が 50%を超えている必要があります。 . However, as generally needs excessive 50%+ freespace for rebuild to be effective しかしながら、Rebuildが効果的になる為には、 一般的には50%を遥かに超える空き領域が必要です。 . Does not reduce index height Indexの高さは減少しません。 alter index bowie_idx coalesce; ◆ Summary まとめ . The vast majority of indexes do not require rebuilding 圧倒的大多数のIndexはRebuildが不要です。 . Oracle B-tree indexes can become “unbalanced” and need to be rebuilt is a myth OracleのB-tree Indexがアンバランスな状態になり、 Rebuildが必要になるという教えは誤りです。 . Deleted space in an index is “deadwood” and over time requires the index to be rebuilt is a myth Index内の削除された領域は無用で、 一定期間毎にRebuildが必要になるという教えは誤りです。 . If an index reaches “x” number of levels, it becomes inefficient and requires the index to be rebuilt is a myth もし、Indexが一定のレベルに達した場合、非効率になり、 Rebuildが必要になるという教えは誤りです。 . If an index has a poor clustering factor, the index needs to be rebuilt is a myth もし、Indexのクラスタリングファクタが悪い場合、 Rebuildが必要になるという教えは誤りです。 . To improve performance, indexes need to be regularly rebuilt is a myth パフォーマンスを向上させるために、定期的なRebuildが必要という教えは 誤りです。 ___________________________________  ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ 単語解説 ___________________________________  ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ Index Height:第1回のOracle解説のB-Tree欄参照 http://imoment.web.fc2.com/20070116.html pct_used:第2回のOracle解説欄参照 http://imoment.web.fc2.com/20070123.html creep:(気づかれないように)忍び寄る、忍び寄り(名詞) root + branch:第1回のOracle解説のB-Tree欄参照 http://imoment.web.fc2.com/20070116.html eventually:ついに、最終的には、ようやく purely:単純に、純粋に condition:条件 adversely:ネガティブだったり有害だったりする影響 subsequent:後続の based on:〜に基づいた Clustering Factor:第1回のOracle解説参照 http://imoment.web.fc2.com/20070116.html selectivity:選択性、全体の行に対してIndexによって特定できる行数の割合 (ある列値の特定で限られた行を特定できる場合、 その列のselectivityは低いと言えます) cardinality:集合の中の要素の種類の数。cardinalityが低い時、 selectivityは高くなります。 例えば列の値が男と女の2種類しかない場合、 cardinalityは2です。 通常はcardinalityではなく、cardinality度数 (全体行中の要素数の割合)を参考にします。 specific:特定の efficiency:効率 portion of:一部分の serve:役立つ、仕える、給仕する on and on:(うんざりするほど)延々と、長い間、 formulate:詳細な計画を立てる、(系統立てて)明確に説明する、 方式やシステムを考案する resource:資産、(価値のある)人材や組織 maintenance window:保守をする為の時間枠 (windowは窓の他に時間枠の意味も持っている) cost vs. benefit equation:費用対利益の定式 relative:関連/関係する coalesce:結合 ※発音(kou(e)le's:cぁLe'S) intensive:集中的な、徹底的な leaf block:第1回のOracle解説のB-Tree欄参照 http://imoment.web.fc2.com/20070116.html pctfree:第2回のOracle解説欄参照 http://imoment.web.fc2.com/20070123.html consecutive:連続した Index height:第1回のOracle解説のB-Tree欄参照 http://imoment.web.fc2.com/20070116.html vast majority:圧倒的多数 Oarcle B-tree Index:第1回のOracle解説のB-Tree欄参照 http://imoment.web.fc2.com/20070116.html deadwood:枯れ木、無用な、お荷物な ___________________________________  ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ★ 英語ぷちクイズ ★ ※答えと思うリンクをぷちっとクリックしてください。 ___________________________________  ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ☆「チャック開いてますよ」って言いたい時に一番使わないフレーズは? ◆Your barn door is open ┗ http://clickenquete.com/a/a.php?M0002066Q0019492A16c50 ◆XYZ ┗ http://clickenquete.com/a/a.php?M0002066Q0019492A29b75 ◆Your fastener is open ┗ http://clickenquete.com/a/a.php?M0002066Q0019492A3c8a3 ◆You are flying low ┗ http://clickenquete.com/a/a.php?M0002066Q0019492A4cf8d ◆Your zipper is open ┗ http://clickenquete.com/a/a.php?M0002066Q0019492A51040 ○結果を見る ┗ http://clickenquete.com/a/r.php?Q0019492C633e 締切:2007年02月18日23時00分 協力:クリックアンケート http://clickenquete.com/ こたえは次回に発表します。 ■ 前回のこたえ ☆どちらが理想的でしょうか。 ◆to study English. ◆to learn English. ○結果を見る ┗ http://clickenquete.com/a/r.php?Q0019367C983d studyはテキストや学校の授業に沿って勉強するというイメージです。 learnは主に実際の経験を伴って、原則として達成も伴う学習を意味します。 どちらが理想と考えるかは人それぞれとは思いますが、 普通に考えるとlearnでしょうか。 学校で勉強しているだけならstudy、それ以上に色々努力しているなら learnを使用すると良いですね。 ___________________________________  ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ Oracle解説 ___________________________________  ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ footeさんが過去の有識者の【うそ】を証明するために様々な角度から Indexの動作を解説してくれました。 【うそ】 OracleのB-tree Indexは時間が経つとアンバランスになり、Rebuildが必要 Deleteが繰り返されると無駄な領域が増えるのでやがてRebuildが必要 Indexのレベルがあるレベルに達すると、非効率になるのでRebuildが必要 Clustering Factorが悪くなるとRebuildが必要 パフォーマンス向上の為にIndexのRebuildが定期的に必要 以下のような【おすすめ】を参考にしながら、 今後のOracle管理に役立てていきたいですね。 【おすすめ】 Rebuildを検討する前にcoalesceを検討しましょう。 以下のケースに該当する場合、Rebuildを検討する価値があります。 ・永続テーブルの収縮 ・特定のIndex値を激減させるdeleteまたはupdateの発生 ・単調に増加するIndex値および削除 ・大規模な同一値のIndexエントリが削除された場合 Index Rebuild編もこれで終了です。 辛抱強くお付き合いいただきありがとうございました。 ___________________________________  ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ 編集後記 ___________________________________  ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ 今号からご購読いただいた皆様はじめまして。 本日も最後まで読んでいただきありがとうございます。 blog上でAdsenseというgoogleの広告サービスを使用しています。 原則としてそのページに合っている広告が表示されるので、 私のページには主にOracle関連、英語関連が表示されます。 そんな中で時々以下のような異色の広告が表示されるんです。 切れないそば打てますよ!そばアカデミー http://soba.specialist.co.jp/academy/ =>何故そば? Oracleのど飴 http://www.oracle.vc/index.html =>かなり笑いました。 それではまた。 ___________________________________  ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ おわりに ___________________________________  ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ 記述誤りなどのご指摘、 記事に関する疑問点・質問・感想・ご意見・ご感想など yakusa_oracle@yahoo.co.jpまでお願い致します。 簡単な自己紹介はこちら http://pr2.cgiboy.com/S/3191274 バックナンバー兼ブログはこちら http://imoment.web.fc2.com/ 登録・解除はこちらから http://www.mag2.com/m/0000200441.htm