オプティマイザ統計にはいくつかの種類があり、かつそれぞれ適切なタイミングで取得する必要があります。基本的にはOracle任せでよいのですが、状況によっては管理者手動でプロシージャを適宜実行し統計情報の取得や削除を行う必要があります。以下に各種プロシージャをまとめました。
| No. | プロシージャ名 DBMS_STATS.xxxxx |
機能 |
| 1 | GATHER_SYSTEM_STATS | システムの統計情報の取得 |
| DELETE_SYSTEM_STATS | システムの統計情報の削除 | |
| 2 | GATHER_DICTIONARY_STATS | ディクショナリのスキーマ(SYS、SYSTEMおよびRDBMSコンポーネントのスキーマ)に関する統計情報の取得 |
| DELETE_DICTIONARY_STATS | ディクショナリのスキーマに関する統計情報の削除 | |
| 3 | GATHER_FIXED_OBJECTS_STATS | 全ての固定オブジェクト(動的パフォーマンス表)に関する統計情報の取得 |
| DELETE_FIXED_OBJECTS_STATS | 全ての固定オブジェクトに関する統計情報の削除 | |
| 4 | GATHER_DATABASE_STATS | データベース内のすべてのオブジェクトに関する統計情報取得（システム統計は除く） |
| DELETE_DATABASE_STATS | システム統計情報を除くオプティマイザ統計全てを削除 | |
| 5 | GATHER_SCHEMA_STATS | スキーマ内全てのオブジェクトに関する統計情報を取得 |
| DELETE_SCHEMA_STATS | スキーマ内全てのオブジェクトに関する統計情報を削除 | |
| 6 | GATHER_TABLE_STATS | 表と列(および索引)の統計情報を取得する。パラメータの指定によって取得内容が異なる。 |
| DELETE_TABLE_STATS | 表と列の統計情報を削除する | |
| 8 | GATHER_INDEX_STATS | 索引の統計情報の収集 |
| DELETE_INDEX_STATS | 索引の統計情報の削除 |
ディクショナリ統計の取得、削除、取得状況確認
GATHER_DICTIONARY_STATSでディクショナリ統計を取得する
以下全て（表統計、列統計、索引統計）Oracle任せで取得する事になります。
begin DBMS_STATS.GATHER_DICTIONARY_STATS (); end;
DELETE_DICTIONARY_STATSでディクショナリ統計を削除する
こんな事めったに実施する事ないと思いますが、削除も意外に時間がかかります。
begin DBMS_STATS.DELETE_DICTIONARY_STATS (); end;
DBA_TAB_STATISTICSを参照しディクショナリ統計の取得状況を確認する
ディクショナリ統計とは基本SYS,SYSTEMが保有しているテーブルなので一般的なテーブルと同様DBA_TAB_STATISTICSで統計情報の取得状況が確認できます。
select t.OWNER,t.TABLE_NAME,t.OBJECT_TYPE,t.NUM_ROWS,t.LAST_ANALYZED,t.STALE_STATS
from DBA_TAB_STATISTICS t where t.OWNER in ('SYS','SYSTEM')
order by t.OWNER,t.TABLE_NAME
;
固定オブジェクト統計の取得、削除、取得状況確認
GATHER_FIXED_OBJECTS_STATSで固定オブジェクト統計を取得する
begin dbms_stats.gather_fixed_objects_stats; end;
DELETE_FIXED_OBJECTS_STATSで固定オブジェクト統計を削除する
begin dbms_stats.delete_fixed_objects_stats; end;
DBA_TAB_STATISTICSを参照し固定オブジェクト統計の取得状況を確認する
固定オブジェクトも所詮テーブルなので一般的なテーブルと同様DBA_TAB_STATISTICSで統計情報の取得状況が確認できます。ただしDBA_TABLESやDBA_OBJECTSディクショナリビューには含まれていません。
select f.NAME,f.OBJECT_ID,f.TYPE,f.TABLE_NUM ,t.OWNER,t.TABLE_NAME,t.OBJECT_TYPE,t.NUM_ROWS,t.LAST_ANALYZED,t.STALE_STATS from V$FIXED_TABLE f inner join DBA_TAB_STATISTICS t on f.NAME = t.TABLE_NAME where f.TYPE='TABLE' order by 1 ;
索引統計の取得、削除、取得状況確認
GATHER_INDEX_STATSで索引統計を取得する
begin DBMS_STATS.GATHER_INDEX_STATS ( ownname => 'SOONI' ,indname => 'TBL26_IDX01' ); end;
DELETE_INDEX_STATSで索引統計を削除する
begin DBMS_STATS.DELETE_INDEX_STATS ( ownname => 'SOONI' ,indname => 'TBL26_IDX01' ); end;
DBA_IND_STATISTICSを参照し索引統計の取得状況を確認する
select t.OWNER,t.INDEX_NAME,t.TABLE_OWNER,t.TABLE_NAME,t.NUM_ROWS,t.LAST_ANALYZED,t.STALE_STATS from DBA_IND_STATISTICS t where t.OWNER ='SOONI' and t.TABLE_NAME='TBL26' ;
set lin 200 col OWNER for a10 col INDEX_NAME for a20 col TABLE_OWNER for a20 col TABLE_NAME for a12 col STALE_STATS for a12
SQL> set lin 200
SQL> col OWNER for a10
SQL> col INDEX_NAME for a20
SQL> col TABLE_OWNER for a20
SQL> col TABLE_NAME for a12
SQL> col STALE_STATS for a12
SQL> select
2 t.OWNER,t.INDEX_NAME,t.TABLE_OWNER,t.TABLE_NAME,t.NUM_ROWS,t.LAST_ANALYZED,t.STALE_STATS
3 from DBA_IND_STATISTICS t where t.OWNER ='SOONI'
4 and t.TABLE_NAME='TBL26'
5 ;
OWNER INDEX_NAME TABLE_OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED STALE_STATS
---------- -------------------- -------------------- ------------ ---------- ------------------- ------------
SOONI TBL26_IDX01 SOONI TBL26 1755483 2021/12/30 23:41:22 NO
SOONI IDX_TBL26_01 SOONI TBL26 1682114 2021/12/29 22:00:31 NO
SOONI PK_TBL26 SOONI TBL26 1739995 2021/12/29 22:00:33 NO
SQL>システム統計情報 の取得、削除、取得状況確認
システムの統計情報を収集します。STARTからSTOP指定の間統計情報が取得されます。ある程度負荷のかかっているタイミングが良いとされています。収集状況は sys.auX_stats$ にて確認できます。
DBMS_STATS.GATHER_SYSTEM_STATSでシステム統計を取得する
begin dbms_stats.gather_system_stats(gathering_mode => 'START'); end;
begin dbms_stats.gather_system_stats(gathering_mode => 'STOP'); end;
DELETE_SYSTEM_STATSでシステム統計を削除する
begin dbms_stats.delete_system_stats(); end;
sys.aux_stats$ を参照しシステム統計情報の取得状況確認
set lin 100
set pages 100
COLUMN SNAME FORMAT A20
COLUMN PNAME FORMAT A14
COLUMN PVAL2 FORMAT A24
SQL> select * from sys.aux_stats$ order by sname,pname
2 ;
SNAME PNAME PVAL1 PVAL2
------------------------ ------------------------ ---------- ------------------------
SYSSTATS_INFO DSTART 07-07-2014 06:53
SYSSTATS_INFO DSTOP 07-07-2014 06:53
SYSSTATS_INFO FLAGS 1
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN CPUSPEEDNW 3308.9701
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN SLAVETHR
SYSSTATS_MAIN SREADTIM
13行が選択されました
-- 取得中は以下の通り
QL> select * from sys.auX_stats$ order by sname,pname
2 ;
SNAME PNAME PVAL1 PVAL2
------------------------ ------------------------ ---------- ------------------------
SYSSTATS_INFO DSTART 12-30-2021 17:27
SYSSTATS_INFO DSTOP 12-30-2021 17:27
SYSSTATS_INFO FLAGS 1
SYSSTATS_INFO STATUS MANUALGATHERING
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN CPUSPEEDNW 3308.9701
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN SLAVETHR
SYSSTATS_MAIN SREADTIM
SYSSTATS_TEMP CACHE_JOB 8314
SYSSTATS_TEMP CPUCYCLES 794808
SYSSTATS_TEMP CPUTIM 983233
SYSSTATS_TEMP JOB 0
SYSSTATS_TEMP MBLKRDS 5438
SYSSTATS_TEMP MBLKRDTIM 5717.2
SYSSTATS_TEMP MBRTOTAL 126939
SYSSTATS_TEMP SBLKRDS 32229
SYSSTATS_TEMP SBLKRDTIM 22529.935
22行が選択されました。
SQL>
-- 取得完了後
SQL> /
SNAME PNAME PVAL1 PVAL2
------------------------ ------------------------ ---------- ------------------------
SYSSTATS_INFO DSTART 12-30-2021 17:27
SYSSTATS_INFO DSTOP 12-30-2021 17:38
SYSSTATS_INFO FLAGS 1
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_MAIN CPUSPEED 1157
SYSSTATS_MAIN CPUSPEEDNW 3308.9701
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN SLAVETHR
SYSSTATS_MAIN SREADTIM 2.626
13行が選択されました。
SQL>
