X

A blog about Oracle Technology Network Japan

  • July 27, 2020

津島博士のパフォーマンス講座 第76回 オプティマイザ統計の運用について(3)

Guest Author

津島博士のパフォーマンス講座 Indexページ ▶▶

 


皆さんこんにちは、今年の梅雨はすっきりしない天気の日が多いですが、これが公開される頃には関東でも梅雨が明けていますね。
今回は、オプティマイザ統計の運用の続きとして、いくつかの注意点について取り上げようと思います。後半に、Oracle Database 19c(Oracle19c)からのオプティマイザ統計収集の拡張機能についても説明していますので、参考にしてください。

1. オプティマイザ統計運用のまとめ
オプティマイザ統計は、多くが自動的に収集されるようになり、使用しやすくなっていますが、オプティマイザ統計による性能問題もまだ多いように思います(自動化されたことで、意識していない方が多いのかもしれません)。そのため、効果的な運用のまとめとして、説明できていなかった以下の注意点などについて説明します。

  • ヒストグラムが必要な列
  • ヒストグラム作成の補足
  • 共有カーソルの無効化

(1)ヒストグラムが必要な列
まずは、どのような列で列統計のヒストグラムが必要になるかについて説明します。
ヒストグラムは、フィルター条件(WHERE句の述語)や結合などで使用されている列で、以下のように使用されるものが主な候補になります(作成していない方は再検討してください)。

  • 値の偏りがある列に対する範囲条件(RANGE、LIKE)や等価条件(EQ、EQ_JOIN)

ある値だけ多いまたは少ない列になるので、条件によって件数が異なります(異なる値の数が少ない場合も、値が繰り返し使用され偏りやすくなります)。第5回でも説明しているように、これがヒストグラムの必要な理由として最も分かりやすいので、認識している方も多いと思います。

  • 範囲の偏りがある列に対する範囲条件

ある範囲に偏りがあるため、範囲検索をすると条件によって件数が大きく異なります。よくあるのが、第46回の「文字列の範囲条件」で説明した日付をDATEデータ型以外で行っている場合です。以下の例は、どちらもヒストグラムを作成していませんが、左側がNUMBER型の年月のため見積り行数が正しくありません(これは、下2桁が01~12の値しか存在しないという偏りが発生するためです)。これを認識していないで、性能問題になっている方が多いように思います。

SQL> SELECT COUNT(*) FROM b WHERE id BETWEEN 200810
 2     AND 200903;
-------------------------------------------
| Id  | Operation          | Name | Rows  |
-------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |
|   1 |  SORT AGGREGATE    |      |     1 |
|*  2 |   TABLE ACCESS FULL| B    |   108K|

SQL> SELECT COUNT(*) FROM b WHERE dt BETWEEN
 2  TO_DATE('200810','YYYYMM') AND TO_DATE('200903','YYYYMM');
-------------------------------------------
| Id  | Operation          | Name | Rows  |
-------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |
|   1 |  SORT AGGREGATE    |      |     1 |
|*  2 |   TABLE ACCESS FULL| B    | 57166 |
    

このような列は、オプティマイザの解析時に収集した、結合やフィルター条件などの情報をSYS.COL_USAGE$(列の使用状況)に登録してから、オプティマイザ統計収集(デフォルトのMETHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO')時に、データの偏りを調べて必要かを判断しています(SYS.COL_USAGE$は、第49回の「自動列グループ検出」で説明したDBMS_STATS.REPORT_COL_USAGEファンクションで確認できます)。そのため、このような列を使用しているときは、ヒストグラムを作成しないような設定はしないでしてください(CLOBやLONGデータ型などは、ヒストグラムの対象外です)。

(2)ヒストグラム作成の補足
次に、ヒストグラム作成の補足として、注意点などについて説明します。
ヒストグラムは、通常は自動的に判断して作成するので、あまり気にしていない方が多いと思います。ただし、自動的に作成されないときや最適に作成するための注意点などもあるので、そのようなことを取り上げます。

  • バルク・ロードのオンライン統計収集

第33回で説明したように、Oracle Database 12c(Oracle12c)からバルク・ロードでオンライン統計収集されるようになりましたが、このときヒストグラムは作成されません。そのため、以下のように指定することで(OPTIONS=>'GATHER AUTO')、基本の列統計を再収集せずに、必要なヒストグラムだけ自動作成することができます(ただし、SYS.COL_USAGE$により作成されるので、問合せが実行されていないと作成されません)。

EXEC DBMS_STATS.GATHER_TABLE_STATS('USER', 'SALES2', OPTIONS=>'GATHER AUTO');

オプティマイザ統計収集の後に、以下のSQLでヒストグラムを確認することができます。OPTIONS=>'GATHER AUTO'で作成されると、以下のようにNOTES列がHISTOGRAM_ONLYになります(STATS_ON_LOADは、オンライン統計収集が実行されたことを意味します)。

SQL> SELECT column_name,num_distinct,notes,histogram FROM user_tab_col_statistics WHERE table_name = 'SALES2';

COLUMN_NAME   NUM_DISTINCT NOTES          HISTOGRAM
------------- ------------ -------------- ---------
AMOUN_SOLD             xxx STATS_ON_LOAD  NONE
PROD_ID                xxx HISTOGRAM_ONLY FREQUENCY

 

  • 最大バケット数

第35回で説明したように、Oracle12cからヒストグラムの最大バケット数が2048になりましたが、デフォルトは254のままになっているので、ヒストグラムを自動作成させると最大バケット数まで使用しないので注意してください。異なる値の数(NUM_DISTINCT)が大きく実行計画の見積り行数が正しくないような場合は、バケット数を増やしてヒストグラムを再作成してみてください(METHOD_OPT=>'FOR COLUMNS <列名> SIZE <バケット数>')。

  • 上位頻度ヒストグラムとハイブリッド・ヒストグラム

Oracle12cからの上位頻度ヒストグラムとハイブリッド・ヒストグラムは、ESTIMATE_PERCENTパラメータがDBMS_STATS.AUTO_SAMPLE_SIZE(デフォルト)のときだけ作成されます(AUTO_SAMPLE_SIZE以外では、これまでの高さ調整済ヒストグラムになるので注意してください)。また、AUTO_SAMPLE_SIZE の動作が、Oracle Database 11gから近似値アルゴリズム(Oracle12cからのAPPROX_COUNT_DISTINCT関数と同じ)によるコスト削減により、全表スキャン(100%のサンプル・サイズ)で行うようになり、より正確になっているので、AUTO_SAMPLE_SIZE以外を使用している方は変更を検討してください。

  • ヒストグラムのコピー

テスト環境で作成されたヒストグラムや拡張統計を、同様の表を持つ別データベース(本番データベースなど)に適用したい場合がるかと思います。そのような方のために、別データベースからのコピー方法を紹介します(詳細は、Doc ID 2388953.1を参照してください)。これは、ヒストグラムや拡張統計が存在する表に対して、以下のようなスクリプトを生成するものになります。このようなものを一から作るのは大変なので、参考のために載せておきました。

/* ヒストグラム用のスクリプト */
EXEC dbms_stats.set_table_prefs('<スキーマ名>','<表名>','METHOD_OPT','FOR ALL COLUMNS SIZE 1,FOR COLUMNS <列> SIZE 254');
/* 拡張統計用のスクリプト */
var r VARCHAR2(50)
EXEC :r := dbms_stats.create_extended_stats('<スキーマ名>','<表名>','<拡張統計情報>');

 

(3)共有カーソルの無効化
最後に、第27回の「共有カーソルのINVALID」の補足として、無効化のタイミングについて説明します。
共有カーソルは、オプティマイザ統計を収集すると無効化されますが、第65回の「DDLによるカーソル無効化の削減」で説明したように、大量のハード解析による性能ダウンを避けるために、DBMS_STATS.GATHER_XXX_STATSプロシージャのNO_INVALIDATEパラメータをDBMS_STATS.AUTO_INVALIDATE(デフォルト)にすることで、Oracleが自動的に無効化のタイミングを決めます(DBMS_STATS.AUTO_INVALIDATEの詳細は、Doc ID 2402871.1を参照してください)。OLTPのようなSQLが大量に実行される環境では、このようにしないとハード解析が多発して、パフォーマンス低下が発生してしまいます。この無効化されるタイミングは、デフォルトでは5時間以内のランダムな時刻に設定されるので、即時に共有カーソルを無効化したい場合でもされないため注意してください。そのため、即時に無効化したいときには、NO_INVALIDATEパラメータをFALSEにする必要があります。ただし、パラレル実行の共有カーソルは、ハード解析時間を気にしないような処理と判断して、即時無効化されるようになっているので、気にする必要はありません。

2. Oracle Database 19cの拡張機能
ここでは、Oracle19cからのオプティマイザ統計収集の拡張機能について説明します。
以下の二つの機能が、Oracle19cからオプティマイザ統計収集に拡張され、オプティマイザ統計がより正確になるようになりました。ただし、どちらもExadataだけで使用できる機能になります。

  • リアルタイム統計
  • 高頻度自動オプティマイザ統計収集

(1)リアルタイム統計
これまでは、バルク・ロード(CREATE TABLE AS SELECTやダイレクト・パス・インサート)だけがオンラインでオプティマイザ統計を収集していましたが、Oracle19cのリアルタイム統計で、従来型DMLでもオンラインでオプティマイザ統計が収集されるようになりました。リアルタイム統計は、ごくわずかなオーバーヘッドで高速実行されるように、最悪な実行計画によるパフォーマンス低下を避けるため、最も重要な統計(MIN、MAX、NUM_ROWSなどのすべてのデータを参照する必要がないデータ)だけが収集されます。残りの統計は、手動または自動オプティマイザ統計収集まで延期されます。SQL文が実行されると、以下のように実行計画に出力されます(左側がINSERT文に対してリアルタイム統計が実行された場合、右側がリアルタイム統計を使用する問合せを実行した場合です)。

SQL> INSERT INTO sales2 SELECT FROM sales;

------------------------------------------------
|Id| Operation                        | Name   |
------------------------------------------------
| 0| INSERT STATEMENT                 |        |
| 1|  LOAD TABLE CONVENTIONAL         | SALES2 |
| 2|   OPTIMIZER STATISTICS GATHERING |        |
| 3|    PARTITION RANGE ALL           |        |
| 4|     TABLE ACCESS FULL            | SALES  |
------------------------------------------------


SQL> SELECT COUNT(*) FROM sales2 WHERE quantity_sold > 50;

-----------------------------------
|Id| Operation           | Name   |
-----------------------------------
| 0| SELECT STATEMENT    |        |
| 1|  SORT AGGREGATE     |        | 
|*2|   TABLE ACCESS FULL | SALES2 |
-----------------------------------
Note
-----
   - dynamic statistics used: stats for conventional DML
    
 

 

 

明示的に統計を収集しないようにするには、以下のようにNO_GATHER_OPTIMIZER_STATISTICSヒントを指定する必要があるので、オプティマイザ統計を固定して運用しているようなシステムは注意してください。

SQL> INSERT /*+ NO_GATHER_OPTIMIZER_STATISTICS */ INTO sales2 SELECT * FROM sales;

Oracle19cからALL/DBA/USER_TAB_STATISTICSビューとALL/DBA/USER_TAB_COL_STATISTICSビューにNOTES列が追加され、リアルタイム統計が実行された表や列のNOTES列に、STATS_ON_CONVENTIONAL_DMLが設定されて確認できるようになっています(以下のように、通常のオプティマイザ統計とリアルタイム統計の二つが存在しますが、パーティション・レベル統計はサポートされないので一つだけになります)。

SQL> SELECT NVL(partition_name, 'GLOBAL') partition_name, num_rows, blocks, notes 
2    FROM user_tab_statistics WHERE table_name = 'SALES2’ORDER BY 1, 4;

PARTITION_NAM   NUM_ROWS     BLOCKS NOTES
------------- ---------- ---------- -------------------------
GLOBAL           1837686       3315 STATS_ON_CONVENTIONAL_DML
GLOBAL            918843       3315
SALES_2015             0          0
SALES_2016             0          0
…

バルク・ロードのオンライン統計収集もAutonomous Databaseと同じ(表が空でなくても統計収集される)ように拡張される予定でしたが、Oracle19cでは機能拡張されませんでした。

(2)高頻度自動オプティマイザ統計収集
Oracle19cから自動オプティマイザ統計収集が、メンテナンス・ウィンドウ以外でも実行できるようになりました。以下のように、Oracle19cからのAUTO_TASK_STATUSパラメータをONにすることで、メンテナンス・ウィンドウ以外でも自動的に実行できるようになります(デフォルトはOFFです)。AUTO_TASK_INTERVALパラメータ(実行間隔)とAUTO_TASK_MAX_RUN_TIMEパラメータ(最大実行時間)も同じように設定できます

exec DBMS_SPM. DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_STATUS', 'ON');

以下のSQLで、AUTO_TASK_STATUSの現在の設定を確認することができます。このパラメータを有効にすると、以下のようにデフォルトで15分ごとに1時間以内で実行されるようになります。

SQL> SELECT dbms_stats.get_prefs('AUTO_TASK_STATUS') AUTO_TASK_STATUS,
  2         dbms_stats.get_prefs('AUTO_TASK_INTERVAL') AUTO_TASK_INTERVAL,
  3         dbms_stats.get_prefs('AUTO_TASK_MAX_RUN_TIME') AUTO_TASK_MAX_RUN_TIME
  4    FROM dual;

AUTO_TASK_STATUS AUTO_TASK_INTERVAL AUTO_TASK_MAX_RUN_TIME
---------------- ------------------ ----------------------
ON               900                3600

タスクの名前は、高頻度タスクがHIGH_FREQ_AUTO_TASK、標準自動タスクがAUTO_TASKになります。以下のように、DBA_AUTO_STAT_EXECUTIONSビューで自動オプティマイザ統計収集タスクのステータスを確認することができます(この出力では、高頻度収集を有効にして、最大実行時間を3分、タスクの実行間隔を4分に設定しています)。

SQL> SELECT opid, origin, status, TO_CHAR(start_time,'DD/MM HH24:MI:SS' ) AS begin_time,
  2         TO_CHAR(end_time,'DD/MM HH24:MI:SS') AS end_time, completed, failed,
  3         timed_out AS timeout, in_progress AS inprog
4    FROM dba_auto_stat_executions ORDER BY opid;

OPID ORIGIN               STATUS   BEGIN_TIME     END_TIME       COMP FAIL TIMEO INPRO
---- -------------------- -------- -------------- -------------- ---- ---- ----- -----
790  HIGH_FREQ_AUTO_TASK  COMPLETE 03/10 14:54:02 03/10 14:54:35  338    3     0     0
793  HIGH_FREQ_AUTO_TASK  COMPLETE 03/10 14:58:11 03/10 14:58:45  193    3     0     0
794  AUTO_TASK            COMPLETE 03/10 15:00:02 03/10 15:00:20   52    3     0     0
...

このように、いろいろ機能拡張されて、より正確なオプティマイザ統計収集ができるようになっているので、実行計画が問題になっている方などは使用を検討してみてください。

3. おわりに
今回は、オプティマイザ統計の運用の続きについて説明しましたが、少しは参考になりましたでしょうか。これからも頑張りますのでよろしくお願いします。
それでは、次回まで、ごきげんよう。    


ページトップへ戻る▲ 

 

津島博士のパフォーマンス講座 Indexページ ▶▶

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.