Oracle9i 物理設計 |
| 第5部 領域監視 |
 |
| 日本オラクル クロスインダストリー本部 OracleDirect SCグループ 中家 裕之 12月17日・18日に開催されるOracleWorldには私の所属するOracleDirectも参加いたします。大きなOracle10gブースの隣の、小さなブースですけど・・・ 私もフルタイムではありませんが説明員として立っておりますので、お立ち寄りの際には是非この連載の感想やリクエストなどをお寄せいただければと思います。技術的なご質問やシステム構築に関するご相談などもお待ちしております。わかる範囲で、ではありますが精一杯対応させていただきます。もっとも、本連載で掲載している社員証の写真はやや古くて、実物はもっと年を食っててちょっと肉付きがよくなってます(^^;のでお見逃しなきよう。あと、講座のトップページにある音声解説つき資料のナレーションも私ではありません(同じ部署の若手です)ので、声を参考に探していただいても見つからないと思います。 |
| ◆ 第1章 領域監視の対象 | ||||||||||
はじめに 一口に領域監視といっても、その目的及び実現手段は様々です。本稿ではその中でもいわゆる「断片化」というキーワードに着目し、断片化の発生を監視するという観点で各種領域監視の対象・方法について解説いたします。監視を行って問題を洗い出した後、その問題を解決する方法については次回第6部で解説いたします。今回はSQLがたくさん紹介されますが、特別な記述がない限りSYSTEMユーザーで実行しています。他のユーザーで実行する場合、検索対象のディクショナリや権限等を適宜調整してください。なお、本章で紹介したSQLは実行例の形式になっているので、SQL文を再利用できるようCode Tipsにも掲載してあります。 断片化とは Oracleにおける断片化とは、Oracleの物理領域が何らかの形で不連続になっている状態をいいます。断片化が発生すると、発生の程度に応じて主に以下のような影響が出ます。
|
| ◆ 第3章 セグメントレベルの断片化 | ||||||||
位置の高いハイウォーターマーク HWMがどのようなものであるかについては第2章で解説しました。ただ、第2章で解説していない重要なポイントが一つあります。それは、HWMは自動では決して下がらないという点です。具体的な下げ方は次回で解説しますが、例えば全件をDELETE文で削除したとしても、HWMの位置はそのままです。
セグメントレベルのHWMは主に
（1）HWMの全件検索に対する影響
テーブルやインデックスのフルスキャンを実行する場合、実際のスキャン範囲はテーブルやインデックス全体ではなく、HWMの位置までをスキャンします。このことにより、図3の上部のように、実際に容量を確保しているサイズに比してデータ量が少ない場合の処理時間を短縮しています。しかし、HWMが自動で下がることはありません。そのため、一旦たくさんデータが入っている状態から大量削除があると、図3の下部のように、実際にはデータが入っていないにもかかわらずHWMの位置までスキャンしてしまい、実データ量に比して検索時間がかかってしまいます。
（2）HWMのダイレクト処理に対する影響
ダイレクト・ロードやダイレクト・ロード・インサートはINSERT文によりデータを挿入するのではなく、先にブロックに格納されたフォーマットイメージを作成し、そのブロックイメージを直接書き込みます。そのため少しでもデータが入っているブロックに書き込むことが出来ません。HWM以降のブロックは空であることが保証されているため、ダイレクト処理は図4の上部のようにHWM以降のブロックにデータを書き込みます。このような処理によりダイレクト処理はパフォーマンスを確保しています。ちなみにパラレル・ダイレクト・ロードの時はHWM以降のエクステントから書き込みます。もしHWM以前の領域に空きが多い場合、図4の下部のようにセグメント内に大きな空き領域が出来てしまいます。もしこのテーブルにダイレクト処理によるデータ挿入しかない場合、この空き領域は使用されることのないまま残ってしまいます。
（3）HWMの位置を知る方法
HWMの位置を知るにはDBMS_SPACEパッケージにあるUNUSED_SPACEプロシージャを利用します。このプロシージャを利用するためには、ANALYZEもしくはANALYZE ANYシステム権限が必要です。ただし、実際にANALYZEを行うわけではありませんので、ルールベースで運用しているシステムでも利用可能です。また、空き領域管理をフリーリストではなく自動セグメント管理(ASSM)を利用している場合は、UNUSED_SPACEプロシージャではなくSPACE_USAGEプロシージャを利用しないと誤った結果になってしまいます。次にUNUSED_SPACEプロシージャの利用例を用いてHWMの位置がどのように表現されるかを解説します。
＜HWMの位置を調べる＞ SQL文を抜き出す SQL> set serveroutput on SQL> declare 2 v_total_blocks number; 3 v_total_bytes number; 4 v_unused_blocks number; 5 v_unused_bytes number; 6 v_last_used_extent_file_id number; 7 v_last_used_extent_block_id number; 8 v_last_used_block number; 9 begin 10 dbms_space.unused_space(upper('&uname'), upper('&sename'), '&stype', 11 v_total_blocks, v_total_bytes, v_unused_blocks, v_unused_bytes, 12 v_last_used_extent_file_id, v_last_used_extent_block_id, v_last_used_block); 13 dbms_output.put_line('HWMのあるデータファイルのID ：' 14 || to_char(v_last_used_extent_file_id, '9,999,990')); 15 dbms_output.put_line('HWMのあるエクステントの開始ブロックID：' 16 || to_char(v_last_used_extent_block_id, '9,999,990')); 17 dbms_output.put_line('HWMのあるブロックの位置 ：' 18 || to_char(v_last_used_block, '9,999,990')); 19 end; 20 / unameに値を入力してください: SCOTT senameに値を入力してください: CUSTOMERS stypeに値を入力してください: TABLE 旧 10: dbms_space.unused_space(upper('&uname'), upper('&sename'), '&stype', 新 10: dbms_space.unused_space(upper('SCOTT'), upper('CUSTOMERS'), 'TABLE', HWMのあるデータファイルのID ： 11 HWMのあるエクステントの開始ブロックID： 1,033 HWMのあるブロックの位置 ： 6 PL/SQLプロシージャが正常に完了しました。 上記SQLスクリプトの実行結果を解説すると、「HWMのあるデータファイルのID」は、プロシージャのV_LAST_USED_EXTENT_FILE_IDパラメータの値になります。DBA_DATA_FILESディクショナリのFILE_ID列がV_LAST_USED_EXTENT_FILE_IDパラメータと一致するデータファイル中にHWMが存在することを示しています。「HWMのあるエクステントの開始ブロックID」は、プロシージャのV_LAST_USED_EXTENT_BLOCK_IDパラメータの値になります。???_EXTENTS(以下???はDBA/ALL/USERのいずれか)ディクショナリのFILE_ID列がV_LAST_USED_EXTENT_FILE_IDと同じで、かつBLOCK_ID列がV_LAST_USED_EXTENT_BLOCK_IDと一致するエクステンのト中にHWMが存在することを示しています。「HWMのあるブロックの位置」は、プロシージャのV_LAST_USED_BLOCKパラメータの値になります。該当エクステントのV_LAST_USED_BLOCKパラメータの値番目のブロックにHWMが存在することを示しています。
セグメントレベルの未使用領域の発生 〜HWM以降の未使用領域〜 第2章のデータファイルのHWM以降の未使用領域と同様の影響があります。HWM以降の未使用領域は以下のような方法で調べることが可能です。 （1）DBMS_SPACE.UNUSED_SPACEプロシージャを利用する
前述のDBMS_SPACE.UNUSED_SPACEプロシージャを利用することでセグメントのHWM以降の未使用領域の大きさが計算できます。下記サンプルスクリプトを参照してください。
＜HWM以降の未使用領域を求める(1)＞ SQL文を抜き出す SQL> set serveroutput on SQL> declare 2 v_total_blocks number; 3 v_total_bytes number; 4 v_unused_blocks number; 5 v_unused_bytes number; 6 v_last_used_extent_file_id number; 7 v_last_used_extent_block_id number; 8 v_last_used_block number; 9 begin 10 dbms_space.unused_space(upper('&uname'), upper('&sename'), '&stype', 11 v_total_blocks, v_total_bytes, v_unused_blocks, v_unused_bytes, 12 v_last_used_extent_file_id, v_last_used_extent_block_id, v_last_used_block); 13 dbms_output.put_line('現セグメント容量：' 14 || to_char(v_total_bytes, '999,999,999,990') || ' バイト'); 15 dbms_output.put_line('消費容量 ：' 16 || to_char(v_total_bytes - v_unused_bytes, '999,999,999,990') || ' バイト'); 17 dbms_output.put_line('残り容量 ：' 18 || to_char(v_unused_bytes, '999,999,999,990') || ' バイト'); 19 dbms_output.put_line('消費率 ： ' 20 || to_char((v_total_bytes - v_unused_bytes) / v_total_bytes * 100, '990.99') || ' %'); 21 end; 22 / unameに値を入力してください: SCOTT senameに値を入力してください: CUSTOMERS stypeに値を入力してください: TABLE 旧 10: dbms_space.unused_space(upper('&uname'), upper('&sename'), '&stype', 新 10: dbms_space.unused_space(upper('SCOTT'), upper('CUSTOMERS'), 'TABLE', 現セグメント容量： 9,437,184 バイト 消費容量 ： 8,593,408 バイト 残り容量 ： 843,776 バイト 消費率 ： 91.06 % PL/SQLプロシージャが正常に完了しました。 （2）ANALYZE実行後ディクショナリを参照する
テーブルの場合はANALYZEを実行した後の???_TABLESディクショナリのBLOCKSとEMPTY_BLOCKSの値を検索することでもHWM以降の未使用領域の大きさを求められます。BLOCKSはセグメント内の使用済みブロック数、EMPTY_BLOCKSはセグメント内の未使用ブロック数(HWM以降)を表します。
＜HWM以降の未使用領域を求める(2)＞ SQL文を抜き出す SQL> analyze table scott.customers compute statistics; 表が分析されました。 SQL> select to_char((blocks + empty_blocks) * 8192, 'FM999,999,999,990') as "テーブル容量", 2 to_char(empty_blocks * 8192, 'FM999,999,999,990') as "残り容量" 3 from dba_tables where owner = '&uname' and table_name = '&tname'; unameに値を入力してください: SCOTT tnameに値を入力してください: CUSTOMERS 旧 3: from dba_tables where owner = '&uname' and table_name = '&tname' 新 3: from dba_tables where owner = 'SCOTT' and table_name = 'CUSTOMERS' テーブル容量 残り容量 -------------------------------- -------------------------------- 9,428,992 843,776 こちらの方法によるセグメント容量と(1)のDBMS_SPACE.UNUSED_SPACEプロシージャを利用する方法によるセグメント容量の1ブロック分(本テストケースでは、ブロックサイズを8,192バイトとしています)の差は、セグメントヘッダを含むか含まないかの差です。前者がセグメントヘッダを含む大きさになっています。 セグメントレベルの未使用領域の発生 〜HWM以前の未使用領域〜 HWM以前の未使用領域の影響は、本章の最初に解説したHWMの解説を参照してください。計算方法は、テーブルのみの対応ですが、以下のようなSQLで算出できます。 ＜HWM以前の未使用領域を求める＞ SQL文を抜き出す SQL> analyze table scott.customers compute statistics; 表が分析されました。 SQL> select to_char(avg_space * blocks, 'FM999,999,999,999') "空き領域" 2 from dba_tables where owner = '&uname' and table_name = '&tname'; unameに値を入力してください: SCOTT tnameに値を入力してください: CUSTOMERS 旧 2: from dba_tables where owner = '&uname' and table_name = '&tname' 新 2: from dba_tables where owner = 'SCOTT' and table_name = 'CUSTOMERS' 空き領域 -------------------------------- 595,264 階層の深いインデックス B*Treeインデックスは階層構造になっています。この階層が深いと検索に時間がかかるようになります。階層の深さは、インデックスをANALYZEしたあと、INDEX_STATSディクショナリのHEIGHT列、ないし???_INDEXESディクショナリのBLEVEL列で調べることが出来ます。これらの列の値が4以上の場合は、インデックスを利用した検索のパフォーマンスに影響が出ますので再編成を検討してください。なお、INDEX_STATSディクショナリを検索する場合は、VALIDATE STRUCTUREオプション付きでANALYZEコマンドを実行する必要があります。 |
| ◆ 第4章 エクステントレベルの断片化 |
不連続のエクステント あるセグメントを構成するエクステントが連続して確保されていなくてもパフォーマンスにほとんど影響しません。ただ、不連続のエクステント間の他セグメントのエクステントが未使用エクステントになった場合、特にディクショナリ管理表領域でセグメント毎に個別のINITIAL/NEXT/PCTINCREASEを指定しているような場合は領域の無駄が発生しやすくなります。ローカル管理の場合はこのようなことが起こりにくい(ないし起こらない)ので特に監視する必要はないでしょう。監視したい場合は以下のようなスクリプトで監視が出来ます。 ＜エクステントが連続しているかどうかを調べる＞ SQL文を抜き出す SQL> select ext2.extent_id "エクステントID", ext2.file_id "ファイルID", 2 ext2.block_id "開始ブロックID", ext2.blocks "ブロック数", 3 case when ext2.extent_id = 0 then '先頭のエクステントです' 4 when ext2.old_fid <> ext2.file_id then 'データファイルが異なります' 5 when ext2.old_blk_id <> ext2.block_id then '不連続のエクステントです' 6 else '連続するエクステントです' 7 end "断片化状況" 8 from (select ext1.extent_id, ext1.file_id, ext1.block_id, ext1.blocks, 9 lag(ext1.file_id, 1) over (order by ext1.extent_id) old_fid, 10 lag(ext1.block_id, 1) over (order by ext1.extent_id) + 11 lag(ext1.blocks, 1) over (order by ext1.extent_id) old_blk_id 12 from dba_extents ext1 13 where ext1.owner = '&uname' and ext1.segment_name = '&sname') ext2; unameに値を入力してください: SCOTT snameに値を入力してください: C3 旧 13: where ext1.owner = '&uname' and ext1.segment_name = '&sname') ext2 新 13: where ext1.owner = 'SCOTT' and ext1.segment_name = 'C3') ext2 エクステントID ファイルID 開始ブロックID ブロック数 断片化状況 -------------- ---------- -------------- ---------- -------------------------- 0 12 113 2 先頭のエクステントです 1 13 93 2 データファイルが異なります 2 12 115 2 データファイルが異なります 3 13 95 2 データファイルが異なります 4 13 97 2 連続するエクステントです 5 13 99 2 連続するエクステントです 6 13 101 2 連続するエクステントです 7 12 119 2 データファイルが異なります 8 12 123 2 不連続のエクステントです 9 12 125 2 連続するエクステントです 10行が選択されました。 |
| ◆ 第5章 ブロックレベルの断片化 | ||||
行移行 あるレコードに対して更新が行われた際に、PCTFREEで確保した領域をもってしても元々存在していたブロックに収まらない長さになってしまったとします。この場合Oracleは該当レコードを別のブロックに格納します。この際、元のブロックに新しい格納先へのポインタを残します。このような状態を行移行と呼びます。行移行が発生すると該当レコードは本来1ブロックに収まる長さであっても2ブロックに渡って格納されます。つまり、このレコードにアクセスするためには2ブロック読み込む必要があるということです。行移行が発生しているレコードへのアクセスが多いと、読み込みI/Oの増加やキャッシュヒット率の低下といった影響が発生します。
行連鎖 行連鎖は行移行と同じくレコードが複数ブロックにまたがる現象ですが、こちらは単にブロックに格納可能なサイズ以上のレコードが複数ブロックに渡って格納される現象です。物理的に大きなレコードを入れようとしている以上仕方のない現象ですが、多発すると行移行と同様の影響があります。
行移行・行連鎖を調査する 行移行と行連鎖は同じ方法で調査します。テーブルをANALYZEした後、???_TABLESディクショナリのCHAIN_CNT列に行移行ないし行連鎖しているレコード数が格納されます。つまり、行移行と行連鎖は別々に把握することが出来ません。 ブロックレベルの未使用領域の発生 本項以前の問題が発生していないのにセグメント容量に比して格納可能なデータ量が少ない場合は、ブロックの中身が効率的に利用されていない場合が考えられます。例えばデータ格納可能領域が3000バイトに対してレコードサイズが平均2000バイトの場合、単純計算だと1ブロックにつき1000バイトの無駄が発生することになります。テーブルであればDELETEの機会に比してPCTUSEDの値が低い場合にブロックレベルの未使用領域が発生しやすくなります。インデックスの場合はDELETEが多い場合、インデックス対象列の値が昇順ではなくランダムや降順にINSERTされる場合、インデックス対象列に対して更新が発生することが多い場合にブロックレベルの未使用領域が発生しやすくなります。容量見積サービス等を利用して、理論的な見積値に対して実際の容量(HWMまでの容量)が大きすぎる場合は再編成を検討してください。特にフルスキャンの場合は見積値と実際値の比に近いレベルで処理時間がかかります。 |
