Oracle オプティマイザ統計めも

●統計情報の収集

exec dbms_stats.gather_XX_stats();

●ヒストグラム収集方法

estimate_percent 推定するパーセント指定。通常、定数DBMS_STATS.AUTO_SAMPLE_SIZEを使用。 0.000001～100まで指定可能。
method_opt 対象列とサンプルサイズ指定 ⇒これにてヒストグラム作成が決まるのでは

FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [column_clause] [size_clause]

size_clauseは、size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}のように定義される。
column_clauseは、column_clause := column_name | extension name | extensionのように定義される。

EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','CUSTOMERS',METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY FOR COLUMNS (CUST_STATE_PROVINCE,COUNTRY_ID) SIZE SKEWONLY'); 

●システム統計

exec dbms_stats.gather_system_stats(); 
exec dbms_stats.gather_system_stats(gathering_mode=>'INTERVAL',interval=>3600,statown=>'SYSTEM');
select * from aux_stats$;

●プリファレンス

統計収集後、即自動的に公開するかどうか(TRUEは公開。FALSEは保留中)

SELECT DBMS_STATS.GET_PREFS('PUBLISH') PUBLISH FROM DUAL;

公開された統計

USER_TAB_STATISTICS、USER_IND_STATISTICS

保留中の統計

USER_TAB_PENDING_STATS、USER_IND_PENDING_STATS

PUBLISH設定は、スキーマ・レベルまたは表レベルで変更可。

EXEC DBMS_STATS.SET_TABLE_PREFS('SH', 'CUSTOMERS', 'PUBLISH', 'false');

customersの統計収集後、その統計は自動的に公開されない。
かわりに、新規収集された統計はUSER_TAB_PENDING_STATSに格納。

デフォルトでは、オプティマイザはDDに格納されている公開済の統計を使用する。
保留中の統計をオプティマイザで使用する場合は、以下設定後、ワークロードを実行する。

-- デフォルトはFALSE、TRUEの場合は、保留中の統計を使用

ALTER SESSION SET OPTIMIZER_USE_PENDING_STATISTICS = TRUE;

保留中の統計が有効である場合、次の文で公開。

EXEC DBMS_STATS.PUBLISH_PENDING_STATS(null, null);

特定の保留中DBオブジェクトの統計を公開する場合。

EXEC DBMS_STATS.PUBLISH_PENDING_STATS('SH','CUSTOMERS');

保留中の統計を公開せずに削除する場合。

EXEC DBMS_STATS.DELETE_PENDING_STATS('SH','CUSTOMERS');

保留中の統計は、以下でエクスポート可能。開発環境でインポートするとテスト可能。
DBMS_STATS.EXPORT_PENDING_STATSファンクションを使用してエクスポートできる。

●実行計画の固定

◆統計情報のロック

DBMS_STATS.LOCK_TABLE_STATS('USER','TABLE');
select owner,table_name,stattype_locked from dba_tab_statistics;

◆統計情報ロック解除

DBMS_STATS.UNLOCK_TABLE_STATS('USER','TABLE');
select owner,table_name,stattype_locked from dba_tab_statistics;

◆その他の方法
ヒント句、outline(SE)、SPM(EE)が紹介されている。

●拡張統計

◆列グループ(マルチカラムインデックスの場合、その列に合わせて作成)

DBMS_STATS.GATHER_TABLE_STATS('SH', 'SALES', method_opt => 'FOR COLUMNS (empno, deptno)'); 

◆作成

DECLARE
cg_name VARCHAR2(30);
BEGIN
cg_name := DBMS_STATS.CREATE_EXTENDED_STATS(null,'customers', 
'(cust_state_province,country_id)');
END;
/

◆列グループの取得

SELECT SYS.DBMS_STATS.SHOW_EXTENDED_STATS_NAME('sh','customers',
'(cust_state_province,country_id)') col_group_name 
FROM DUAL;

COL_GROUP_NAME
---------------------------------
SYS_STU#S#WF25Z#QAHIHE#MOFFMM

◆式の統計(function INDEXに近いような考え方)

DBMS_STATS.GATHER_TABLE_STATS('SH', 'SALES', method_opt => 'FOR COLUMNS (sal+comm)');

EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','CUSTOMERS',METHOD_OPT =>
'FOR ALL COLUMNS SIZE SKEWONLY 
FOR COLUMNS (CUST_STATE_PROVINCE,COUNTRY_ID) SIZE SKEWONLY');

オプティマイザは、等価述語でのみ複数列の統計を使用

◆式の統計の作成
GATHER_TABLE_STATSプロシージャの一部として、式の統計を作成。

EXEC DBMS_STATS.GATHER_TABLE_STATS('sh','customers', method_opt =>
'FOR ALL COLUMNS SIZE SKEWONLY 
FOR COLUMNS (LOWER(cust_state_province)) SIZE SKEWONLY');

 

◆単独で式の統計を作成するには、

SELECT 
DBMS_STATS.CREATE_EXTENDED_STATS(null,'customers','(LOWER(cust_state_province))') 
FROM DUAL;

◆式の統計に関する情報を取得。

SELECT EXTENSION_NAME, EXTENSION FROM USER_STAT_EXTENSIONS WHERE TABLE_NAME='CUSTOMERS';

EXTENSION_NAME EXTENSION
------------------------------- --------------------------------
SYS_STUBPHJSBRKOIK9O2YV3W8HOUE (LOWER("CUST_STATE_PROVINCE" ))

◆個別値の数(NDV)と、ヒストグラムが作成されているかどうかの確認。

SELECT e.EXTENSION col_group, t.NUM_DISTINCT, t.HISTOGRAM
FROM USER_STAT_EXTENSIONS e, USER_TAB_COL_STATISTICS t
WHERE e.EXTENSION_NAME=t.COLUMN_NAME AND t.TABLE_NAME='CUSTOMERS';

COL_GROUP NUM_DISTINCT HISTOGRAM
------------------------------- ------------ ---------
(LOWER("CUST_STATE_PROVINCE" )) 145 FREQUENCY

 

●統計のリストア

◆戻し

DBMS_STATS.RESTORE_XX_STATS

ユーザーが独自に定義した統計はリストア不可。

DBMS_STATSを使用してスキーマ/データベース・レベルで実行された統計操作履歴

DBA_OPTSTAT_OPERATIONS

◆表統計の変更履歴

DBA_TAB_STATS_HISTORY

◆古い統計は、統計履歴の保存設定とシステムの最終分析時刻に基づいて、定期的かつ自動的に消去される。
保存期間構成

DBMS_STATS.ALTER_STATS_HISTORY_RETENTION( 保存期間 )

デフォルト 31日。

保存期間：1～365000
-1: 自動パージで統計履歴を削除しない。
0: 古い統計情報を保存しない。自動パージですべての統計履歴を削除する。
NULL: 統計履歴のリテンションをデフォルト値に変更する。

統計収集にANALYZEが使用された場合、古いバージョンの統計は格納されない。

自動消去は、STATISTICS_LEVEL=TYPICALまたはALL 場合に有効。
自動消去が無効化されている場合、PURGE_STATSを使用して古いバージョンの統計を手動消去要。

◆統計履歴が使用可能な最も古いタイムスタンプを取得

DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY

●統計のエクスポート、インポート

・統計表は、DBMS_STATS.CREATE_STAT_TABLEプロシージャで作成。
・表作成後、DBMS_STATS.EXPORT_*_STATSプロシージャを使用して、DDから統計表に統計をエクスポート可能。
・統計をインポートするには、DBMS_STATS.IMPORT_*_STATSプロシージャを使用。

--統計リストアとインポートまたはエクスポートの相違点
・リストア機能を使用する場合
  統計の古いバージョンをリカバリする場合。たとえば、オプティマイザの動作を前の日付までリストアする場合など。
・データベースで統計履歴の保存および消去を管理する場合。

--EXPORT/IMPORT_*_STATSプロシージャを使用する場合
・複数の統計セットを試験的に使用して値を増減させる場合。
・データベース間で統計を移動する場合。たとえば、本番システムからテスト・システムに統計を移動する場合など。
・既知の統計セットを統計のリストアに必要な保存日数よりも長期間にわたって保存する場合。

●動的統計

オプティマイザが、自動的に動的統計を収集する場面。

統計の欠落：検索対象表に統計がない場合、オプティマイザは最適化の前に対象表の基本的な統計を収集する。

この場合、統計はDBMS_STATSパッケージを使用して収集された統計ほど高品質ではなく、完全でもない。
これは、文のコンパイル時間に及ぼす影響を限定的にするために妥協される。

統計の失効：通常、最後に統計が収集されて以降、表内の10%以上の行が変更されると、統計は失効する。

不十分な統計：オプティマイザが、列間の相関、列データ配分の偏り、式の統計などを考慮に入れずに、述語(フィルタまたは結合)の選択性またはGROUP BY句を見積る場合、統計は常に不十分なものになる可能性がある。

●統計が無い時の処理

統計がない表が検出されると、デフォルトでオプティマイザに必要な統計を動的に収集する。
ただし、リモート表や外部表などの特定のタイプの表に対しては、動的統計を収集しない。

上記の場合および動的統計が無効になっている場合、オプティマイザの統計ではデフォルト値が使用される。

表のデフォルト値
表統計	オプティマイザによって使用されるデフォルト値
カーディナリティ	ブロック数×(ブロック・サイズ - キャッシュ層)÷行の平均の長さ
行の平均長さ	100バイト
ブロック数	100、またはエクステント・マップに基づく実際の値
リモート・カーディナリティ	2000行
リモートの行の平均長さ	100バイト

索引のデフォルト値
索引統計	オプティマイザによって使用されるデフォルト値
レベル	1
リーフ・ブロック	25
リーフ・ブロック/キー	1
データ・ブロック/キー	1
個別キー	100
クラスタ化係数	800

●統計情報系DD

◆統計情報の収集日を確認する
dba_tab_stats_history

◆拡張統計情報の詳細を確認する
dba_tab_stats_history

◆統計情報の取得状況を確認する
dba_tab_statistics
dbatab_col_statistics

●統計情報の停止

set pages 100
col sql_tune_advisor for a8
col window_name for a16
select WINDOW_NAME, AUTOTASK_STATUS,OPTIMIZER_STATS, SEGMENT_ADVISOR, SQL_TUNE_ADVISOR
from DBA_AUTOTASK_WINDOW_CLIENTS;
exec dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection', operation=> NULL, window_name => 'MONDAY_WINDOW');
exec dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection', operation=> NULL, window_name => 'TUESDAY_WINDOW');
exec dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection', operation=> NULL, window_name => 'WEDNESDAY_WINDOW');
exec dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection', operation=> NULL, window_name => 'THURSDAY_WINDOW');
exec dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection', operation=> NULL, window_name => 'FRIDAY_WINDOW');
exec dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection', operation=> NULL, window_name => 'SATURDAY_WINDOW');
exec dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection', operation=> NULL, window_name => 'SUNDAY_WINDOW');
set pages 100
col sql_tune_advisor for a8
col window_name for a16
select WINDOW_NAME, AUTOTASK_STATUS,OPTIMIZER_STATS, SEGMENT_ADVISOR, SQL_TUNE_ADVISOR
from DBA_AUTOTASK_WINDOW_CLIENTS;

●統計情報関係の操作
設定の停止方法：

exec dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection', operation=> NULL, window_name => 'TUESDAY_WINDOW');
exec dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection', operation=> NULL, window_name => 'WEDNESDAY_WINDOW');
exec dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection', operation=> NULL, window_name => 'THURSDAY_WINDOW');
exec dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection', operation=> NULL, window_name => 'FRIDAY_WINDOW');
exec dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection', operation=> NULL, window_name => 'SATURDAY_WINDOW');
exec dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection', operation=> NULL, window_name => 'SUNDAY_WINDOW');

設定のON/OFFの確認

set pages 100
col sql_tune_advisor for a8
col window_name for a16
select WINDOW_NAME, AUTOTASK_STATUS,OPTIMIZER_STATS, SEGMENT_ADVISOR, SQL_TUNE_ADVISOR
from DBA_AUTOTASK_WINDOW_CLIENTS;

各ウィンドウの内容

set pages 100
col window_name for a16
col duration for a20
col repeat_interval for a40
select window_name,repeat_interval,duration from dba_scheduler_windows where window_name like '%_WINDOW';

統計情報収集履歴

set pages 100
col client_name for a10
col window_name for a16
col job_name for a30
col job_status for a10
col job_info for a10
col job_start_time for a35
col window_start_time for a20
col job_duration for a20
col window_duration for a20
select * from dba_autotask_job_history 
where client_name like '%optimizer%'
order by 1,3;

col client_name for a31
select client_name,job_start_time,job_status from dba_autotask_job_history 
where client_name like '%optimizer%'
order by 1,2;