ablog

不器用で落着きのない技術者のメモ

Oracle Database で統計情報がいつ収集されたかざっくり確認する SQL

Oracle

yoheia/show_last_analyzed.sql at master · yoheia/yoheia · GitHub

set pageszie 50000
set linesize 200
col owner for a10

select 'DBA_TABLES' "DICTIONARY", owner,
		to_char(min(last_analyzed),'YYYY-MM-DD HH24:MI:SS') "MIN",
		to_char(max(last_analyzed),'YYYY-MM-DD HH24:MI:SS') "MAX",
		to_char(median(last_analyzed),'YYYY-MM-DD HH24:MI:SS') "AVG",
		to_date(max(last_analyzed)) - to_date(min(last_analyzed)) "MAX-MIN"
	from dba_tables group by owner
union all
select 'DBA_TAB_PARTITIONS' "DICTIONARY", table_owner "OWNER", 
		to_char(min(last_analyzed),'YYYY-MM-DD HH24:MI:SS') "MIN",
		to_char(max(last_analyzed),'YYYY-MM-DD HH24:MI:SS') "MAX",
		to_char(median(last_analyzed),'YYYY-MM-DD HH24:MI:SS') "AVG",
		to_date(max(last_analyzed)) - to_date(min(last_analyzed)) "MAX-MIN"
	from dba_tab_partitions group by table_owner
union all
select 'DBA_TAB_SUBPARTITIONS' "DICTIONARY", table_owner "OWNER", 
		to_char(min(last_analyzed),'YYYY-MM-DD HH24:MI:SS') "MIN",
		to_char(max(last_analyzed),'YYYY-MM-DD HH24:MI:SS') "MAX",
		to_char(median(last_analyzed),'YYYY-MM-DD HH24:MI:SS') "AVG",
		to_date(max(last_analyzed)) - to_date(min(last_analyzed)) "MAX-MIN"
	from dba_tab_subpartitions group by table_owner
union all
select 'DBA_INDEXES' "DICTIONARY", owner,
		to_char(min(last_analyzed),'YYYY-MM-DD HH24:MI:SS') "MIN",
		to_char(max(last_analyzed),'YYYY-MM-DD HH24:MI:SS') "MAX",
		to_char(median(last_analyzed),'YYYY-MM-DD HH24:MI:SS') "AVG",
		to_date(max(last_analyzed)) - to_date(min(last_analyzed)) "MAX-MIN"
	from dba_indexes group by owner
union all
select 'DBA_IND_PARTITIONS' "DICTIONARY", index_owner "OWNER", 
		to_char(min(last_analyzed),'YYYY-MM-DD HH24:MI:SS') "MIN",
		to_char(max(last_analyzed),'YYYY-MM-DD HH24:MI:SS') "MAX",
		to_char(median(last_analyzed),'YYYY-MM-DD HH24:MI:SS') "AVG",
		to_date(max(last_analyzed)) - to_date(min(last_analyzed)) "MAX-MIN"
	from dba_ind_partitions group by index_owner
union all
select 'DBA_IND_SUBPARTITIONS' "DICTIONARY", index_owner "OWNER", 
		to_char(min(last_analyzed),'YYYY-MM-DD HH24:MI:SS') "MIN",
		to_char(max(last_analyzed),'YYYY-MM-DD HH24:MI:SS') "MAX",
		to_char(median(last_analyzed),'YYYY-MM-DD HH24:MI:SS') "AVG",
		to_date(max(last_analyzed)) - to_date(min(last_analyzed)) "MAX-MIN"
	from dba_ind_subpartitions group by index_owner
union all
select 'DBA_TAB_COLUMNS' "DICTIONARY", owner "OWNER", 
		to_char(min(last_analyzed),'YYYY-MM-DD HH24:MI:SS') "MIN",
		to_char(max(last_analyzed),'YYYY-MM-DD HH24:MI:SS') "MAX",
		to_char(median(last_analyzed),'YYYY-MM-DD HH24:MI:SS') "AVG",
		to_date(max(last_analyzed)) - to_date(min(last_analyzed)) "MAX-MIN"
	from dba_tab_columns group by owner
union all
select 'DBA_TAB_COL_STATISTICS' "DICTIONARY", owner "OWNER", 
		to_char(min(last_analyzed),'YYYY-MM-DD HH24:MI:SS') "MIN",
		to_char(max(last_analyzed),'YYYY-MM-DD HH24:MI:SS') "MAX",
		to_char(median(last_analyzed),'YYYY-MM-DD HH24:MI:SS') "AVG",
		to_date(max(last_analyzed)) - to_date(min(last_analyzed)) "MAX-MIN"
	from dba_tab_col_statistics group by owner
union all
select 'DBA_PART_COL_STATISTICS' "DICTIONARY", owner "OWNER", 
		to_char(min(last_analyzed),'YYYY-MM-DD HH24:MI:SS') "MIN",
		to_char(max(last_analyzed),'YYYY-MM-DD HH24:MI:SS') "MAX",
		to_char(median(last_analyzed),'YYYY-MM-DD HH24:MI:SS') "AVG",
		to_date(max(last_analyzed)) - to_date(min(last_analyzed)) "MAX-MIN"
	from dba_part_col_statistics group by owner
union all
select 'DBA_SUBPART_COL_STATISTICS' "DICTIONARY", owner "OWNER", 
		to_char(min(last_analyzed),'YYYY-MM-DD HH24:MI:SS') "MIN",
		to_char(max(last_analyzed),'YYYY-MM-DD HH24:MI:SS') "MAX",
		to_char(median(last_analyzed),'YYYY-MM-DD HH24:MI:SS') "AVG",
		to_date(max(last_analyzed)) - to_date(min(last_analyzed)) "MAX-MIN"
	from dba_subpart_col_statistics group by owner
;

実行結果

DICTIONARY                 OWNER      MIN                 MAX                 AVG                    MAX-MIN
-------------------------- ---------- ------------------- ------------------- ------------------- ----------
DBA_TABLES                 DBSNMP     2010-01-26 22:56:17 2010-01-26 22:56:17 2010-01-26 22:56:17          0
DBA_TABLES                 EXFSYS     2010-01-26 22:59:54 2010-01-26 23:01:30 2010-01-26 23:01:30          0
DBA_TABLES                 MDSYS      2010-01-27 22:00:28 2010-01-27 22:00:44 2010-01-27 22:00:31          0
DBA_TABLES                 ORDSYS     2010-01-26 23:04:25 2010-01-26 23:04:25 2010-01-26 23:04:24          0
DBA_TABLES                 OUTLN      2010-01-26 22:56:17 2010-01-26 22:56:17 2010-01-26 22:56:17          0
DBA_TABLES                 PERFSTAT   2010-01-27 22:00:35 2010-01-27 22:00:44 2010-01-27 22:00:39          0
DBA_TABLES                 SYS        2010-01-26 22:56:22 2010-01-27 23:05:30 2010-01-26 22:56:43          1
DBA_TABLES                 SYSMAN     2010-01-26 23:08:19 2010-01-27 22:21:48 2010-01-26 23:08:22          1
DBA_TABLES                 SYSTEM     2010-01-26 22:56:17 2010-01-26 23:08:28 2010-01-26 22:56:19          0
DBA_TABLES                 TSMSYS     2010-01-27 22:00:26 2010-01-27 22:00:26 2010-01-27 22:00:26          0
DBA_TABLES                 WMSYS      2010-01-26 22:57:48 2010-01-26 22:57:49 2010-01-26 22:57:48          0
DBA_TABLES                 XDB        2010-01-26 23:01:06 2010-01-27 22:21:46 2010-01-26 23:01:08          1
DBA_TAB_PARTITIONS         SYS        2010-01-26 22:56:42 2010-01-27 23:05:30 2010-01-27 23:05:25          1
DBA_TAB_PARTITIONS         SYSTEM     2010-01-26 22:56:18 2010-01-26 22:56:20 2010-01-26 22:56:19          0
DBA_INDEXES                DBSNMP     2010-01-26 22:56:17 2010-01-26 22:56:17 2010-01-26 22:56:17          0
DBA_INDEXES                EXFSYS     2010-01-26 22:59:54 2010-01-26 23:01:30 2010-01-26 23:01:30          0
DBA_INDEXES                MDSYS      2010-01-27 22:00:28 2010-01-27 22:00:44 2010-01-27 22:00:30          0
DBA_INDEXES                ORDSYS     2010-01-26 23:04:25 2010-01-26 23:04:25 2010-01-26 23:04:24          0
DBA_INDEXES                OUTLN      2010-01-26 22:56:17 2010-01-26 22:56:17 2010-01-26 22:56:17          0
DBA_INDEXES                PERFSTAT   2010-01-27 22:00:35 2010-01-27 22:00:45 2010-01-27 22:00:39          0
DBA_INDEXES                SYS        2010-01-26 22:56:22 2010-01-27 23:05:30 2010-01-26 22:56:44          1
DBA_INDEXES                SYSMAN     2010-01-26 23:08:20 2010-01-27 22:21:48 2010-01-26 23:08:23          1
DBA_INDEXES                SYSTEM     2010-01-26 22:56:17 2010-01-26 23:08:28 2010-01-26 22:56:20          0
DBA_INDEXES                TSMSYS     2010-01-27 22:00:27 2010-01-27 22:00:27 2010-01-27 22:00:27          0
DBA_INDEXES                WMSYS      2010-01-26 22:57:48 2010-01-26 22:57:49 2010-01-26 22:57:48          0
DBA_INDEXES                XDB        2010-01-26 23:01:06 2010-01-27 22:21:48 2010-01-26 23:01:08          1
DBA_IND_PARTITIONS         SYS        2010-01-26 22:56:42 2010-01-27 23:05:30 2010-01-27 23:05:24          1
DBA_IND_PARTITIONS         SYSTEM     2010-01-26 22:56:18 2010-01-26 22:56:20 2010-01-26 22:56:19          0
DBA_TAB_COLUMNS            DBSNMP     2010-01-26 22:56:17 2010-01-26 22:56:17 2010-01-26 22:56:17          0
DBA_TAB_COLUMNS            EXFSYS     2010-01-26 22:59:54 2010-01-26 23:01:30 2010-01-26 22:59:54          0
DBA_TAB_COLUMNS            MDSYS      2010-01-27 22:00:28 2010-01-27 22:00:44 2010-01-27 22:00:31          0
DBA_TAB_COLUMNS            ORDSYS     2010-01-26 23:04:25 2010-01-26 23:04:25 2010-01-26 23:04:25          0
DBA_TAB_COLUMNS            OUTLN      2010-01-26 22:56:17 2010-01-26 22:56:17 2010-01-26 22:56:17          0
DBA_TAB_COLUMNS            PERFSTAT   2010-01-27 22:00:35 2010-01-27 22:00:44 2010-01-27 22:00:39          0
DBA_TAB_COLUMNS            SYS        2010-01-26 22:56:22 2010-01-27 23:05:30 2010-01-26 22:56:44          1
DBA_TAB_COLUMNS            SYSMAN     2010-01-26 23:08:19 2010-01-27 22:21:48 2010-01-26 23:08:23          1
DBA_TAB_COLUMNS            SYSTEM     2010-01-26 22:56:17 2010-01-26 23:08:28 2010-01-26 22:56:19          0
DBA_TAB_COLUMNS            TSMSYS     2010-01-27 22:00:26 2010-01-27 22:00:26 2010-01-27 22:00:26          0
DBA_TAB_COLUMNS            WMSYS      2010-01-26 22:57:48 2010-01-26 22:57:49 2010-01-26 22:57:48          0
DBA_TAB_COLUMNS            XDB        2010-01-26 23:01:06 2010-01-27 22:21:46 2010-01-26 23:01:08          1
DBA_TAB_COL_STATISTICS     DBSNMP     2010-01-26 22:56:17 2010-01-26 22:56:17 2010-01-26 22:56:17          0
DBA_TAB_COL_STATISTICS     EXFSYS     2010-01-26 22:59:54 2010-01-26 23:01:30 2010-01-26 22:59:54          0
DBA_TAB_COL_STATISTICS     MDSYS      2010-01-27 22:00:28 2010-01-27 22:00:44 2010-01-27 22:00:31          0
DBA_TAB_COL_STATISTICS     ORDSYS     2010-01-26 23:04:25 2010-01-26 23:04:25 2010-01-26 23:04:25          0
DBA_TAB_COL_STATISTICS     OUTLN      2010-01-26 22:56:17 2010-01-26 22:56:17 2010-01-26 22:56:17          0
DBA_TAB_COL_STATISTICS     PERFSTAT   2010-01-27 22:00:35 2010-01-27 22:00:44 2010-01-27 22:00:39          0
DBA_TAB_COL_STATISTICS     SYS        2010-01-26 22:56:22 2010-01-27 23:05:30 2010-01-26 22:56:44          1
DBA_TAB_COL_STATISTICS     SYSMAN     2010-01-26 23:08:19 2010-01-27 22:21:48 2010-01-26 23:08:23          1
DBA_TAB_COL_STATISTICS     SYSTEM     2010-01-26 22:56:17 2010-01-26 23:08:28 2010-01-26 22:56:19          0
DBA_TAB_COL_STATISTICS     TSMSYS     2010-01-27 22:00:26 2010-01-27 22:00:26 2010-01-27 22:00:26          0
DBA_TAB_COL_STATISTICS     WMSYS      2010-01-26 22:57:48 2010-01-26 22:57:49 2010-01-26 22:57:48          0
DBA_TAB_COL_STATISTICS     XDB        2010-01-26 23:01:06 2010-01-27 22:21:46 2010-01-26 23:01:08          1
DBA_PART_COL_STATISTICS    SYS        2010-01-26 22:56:42 2010-01-27 23:05:30 2010-01-27 23:05:24          1
DBA_PART_COL_STATISTICS    SYSTEM     2010-01-26 22:56:18 2010-01-26 22:56:20 2010-01-26 22:56:19          0

関連

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

参考

MEDIAN（集計関数） - オラクル・Oracle SQL 関数リファレンス