しばちょう先生の試して納得！DBAへの道 indexページ▶▶
みなさん、こんにちは。“しばちょう”こと柴田長（しばた つかさ）です。
さて、今回から複数回に渡り、Oracle Databaseのバージョンが上がる度に進化を続けているオプティマイザ統計収集の管理についてご紹介していきます。
オプティマイザ統計はスキーマ・オブジェクト（表や列、索引等）等の特性やデータ配分に関する情報であり、問合せオプティマイザが最良の実行計画を選択する為のコスト計算に使用されます。重要なポイントは、そのオプティマイザ統計情報の精度（どれだけ実際に格納されているデータの特徴に近いか）です。精度が悪い統計情報をベースに問合せオプティマイザが最良な実行計画を選択しても、実データにとっては最良ではない可能性がありそうですよね？
第8回目の今回は、このオプティマイザ統計情報の精度を保ちつつ、収集時間を高速化する機能を体験して頂きます。以下の演習をこれまでの演習で使用したOracle Database 11g Release 2 Enterprise Editionのデータベースで試してみてください。
尚、本連載で公開している測定時間はH/W環境やデータベース構成に大きく依存する部分である為、性能を保証するものではございませんのでご注意ください。
■ 1. 10個の列で構成される表「TBL1」を作成し、セグメント・サイズが2GBになるようレコードを挿入して下さい。また、主キー用のユニーク索引「PK_TBL1」と任意の1列に対するBツリー索引も作成してください。必要に応じて、表領域を作成して下さい。
sqlplus / as sysdba SQL> create bigfile tablespace TBS1 datafile '/oracle_datafile1/tbs1.dbf' size 4g extent management local uniform size 8m segment space management auto ; alter user TRY quota unlimited on TBS1 ; alter user TRY default tablespace TBS1 ;
sqlplus TRY/TRY
SQL>
create table TBL1 (COL01 number NOT NULL,
COL02 varchar2(10),
COL03 date, COL04 varchar2(20),
COL05 varchar2(20), COL06 varchar2(20),
COL07 varchar2(20), COL08 varchar2(20),
COL09 varchar2(20), COL10 varchar2(32)) ;
begin
for i in 0..49 loop
insert /*+append */ into TBL1
select LEVEL + 100000 * i, to_char(LEVEL + 100000 * i),
to_date('2012/07/07', 'YYYY/MM/DD'),
rpad(to_char(mod(level,5)), 20, '-'),
rpad(to_char(mod(level,50)), 20, '-'),
rpad(to_char(mod(level,500)), 20, '-'),
rpad(to_char(mod(level,5000)), 20, '-'),
rpad(to_char(mod(level,50000)), 20, '-'),
rpad('col9', 20, '-'), rpad('col10', 32, '-')
from DUAL connect by LEVEL <= 100000 ;
commit ;
end loop ;
for i in 50..99 loop
insert /*+append */ into TBL1
select LEVEL + 100000 * i, to_char(LEVEL + 100000 * i),
to_date('2012/07/07', 'YYYY/MM/DD'),
rpad(to_char(mod(level,10)), 20, '-'),
rpad(to_char(mod(level,100)), 20, '-'),
rpad(to_char(mod(level,1000)), 20, '-'),
rpad(to_char(mod(level,10000)), 20, '-'),
rpad(to_char(mod(level,100000)), 20, '-'),
rpad('col9', 20, '-'), rpad('col10', 32, '-')
from DUAL connect by LEVEL <= 100000 ;
commit ;
end loop ;
end ;
/
create unique index PK_TBL1 on TBL1(COL1) ;
alter table TBL1 add primary key (COL1) using index ;
create index IDX_TBL1_COL2 on TBL1(COL2) ;
select SEGMENT_NAME, SEGMENT_TYPE, sum(BYTES)/1024/1024 "MB"
from USER_SEGMENTS
group by SEGMENT_NAME, SEGMENT_TYPE ;
SEGMENT_NAME SEGMENT_TYPE MB
---------------- ------------------ ----------
TBL1 TABLE 2064
PK_TBL1 INDEX 176
IDX_TBL1_COL2 INDEX 216
これまでの演習の復習を行いつつ、オプティマイザ統計収集の対象となる表と索引を作成して頂きます。検証環境の作成はちょっとした腕の見せ所でもあるので、是非楽しんでみてください。上記の例では、10,000,000レコードの表を作成していますが、このレベルのSQLをサラリと書けたりするとカッコイイですよね。
ちなみに、目的のサイズの表セグメントを作成するのは非常に厄介な作業ですね。各列の定義情報や格納データを分析して計算する方法が公開されていたりもしますが、私の場合は、1000レコード程度をテスト的にINSERTした後、1つのブロック当たりに格納されているレコード数を確認することで、目的のセグメント・サイズにする為には、何レコードINSERTすべきかを見積もることが多いです。次の例のように、マニュアル「PL/SQLパッケージ・プロシージャ及びタイプ・リファレンス」に掲載されているDBMS_ROWIDパッケージのROWID_BLOCK_NUMBERファンクションを使用することで簡単に確認することができますので、参考にしてみてください。
sqlplus TRY/TRY
SQL>
select BLOCK_NO, count(*)
from (select dbms_rowid.rowid_block_number(rowid,'BIGFILE') "BLOCK_NO", COL01 from TBL1)
group by BLOCK_NO ;
BLOCK_NO COUNT(*)
---------- ----------
151 38
152 38
153 38
167 38
168 38
...........
上記の例では、各ブロックには38レコードずつ格納されていることが理解できます。ROWID_BLOCK_NUMBERファンクションは、引数としてROWIDを受け取ると、そのROWIDのレコードが格納されているブロック番号を戻すファンクションです。これは第2回の連載で説明させて頂いた「ROWIDは、そのレコードが格納されているデータ・ファイル番号やブロックアドレスから生成されている」というポイントを覚えていた方にとっては、新しい知識と古い知識が結びつくことで、理解の深まりを感じて頂けたかと思います。
一点注意点を挙げておくと、ブロック番号はデータ・ファイル毎に割り当てられるものなので、対象セグメントが複数のデータ・ファイル上に配置されている場合（1つの表領域が複数のデータ・ファイルで構成されているようなスモールファイル表領域の場合）には、上記のSQLでは正確な結果が得られません。そのような場合には、次のSQLのようにROWID_RELATIVE_FNOファンクションを使用して対処します。
select BLOCK_NO, count(*)
from (select dbms_rowid.rowid_relative_fno(rowid,'SMALLFILE') "FILE_NO",
dbms_rowid.rowid_block_number(rowid,'SMALLFILE') "BLOCK_NO",
* from
) group by FILE_NO, BLOCK_NO order by 1,2;
さあ、いよいよ演習本番です。オプティマイザ統計情報と言えば「DBMS_STATSパッケージ」だと覚えてしまって下さい。マニュアル「PL/SQLパッケージ・プロシージャ及びタイプ・リファレンス」にも記載されている通り、このパッケージには非常に便利なサブプログラム（プロシージャ、ファンクション）が多数含まれているので、機会があればこの連載でも少しずつご紹介していきたいと考えています。
今回は、基本中の基本である「GATHER_TABLE_STATSプロシージャ」を実行してみましょう。このプロシージャは、表と列および索引の統計情報を収集する際に使用します。当たり前過ぎるのか、あまり記述が見当たらないので明記しておきますと、「GATHER_TABLE_STATSで表の統計情報を収集すると、同時に各列の統計情報も収集されます。ちなみに、その表に作成されている索引の統計情報を収集するか否かはOracleが自動的に判断します。」
■ 2. DBMS_STATSパッケージのGATHER_TABLE_STATSプロシージャを使用して、表「TBL1」のオプティマイザ統計情報を収集して下さい。その際、サンプリングするレコードの割合を100%に指定し、実行時間を測定して下さい。
sqlplus TRY/TRY SQL> set timing on exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TRY', tabname => 'TBL1', estimate_percent => 100); 経過: 00:02:53.91 ※実行時間は、検証環境のH/W構成やデータベース構成に依存する点にご注意ください
マニュアルでGATHER_TABLE_STATSプロシージャを参照すると、次のような構文や各パラメータの説明が記述されています。全部で15個もパラメータがありますが、まずは、最低限指定しなければならないパラメータを読み取る必要があります。
DBMS_STATS.GATHER_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT to_estimate_percent_type
(get_param('ESTIMATE_PERCENT')),
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),
degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),
granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')),
stattype VARCHAR2 DEFAULT 'DATA',
force BOOLEAN DEFAULT FALSE);
その答えは、「DEFAULT」の記述が無いパラメータであり、対象表を保持しているスキーマ名を指定する「ownname」と対象の表名を指定する「tabname」の2つとなります。それ以外のパラメータは「DEFAULT」の後ろに記述されている設定値が適用されます。次に演習問題の「サンプリングするレコードの割合を100%に指定」という部分をどのように指定するのかという点ですが、マニュアルで各パラメータの説明を読み進めると、それとなく「estimate_percent」パラメータであることが判別できると思います。
つまり、上記の回答例のように3つのパラメータを指定するだけで、TRYスキーマの表「TBL1」のオプティマイザ統計情報をサンプリング100%で取得することができるわけです。実はそれほど難しくない。そう感じて頂けたのではないでしょうか。そして、もう一つここで感じておいて頂きたいことは、サンプリング100%のポイントです。サンプリング100% = 全てのレコードにアクセスする必要があるということ。つまり、「読み込むデータ量が多いので実行時間が長くなりそうだ。ただし、統計情報の精度は完璧」ということです。
■ 3. 表「TBL1」の各列の統計情報である「列内で異なる値の数（Number of Distinct Values）」をUSER_TAB_COL_STATISTICSディクショナリ・ビューで確認して下さい。
set pagesize 5000 linesize 120
col TABLE_NAME for a12
col COLUMN_NAME for a12
select TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, SAMPLE_SIZE,
to_char(LAST_ANALYZED, 'MM/DD HH24:MI:SS') "LAST_ANALYZED"
from USER_TAB_COL_STATISTICS
where TABLE_NAME='TBL1' order by 1, 2 ;
TABLE_NAME COLUMN_NAME NUM_DISTINCT SAMPLE_SIZE LAST_ANALYZED
------------ ------------ ------------ ----------- --------------
TBL1 COL01 10000000 10000000 07/18 14:41:39
TBL1 COL02 10000000 10000000 07/18 14:41:39
TBL1 COL03 1 10000000 07/18 14:41:39
TBL1 COL04 10 10000000 07/18 14:41:39
TBL1 COL05 100 10000000 07/18 14:41:39
TBL1 COL06 1000 10000000 07/18 14:41:39
TBL1 COL07 10000 10000000 07/18 14:41:39
TBL1 COL08 100000 10000000 07/18 14:41:39
TBL1 COL09 1 10000000 07/18 14:41:39
TBL1 COL10 1 10000000 07/18 14:41:39
折角の機会なので、サンプリング100%で収集した完璧なオプティマイザ統計情報を確認してみましょう。これもそれほど難しくはないと思います。USER_TAB_COL_STATISTICSディクショナリ・ビューには、各列の統計情報が格納されており、今回の演習ではその一つである「列内で異なる値の数（Number of Distinct Values）」を参照しています。「列内で異なる値の数」はマニュアル上の表現ですが、別の表現では「列内の個別値」、「NDV」、「値の種類」という呼ばれ方もしています。
上記の演習結果を簡単に説明しておきますね。表TBL1の列COL01のNDVは10,000,000となっていますが、これは正しいかを考えてみましょう。と言っても非常に簡単ですよね。表TBL1は合計10,000,000レコードが格納されており、列COL01は主キーの列なのでユニークな列データしか含まれないことを考えれば、このNDVの値は正確（高い精度）だと判断することができます。その他の列に関しては、演習問題0に掲載したINSERT文を解析して頂ければ、NDVの値が正確であるとご理解頂けると思います。
ちなみに、演習では確認していませんが、USER_TAB_STATISTICSディクショナリ・ビューでは表の行数（NUM_ROWS）が確認できます。基本的には、このNUM_ROWSと先に紹介したNDVの2つの値を使用して、問合せオプティマイザがSQL文でアクセスする対象の行数（= Cardinalityと呼ばれる）を見積もることになる為、非常に重要なインプット情報で高い精度が求められます。
各統計情報を確認するディクショナリ・ビューを整理しておきます。表統計は「DBA/ALL/USER_TAB_STATISTICS」、列統計は「DBA/ALL/USER _TAB_COL_STATISTICS」、索引統計は「USER_IND_STATISTICS」の各ディクショナリ・ビューで確認することができます。
では、次に統計情報を収集する時間を短縮する目的で、サンプリングするレコードの割合を小さい値に指定する演習を試してみましょう。
■ 4. 演習1と同様に、サンプリングするレコードの割合を10%に指定し、オプティマイザ統計情報収集の実行時間を測定して下さい。
sqlplus TRY/TRY SQL> set timing on exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TRY', tabname => 'TBL1', estimate_percent => 10); 経過: 00:00:35.32 ※実行時間は、検証環境のH/W構成やデータベース構成に依存する点にご注意ください
いかがですか？予想通り、実行時間が非常に短縮されたことが確認できたかと思います。サンプリング100%時の実行時間（2分53秒 = 173秒）と比較すると1/5まで高速化することが確認出来ました。これで、「限られたメンテナンス時間内でオプティマイザ統計情報御収集できるぞ、バンザーイ!! 」とは行きませんよね。繰り返しになりますが、統計情報は、その精度が肝になりますから。。。
オプティマイザ統計情報の収集においてDBAに求められることは、「統計情報としての精度（正確性）」と「統計収集に要する時間の短縮」という2つの相反する要件をバランス良く満たすことであり、「この2つのバランスの良い地点を探る」 = 「サンプリングの割合を調整する」という作業で過去に非常に苦労をされたDBAの方も多くいらっしゃるかと思います。しかし、次の演習問題4でご紹介するOracle Database 11gの画期的な新機能を使用することで、このような作業から解放されることになるのです。
■ 5. 演習1と同様に、サンプリングするレコードの割合を指定せずに、オプティマイザ統計情報収集の実行時間を測定して下さい。
sqlplus TRY/TRY SQL> set timing on exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TRY', tabname => 'TBL1'); 経過: 00:00:37.06 ※実行時間は、検証環境のH/W構成やデータベース構成に依存する点にご注意ください
さて、どのような結果になりましたか？サンプリングするレコードの割合を指定しなかったにも関わらず、サンプリング10%と同じ実行時間でオプティマイザ統計収集が完了してしまっていますね。実は、これがOracle Database 11gの新機能の効果なのです。
estimate_percentパラメータを指定しなかった場合に採用される値（デフォルト値）としては、演習問題1の解説に掲載したマニュアル上の構文から「to_estimate_percent_type(get_param(‘ESTIMATE_PERCENT’))」になると読み取ることができますね。get_paramファンクションは下位互換性を維持する為に保持されているファンクションであり、Oracle Database 11gではget_prefsファンクションの使用が推奨されている点を上手（？）に考慮して頂きまして、次のようなSELECT文を実行することで、スキーマTRYの表TBL1の統計情報を収集する際にestimate_percentパラメータを指定しなかった場合に採用される値を確認することができます。
■ 6. 表領域TBS_BIG_ASSMのエクステントの割り当てサイズを4MBに均一化する設定をして下さい。sqlplus TRY/TRY SQL> select DBMS_STATS.GET_PREFS(pname => 'ESTIMATE_PERCENT', ownname => 'TRY', tabname => 'TBL1') from DUAL; DBMS_STATS.GET_PREFS(PNAME=>'ESTIMATE_PERCENT',OWNNAME=>'TRY',TABNAME=>'TBL1') -------------------------------------------------------------------------------- DBMS_STATS.AUTO_SAMPLE_SIZE
この「DBMS_STATS.AUTO_SAMPLE_SIZE」はOracle Database 10gから登場し、Oracle Databaseの内部で表毎にサンプリング・サイズを自動的に決定する仕組みとして提供していました。これにより、DBAが苦労して最適なサンプリング・サイズを判断していた作業が軽減されたことは確かです。ただし、あくまでサンプリングで一部のレコードを抜粋して統計情報を作り出す仕組みであった為、データの偏りが激しい表においては統計情報の精度に問題が見られるケースが稀にありました。Oracle Database 11gの「DBMS_STATS.AUTO_SAMPLE_SIZE」では新しいハッシュ・ベースのサンプリング・アルゴリズムが採用されることで、「サンプリング100%とほぼ同等の統計情報の精度」と「サンプリング10%の統計情報収集とほぼ同程度の実行時間」を同時に満たす機能へと進化を遂げています。
もちろん、環境により実行時間の改善率は異なりますので、ご注意ください。
今現在、この「新しいハッシュ・ベースのサンプリング・アルゴリズム」とは何者かという公開ドキュメントの存在を確認はできていない為、あまり多くを書くことはできないのですが、Oracle Database 11gの「DBMS_STATS.AUTO_SAMPLE_SIZE」はサンプリングによる一部のレコードの抜粋ではなく全てのレコードにアクセスし、従来よりもCPUコストが低いアルゴリズムで集計を行っているように見えます。ご興味のある方は、第6回でご紹介したV$SQLやSQL監視を利用して、DBMS_STATS.GATHER_TABLE_STATSを実行した際に内部的に実行されるSELECT文を分析してみると非常に面白いと思いますのでチャレンジしてみてください。
そして、ここまでの演習で気付いて頂けていると思いますが、この「新しいハッシュ・ベースのサンプリング・アルゴリズム」が適用されるのは、estimate_percentパラメータを「DBMS_STATS.AUTO_SAMPLE_SIZE」に設定した場合のみであり、演習問題1や3でestimate_percnetパラメータに数値を指定した場合には適用されません。とは言え、下手にestimate_percentパラメータに「10」を指定するよりも、「DBMS_STATS.AUTO_SAMPLE_SIZE」を指定した方が「実行時間は同じだが統計情報の精度が高い」という賢い選択になるということは体感して頂けたと思います。
さて、Oracle Database 11gでのオプティマイザ統計情報の取得の高速化の一例を体験して頂きましたが、いかがでしたでしょうか？もし、周りに統計情報の収集時間がメンテナンス・ウィンドウに収まらないという点で困っているDBAを見つけたら、是非、Oracle Database 11gの「DBMS_STATS.AUTO_SAMPLE_SIZE」を紹介してみてください。今回の演習では、統計情報の精度の比較までは行えませんでしたが、この点については、機械的にレコードをINSERTした検証用の表ではなく、お客様の本番環境の表で試して頂ければ一発で納得できると思っています。「えっ？本番環境で検証？非常識じゃないか？本番の統計情報が置き換わってしまうのでは？」と思われた方、確かに正解です。しかし、それでも私は本番で試します。現在の統計情報はそのままに新しい統計情報を取得し、その比較まで行ってしまいます。それが可能な方法も今後の連載でご紹介していきたいと思っています。もし早急にオプティマイザ統計情報の管理についての技術情報をキャッチアップされたい場合には、次の2つのWhite Paperが非常に参考になるかと思います。
今回もありがとうございました。次回も頑張りますので、よろしくお願いします。