oracle

Oracle オプティマイザ統計情報の取得、削除、取得状況確認

オプティマイザ統計にはいくつかの種類があり、かつそれぞれ適切なタイミングで取得する必要があります。基本的には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>
スポンサーリンク
コピペで使う