Oracle Database 12c Release 2で追加されたStatistics Advisorの説明です。
オプティマイザ統計
Oracle Databaseが実行計画を作成する際に参考にする情報がオプティマイザ統計です。オプティマイザ統計にはテーブルやインデックスに格納されているデータの量、種類、分散に加えて、データベースが稼働するホストのCPU性能やのI/O性能等様々な情報があります。
オプティマイザ統計の多くはテーブルやインデックスからサンプリングにより取得されているため、リアルタイムな情報ではありません。このため短時間にデータ量が大きく変化するテーブルでは実データと統計情報が大きく異なるケースが発生します。
実データと統計情報のかい離は、望ましくない実行計画作成の大きな原因にもなります。しかし、統計情報の取得を頻繁に行うと今度は統計情報取得処理がシステムの負荷になります。このように統計情報の取得処理の調整はOracle Datbase DBAが頭を悩ませる運用の一つです。
オプティマイザ統計アドバイザ
Oracle Database 12cにはDBAの悩みを解消するためにオプティマイザ統計アドバイザを搭載しました。これは２４個のルールを元にオプティマイザ統計の取得方法をベスト・プラクティスとして提供します。
オプティマイザ統計アドバイザを実行すると、現状のデータベースを分析して推奨される変更点をレポートしてくれます。アドバイザ作成のために、DBMS_STATSパッケージに以下のファンクションが追加されました。
- CREATE_ADVISOR_TASKファンクション = アドバイザのタスクを作成します
- EXECUTE_ADVISOR_TASKファンクション = アドバイザのタスクを実行します
- REPORT_ADVISOR_TASKファンクション = レポートを作成します。
- DROP_ADVISOR_TASKプロシージャ = アドバイザのタスクを削除します
- CONFGURE_ADVISOR_* ファンクション = アドバイザの実行対象を変更します（複数あります）
- IMPLEMENT_ADVISOR_TASKファンクション = 推奨事項をそのまま実装します
- RESET_ADVISOR_TASKファンクション = アドバイザのタスクをリセットします
- CANCEL_ADVISOR_TASKプロシージャ = アドバイザの実行をキャンセルします
- SCRIPT_ADVISOR_TASKファンクション = 推奨事項をスクリプト化します
- 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.
--------------------------------------------------------------------------------