パーティション表の統計情報がいつ収集されたか確認する SQL

スキーマ単位でパーティション表の統計情報が最後に収集された日時を表示する SQL。

set pagesize 50000
set linesize 200
col table_owner for a10
col table_name for a20
col index_owner for a10
col index_name for a20
col partition_name for a20
col last_analyzed for a20
col column_name for a30

select table_owner, table_name, partition_name, to_char(last_analyzed, 'YYYY-MM-DD HH24:MI:SS') "LAST_ANALYZED" 
	from dba_tab_partitions 
		where table_owner in ('SCOTT')
	order by table_owner, table_name, partition_name;

select index_owner, index_name, partition_name, to_char(last_analyzed, 'YYYY-MM-DD HH24:MI:SS') "LAST_ANALYZED" 
	from dba_ind_partitions 
		where index_owner in ('SCOTT')
	order by index_owner, index_name, partition_name;

select owner, table_name, partition_name, column_name, to_char(last_analyzed, 'YYYY-MM-DD HH24:MI:SS') "LAST_ANALYZED" 
	from dba_part_col_statistics
		where owner in ('SCOTT')
	order by owner, table_name, partition_name, column_name;

• 統計情報が格納されるディクショナリ表
  • DBA_TABLES
  • DBA_TAB_PARTITIONS
  • DBA_TAB_SUBPARTITIONS
  • DBA_INDEXES
  • DBA_IND_PARTITIONS
  • DBA_IND_SUBPARTITIONS
  • DBA_TAB_COLUMNS
  • DBA_TAB_COL_STATISTICS
  • DBA_PART_COL_STATISTICS
  • DBA_SUBPART_COL_STATISTICS

関連

• パーティション表の統計情報を収集する PL/SQL - ablog