SQL 文を実行するには実行計画が必要です。最終的に選択される実行計画は考えられる多数の実行計画の中から通常最もコストが低い案が採用されます。コスト算出のために使用される情報が「オプティマイザ統計」です。
高頻度自動オプティマイザ統計収集
Oracle Database 10g 以降、オプティマイザ統計は自動的に取得されるようになりました。統計情報が失効したテーブルおよび統計情報が未取得のテーブルに対してデフォルトでは毎日一回夜間に実行されます。下記の例は自動メンテナンス・タスクの有効性を確認すしています。自動統計情報ジョブは「auto optimizer stats collection」です。
SQL> SELECT client_name, status FROM dba_autotask_client;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
sql tuning advisor ENABLED
auto optimizer stats collection ENABLED
auto space advisor ENABLED
アプリケーションによっては短い時間に大幅にデータ量や質が変化するテーブルが作られることがあります。これらのテーブルについては統計情報が実態と相違が出るため、好ましくない実行計画が作成される可能性が高くなります。統計情報が存在しないテーブルに対してはダイナミック・サンプリング機能により、少量のサンプルによる統計情報が取得される可能性もあります。
SQL> SHOW PARAMETER dynamic
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
Oracle Database 19c ではより頻度の高い統計情報取得ジョブが実行できるようになりました。本機能は Exadata システム専用の機能になります。
設定確認
高頻度自動オプティマイザ統計収集機能はデフォルトでは無効になっています。有効にするには以下のGLOBAL統計プリファレンスを変更します。マニュアルはこちらです。
| プリファレンス | 説明 | デフォルト値 |
|---|---|---|
| AUTO_TASK_STATUS | 高頻度自動オプティマイザ統計収集の有効化 | OFF |
| AUTO_TASK_MAX_RUN_TIME | 統計収集の実行の最大実行時間（秒） | 3600 |
| AUTO_TASK_INTERVAL | 統計収集の実行間隔（秒） | 900 |
SQL> SELECT DBMS_STATS.GET_PREFS('AUTO_TASK_STATUS') FROM DUAL;
DBMS_STATS.GET_PREFS('AUTO_TASK_STATUS')
--------------------------------------------------------------------------------
OFF
SQL> SELECT DBMS_STATS.GET_PREFS('AUTO_TASK_MAX_RUN_TIME') FROM DUAL;
DBMS_STATS.GET_PREFS('AUTO_TASK_MAX_RUN_TIME')
--------------------------------------------------------------------------------
3600
SQL> SELECT DBMS_STATS.GET_PREFS('AUTO_TASK_INTERVAL') FROM DUAL;
DBMS_STATS.GET_PREFS('AUTO_TASK_INTERVAL')
--------------------------------------------------------------------------------
900
有効化
GLOBAL統計プリファレンス「AUTO_TASK_STATUS」を「ON」に指定することで高頻度自動オプティマイザ統計収集機能が有効になります。
SQL> EXECUTE DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_STATUS', 'ON');
PL/SQLプロシージャが正常に完了しました。
SQL> SELECT DBMS_STATS.GET_PREFS('AUTO_TASK_STATUS') FROM DUAL;
DBMS_STATS.GET_PREFS('AUTO_TASK_STATUS')
--------------------------------------------------------------------
ON
サンプル用のテーブルを作成して15分待ち、統計情報の取得時刻を確認します。
SQL> CREATE TABLE stats1(c1 NUMBER, c2 VARCHAR2(10));
表が作成されました。
SQL> INSERT INTO stats1 SELECT LEVEL c1, 'data1' c2 FROM DUAL CONNECT BY LEVEL <= 1000000;
1000000行が作成されました。
SQL> COMMIT;
コミットが完了しました。
SQL> SELECT table_name, TO_char(last_analyzed, 'HH24:MI:SS') last_analyzed FROM user_tables WHERE table_name='STATS1';
TABLE_NAME LAST_ANA
------------------------------ --------
STATS1
-- 15分待つ
SQL> SELECT table_name, TO_char(last_analyzed, 'HH24:MI:SS') last_analyzed FROM user_tables WHERE table_name='STATS1';
TABLE_NAME LAST_ANA
------------------------------ --------
STATS1 09:51:22
非 Exadata 環境では「ORA-40216: 機能がサポートされていません」エラーが発生します。
SQL> EXECUTE DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_STATUS','ON');
BEGIN DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_STATUS','ON'); END;
*
行1でエラーが発生しました。:
ORA-40216: 機能がサポートされていません ORA-06512:
"SYS.DBMS_SYS_ERROR", 行79
ORA-06512: "SYS.DBMS_STATS_INTERNAL", 行25430
ORA-06512: "SYS.DBMS_STATS_INTERNAL", 行11707
ORA-06512: "SYS.DBMS_STATS", 行52551
ORA-06512: "SYS.DBMS_STATS", 行52794
ORA-06512: 行1