Oracle 11gで改善された統計収集における自動サンプリング機能
ひきつづき、Inside the Oracle Optimizer - Removing the black magicからの翻訳です。
原文:Improvement of AUTO sampling statistics gathering feature in Oracle 11g
— beginning of translation —
Oracle 11gで改善された統計収集における自動サンプリング機能
Oracleのオプティマイザ統計はPL/SQLパッケージのdbms_statsで管理されます。 dbms_statsパッケージには、表やスキーマ、データベースなどの統計を収集するためのプロシージャが用意されています。例えば、gather_table_statisticsプロシージャは表統計を収集するために使用します。このプロシージャにはestimate_percentというパラメータがあり、統計収集時にサンプリング率を指定します。このパラメータの値は0～100に設定できます。例えば、BIGTテーブルに1%のサンプリング率を設定する場合、次のように指定できます:
exec dbms_stats.gather_table_stats(null, 'BIGT', estimate_percent => 1);
しかし、ユーザが適切なサンプリング率を設定するのは、なかなか容易なことではありません。サンプリング率の設定が高すぎるような場合は、統計収集に時間がかかってしまいます。反対にデータに偏りがあってサンプリング率が低すぎる場合、正しい統計が得られません。
このような理由で、Oracleはestimate_percentパラメータに自動サンプル・サイズを導入しました。 例えば、テーブルBIGTの統計は次のように収集できます:
exec dbms_stats.gather_table_stats(null, 'BIGT',
estimate_percent=>dbms_stats.auto_sample_size);
固定サイズではなく自動サンプル・サイズを使用する利点は２つあります。1つ目は、自動サンプル・サイズを指定することにより、システム側で適正なサンプリング率が決められる点です。2つ目は、固定サイズよりも自動サンプル・サイズのほうが柔軟性に優れている点です。サンプリング率を固定値にした場合、ある時点では適正であった値でも、表のデータ配分が変化するにつれて適正な値ではなくなっているかもしれません。自動サンプリング・サイズを使用すれば、データ配分が変化した場合でもOracleがサンプリング・サイズを調整してくれるわけです。
自動サンプル・サイズが設定されると、Oracleは、十分な統計品質が得られるようにサンプル・サイズを決定します。しかしながら、データに極端な偏りがある場合は上手く機能しません。そしてOracle11gでは、自動サンプル・サイズが設定されている時の動作が改善されました。まず最初に、自動サンプリングは決定論的な統計を生成します。 2番目はより重要な点ですが、自動サンプリングは100%のサンプリングとほぼ同じぐらい正確な統計を生成し、かつ、より短時間で処理が行われるということです。 そこで、これらのメリットを検証するために、以下で固定サンプル率を使用した場合と、Oracle10gでAUTOサンプルサイズを使用した場合、およびOracle11gで自動サンプル・サイズを使用した場合とについて性能を比較します。
テーブルLineitemの作成には標準のTPC-Dデータジェネレータを使用しました。 テーブルLineitemはおよそ230G、列数は16列で180万件のデータが登録されています。 テーブルLineitemの構成は次の通りです:
| 列名 | 列タイプ |
|---|---|
| l_shipdate | date |
| l_orderkey | number |
| l_discount | number |
| l_extendedprice | number |
| l_suppkey | number |
| l_quantity | number |
| l_returnflag | varchar2 |
| l_partkey | number |
| l_linestatus | varchar2 |
| l_tax | number |
| l_commitdate | date |
| l_receiptdate | date |
| l_shipmode | varchar2 |
| l_linenumber | number |
| l_shipinstruct | varchar2 |
| l_comment | varchar2 |
表１は、テーブルLineitemの統計収集に要した時間をサンプル・サイズ別に示しています。
| サンプリング率 | 経過時間 (秒) |
|---|---|
| サンプリング率1% | 797 |
| サンプリング率100%(Compute) | 18772 |
| Oracle 10gの自動サンプリング | 2935 |
| Oracle 11gの自動サンプリング | 1908 |
表1: テーブルLineitem(230G TPC-D)の推定率別統計収集時間
また、その推定値ごとに収集された統計に対し品質も比較することにします。列統計の中でも、固有の値の数（NDV）は、精度の面で以前から問題になっていました。列内のNDVの正解率は次のように定義します:
正解率 = 1 - (|estimated NDV - actual NDV|)/actual NDV.
正解率の範囲は0%から100%です。 正解率が高ければ高いほど、より収集された統計が正確であることを意味します。 100%のサンプリングは100%の正解率を導き出すためあえて報告しません。それぞれの推定率を使用した際に、少なくとも99.9%未満の統計正解率があるような列に着目します。列の正確率について表２に示します。
| 列名 | Actual NDV | Oracle 11gの自動サンプリング | サンプリング・サイズ1% |
|---|---|---|---|
| orderkey | 450,000,000 | 98.0% | 50% |
| comment | 181,122,127 | 98.60% | 4.60% |
| partkey | 60,000,000 | 99.20% | 98.20% |
| suppkey | 3,000,000 | 99.60% | 99.90% |
| extendedprice | 3,791,320 | 99.60% | 94.30% |
表2: 各推定率で収集したテーブルLineItem のNDV正解率
要約すると、「Oracle11gにおける自動サンプル・サイズ」は、100%のサンプリングよりも経過時間は10倍短く、しかも同品質の統計(100%に近い正解率)を提供するということになります。
— end of translation —