Oracle Database 12c Release 2 のオプティマイザ統計アドバイザーを試す

Oracle Database 12c Release 2で追加されたStatistics Advisorの説明です。

# オプティマイザ統計
Oracle Databaseが実行計画を作成する際に参考にする情報がオプティマイザ統計です。オプティマイザ統計にはテーブルやインデックスに格納されているデータの量、種類、分散に加えて、データベースが稼働するホストのCPU性能やのI/O性能等様々な情報があります。
オプティマイザ統計の多くはテーブルやインデックスからサンプリングにより取得されているため、リアルタイムな情報ではありません。このため短時間にデータ量が大きく変化するテーブルでは実データと統計情報が大きく異なるケースが発生します。
実データと統計情報のかい離は、望ましくない実行計画作成の大きな原因にもなります。しかし、統計情報の取得を頻繁に行うと今度は統計情報取得処理がシステムの負荷になります。このように統計情報の取得処理の調整はOracle Datbase DBAが頭を悩ませる運用の一つです。

# オプティマイザ統計アドバイザ
Oracle Database 12cにはDBAの悩みを解消するためにオプティマイザ統計アドバイザを搭載しました。これは２４個のルールを元にオプティマイザ統計の取得方法をベスト・プラクティスとして提供します。
オプティマイザ統計アドバイザを実行すると、現状のデータベースを分析して推奨される変更点をレポートしてくれます。アドバイザ作成のために、DBMS_STATSパッケージに以下のファンクションが追加されました。

1. CREATE_ADVISOR_TASKファンクション = アドバイザのタスクを作成します
2. EXECUTE_ADVISOR_TASKファンクション = アドバイザのタスクを実行します
3. REPORT_ADVISOR_TASKファンクション = レポートを作成します。
4. DROP_ADVISOR_TASKプロシージャ = アドバイザのタスクを削除します
5. CONFGURE_ADVISOR_* ファンクション = アドバイザの実行対象を変更します（複数あります）
6. IMPLEMENT_ADVISOR_TASKファンクション = 推奨事項をそのまま実装します
7. RESET_ADVISOR_TASKファンクション = アドバイザのタスクをリセットします
8. CANCEL_ADVISOR_TASKプロシージャ = アドバイザの実行をキャンセルします
9. SCRIPT_ADVISOR_TASKファンクション = 推奨事項をスクリプト化します
10. SET_ADVISOR_TASK_PARAMETERプロシージャ = パラメーターを指定します

ベストプラクティスとなる２４個のルールはV$STATS_ADVISOR_RULESビューに格納されています。

```
SQL> SELECT name, description FROM V$STATS_ADVISOR_RULES ;

NAME                            DESCRIPTION
------------------------------- -----------------------------------------------------------------------------
UseAutoJob                      Use Auto Job for Statistics Collection
CompleteAutoJob                 Auto Statistics Gather Job should complete successfully
MaintainStatsHistory            Maintain Statistics History
UseConcurrent                   Use Concurrent preference for Statistics Collection
UseDefaultPreference            Use Default Preference for Stats Collection
TurnOnSQLPlanDirective          SQL Plan Directives should not be disabled
AvoidSetProcedures              Avoid Set Statistics Procedures
UseDefaultParams                Use Default Parameters in Statistics Collection Procedures
UseGatherSchemaStats            Use gather_schema_stats procedure
AvoidInefficientStatsOprSeq     Avoid inefficient statistics operation sequences
AvoidUnnecessaryStatsCollection Avoid unnecessary statistics collection
AvoidStaleStats                 Avoid objects with stale or no statistics
GatherStatsAfterBulkDML         Do not gather statistics right before bulk DML
LockVolatileTable               Statistics for objects with volatile data should be locked
UnlockNonVolatileTable          Statistics for objects with non-volatile should not be locked
MaintainStatsConsistency        Statistics of dependent objects should be consistent
AvoidDropRecreate               Avoid drop and recreate object seqauences
UseIncremental                  Statistics should be maintained incrementally when it is beneficial
NotUseIncremental               Statistics should not be maintained incrementally when it is not beneficial
AvoidOutOfRange                 Avoid Out of Range Histogram endpoints
UseAutoDegree                   Use Auto Degree for statistics collection
UseDefaultObjectPreference      Use Default Object Preference for statistics collection
AvoidAnalyzeTable               Avoid using analyze table commands for statistics collection

24 rows selected.
```

# アドバイザの実行
最も簡単にアドバイザを実行する方法について説明します。３つのファンクションCREATE_ADVISOR_TASK / EXECUTE_ADVISOR_TASK / REPORT_ADVISOR_TASKを実行するとデフォルト設定でアドバイザを実行できます。

以下はアドバイザを実行するPL/SQLプログラム例です。

```
SET LONG 1000000
SET LONGCHUNKSIZE 100000 
SET SERVEROUTPUT ON
SET LINE 300
SET PAGES 1000

DECLARE
 task_name VARCHAR2(128) := 'ADVISOR_TASK_1' ;
 exec_name VARCHAR2(128) := NULL ;
 report CLOB := NULL ;
 script CLOB := NULL ;
BEGIN
 task_name := DBMS_STATS.CREATE_ADVISOR_TASK(task_name);
 exec_name := DBMS_STATS.EXECUTE_ADVISOR_TASK(task_name);
 report := DBMS_STATS.REPORT_ADVISOR_TASK(task_name);
 DBMS_OUTPUT.PUT_LINE(report);
END;
/
```

PL/SQLプログラム実行結果の例です

```
ENERAL INFORMATION
-------------------------------------------------------------------------------

 Task Name       : ADVISOR_TASK_1
 Execution Name  : EXEC_545
 Created         : 08-25-17 02:21:06
 Last Modified   : 08-25-17 02:21:18

-------------------------------------------------------------------------------
SUMMARY
-------------------------------------------------------------------------------
 For execution EXEC_545 of task ADVISOR_TASK_1, the Statistics Advisor has 8
 finding(s). The findings are related to the
following rules: USECONCURRENT,
 USEGATHERSCHEMASTATS, AVOIDUNNECESSARYSTATSCOLLECTION, AVOIDSTALESTATS,
 MAINTAINSTATSCONSISTENCY, AVOIDDROPRECREATE, USEINCREMENTAL. Please refer to
 the finding section for detailed information.

-------------------------------------------------------------------------------
FINDINGS
-------------------------------------------------------------------------------
 Rule Name:         UseConcurrent
 Rule Description:  Use Concurrent preference for
Statistics Collection
 Finding:  The CONCURRENT preference is not used.

 Recommendation:  Set the CONCURRENT preference.
 Example:

dbms_stats.set_global_prefs('CONCURRENT', 'ALL');
 Rationale:  The system's condition satisfies the use of concurrent statistics
             gathering. Using CONCURRENT increases the efficiency of statistics
             gathering.

----------------------------------------------------
 Rule Name:         UseGatherSchemaStats
 Rule Description:  Use gather_schema_stats procedure
 Finding:  There are 2 uses of GATHER_TABLE_STATS.

Operation:
 gather_table_stats(ownname=>'TPCD', tabname=>'ORDERS2');
 gather_table_stats(ownname=>'SCOTT', tabname=>'PART_A1');

 Recommendation:  Use GATHER_SCHEMA_STATS instead of GATHER_TABLE_STATS.
 Example:

 -- Gather statistics for 'SH' schema:
 BEGIN dbms_stats.gather_schema_stats('SH'); END;
 Rationale:  GATHER_SCHEMA_STATS has more options available, including checking
             for staleness and gathering statistics
concurrently. Also it is
             more maintainable for new tables added to the schema. If you only
             want to gather statistics for certain tables in the schema,
             specify them in the obj_filter_list parameter of

GATHER_SCHEMA_STATS.
----------------------------------------------------
 Rule Name:         AvoidUnnecessaryStatsCollection
 Rule Description:  Avoid unnecessary statistics collection
 Finding:
Statistics are being gathered on 1 object(s) when they are not
           stale.
 Schema:
 TPCD
 Objects:
 PART

 Recommendation:  GATHER_SCHEMA_STATS with GATHER_STALE option can be used to

gather statistics only when the objects are stale.
 Rationale:  Gathering statistics when statistics are not stale wastes
             resources including the resources needed to collect statistics and
             the space required to save the statistics and history.

----------------------------------------------------
 Rule Name:         AvoidStaleStats
 Rule Description:  Avoid objects with stale or no statistics
 Finding:  There are 3 object(s) with stale statistics.

Schema:
 AUDSYS
 Objects:
 AUD$UNIFIED
 Schema:
 CTXSYS
 Objects:
 DR$DBO
 Schema:
 DBSNMP
 Objects:
 BSLN_TIMEGROUPS

 Recommendation:  Regather statistics on objects with stale statistics.

Example:
 -- Gathering statistics for tables with stale or no statistics in schema, SH:
 exec dbms_stats.gather_schema_stats('SH', options => 'GATHER AUTO')
 Rationale:  Stale statistics or no statistics will result
in bad plans.
----------------------------------------------------
 Rule Name:         MaintainStatsConsistency
 Rule Description:  Statistics of dependent objects should be consistent
 Finding:  There are 1 object(s) which have inconsistent
statistics for number
           of distinct values with the columns in their referenced tables.
 Schema:
 TPCD
 Objects:
 LINEITEM
 PARTSUPP

 Recommendation:  Recollect statistics for these tables with referential
                  constraints at the same time.

 Example:
 -- Gathering statistics for tables with stale or no statistics in schema, SH:
 exec dbms_stats.gather_schema_stats('SH', options => 'GATHER AUTO')
 Rationale:  Statistics should be consistent across
columns of tables with
             Primary Key-Foreign Key referential constraints to get optimal
             execution plans.
 Finding:  There are 1 object(s) which have inconsistent statistics for column
           maximum values
with the columns in their referenced tables.
 Schema:
 TPCD
 Objects:
 LINEITEM
 PARTSUPP

 Recommendation:  Recollect statistics for these tables with referential
                  constraints at the same time.
 Example:

 -- Gathering statistics for tables with stale or no statistics in schema, SH:
 exec dbms_stats.gather_schema_stats('SH', options => 'GATHER AUTO')
 Rationale:  Statistics should be consistent across columns of tables with
             Primary Key-Foreign Key referential
constraints to get optimal
             execution plans.
----------------------------------------------------
 Rule Name:         AvoidDropRecreate
 Rule Description:  Avoid drop and recreate object
seqauences
 Finding:  There are 1 table(s) which have been dropped multiple times.

 Recommendation:  Use TRUNCATE TABLE instead of DROP TABLE commands on these
                  tables.
 Example:

 truncate table T1;
 Rationale:  After the table is dropped, we will lose the column usage
             information for that table. That might prevent some statistics
             (for example, histograms) from being
collected in the future.
----------------------------------------------------
 Rule Name:         UseIncremental
 Rule Description:  Statistics should be maintained incrementally when it is
                    beneficial

 Finding:  Incremental option should be used on 3 object(s) for statistics
           gathering.
 Schema:
 SCOTT
 Objects:
 PART_A1
 PART_M1
 PART_M2

 Recommendation:  Use the incremental option for statistics gathering
on these
                  objects.
 Example:
 -- Turn on the incremental option for 'SH.SALES':
 dbms_stats.set_table_prefs('SH', 'SALES',
'INCREMENTAL', 'TRUE');
 Rationale:  Using the incremental option reduces the time it takes to gather
             statistics on partitioned tables. However, it does store
             additional information, which takes up additional space.

--------------------------------------------------------------------------------
```