【統計資訊】全面檢視錶所有統計資訊SQL
1. SQL 指令碼說明
1 ）用於查詢某個表的所有統計資訊，這個指令碼將會訪問使用者名稱和表名
2 ）執行環境為 sqlplus
3 ）執行者許可權為
Requires select privileges on dba_tables, dba_tab_columns, dba_indexes, dba_ind_columns
2. 該 SQL 參考輸出結果如下
*********** Table Level *********** Table Number Empty Average Chain Average Global User Sample Date Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY --------------- ---------- -------- -------- ------- ----- ------- ------ ------ ---------- ---------- T1 5,000 20 0 0 0 9 YES NO 5,000 06-30-2003 Column Column Distinct Number Global User Sample Date Name Details Values Density Buckets NUM_NULLS Stats Stats Size MM-DD-YYYY ------------------------- ------------------------ -------- ------- ------- ---------- ------ ------ ---------- ---------- T1C1 NUMBER(22) 5,000 0 1 0 YES NO 5,000 06-30-2003 T1C2 NUMBER(22) 7 0 1 0 YES NO 5,000 06-30-2003 T1C3 NUMBER(22) 8 0 1 0 YES NO 5,000 06-30-2003 B Average Average Index Tree Leaf Distinct Number Leaf Blocks Data Blocks Cluster Global User Sample Date Name Unique Level Blks Keys of Rows Per Key Per Key Factor Stats Stats Size MM-DD-YYYY --------------- --------- ----- ---- ---------- ---------- ----------- ----------- -------- ------ ------ ---------- ---------- UN_T1 NONUNIQUE 1 12 5,000 5,000 1 1 12 YES NO 5,000 06-30-2003 Index Column Col Column Name Name Pos Details --------------- ------------------------- ---- ------------------------ UN_T1 T1C1 1 NUMBER(22) *************** Partition Level *************** Partition Number Empty Average Chain Average Global User Sample Date Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY --------------- ---------- -------- -------- ------- ----- ------- ------ ------ ---------- ---------- Q1 2,144 10 0 0 0 9 YES NO 2,144 06-30-2003 Q2 2,856 10 0 0 0 9 YES NO 2,856 06-30-2003 Partition Column Distinct Number Global User Sample Date Name Name Values Density Buckets NUM_NULLS Stats Stats Size MM-DD-YYYY --------------- ------------------------- -------- ------- ------- ---------- ------ ------ ---------- ---------- Q1 T1C1 2,144 0 1 0 YES NO 2,144 06-30-2003 T1C2 3 0 1 0 YES NO 2,144 06-30-2003 T1C3 8 0 1 0 YES NO 2,144 06-30-2003 Q2 T1C1 2,856 0 1 0 YES NO 2,856 06-30-2003 T1C2 4 0 1 0 YES NO 2,856 06-30-2003 T1C3 8 0 1 0 YES NO 2,856 06-30-2003 B Average Average Index Partition Tree Leaf Distinct Number Leaf Blocks Data Blocks Cluster Global User Sample Date Name Name Level Blks Keys of Rows Per Key Per Key Factor Stats Stats Size MM-DD-YYYY --------------- --------------- ----- ---- ---------- ---------- ----------- ----------- -------- ------ ------ ---------- ---------- UN_T1 Q1 1 12 5,000 5,000 1 1 12 YES NO 5,000 06-30-2003 UN_T1 Q2 1 12 5,000 5,000 1 1 12 YES NO 5,000 06-30-2003 *************** SubPartition Level *************** Partition SubPartition Number Empty Average Chain Average Global User Sample Date Name Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY --------------- --------------- ---------- -------- -------- ------- ----- ------- ------ ------ ---------- ---------- Q1 SYS_SUBP497 803 5 0 0 0 9 YES NO 803 06-30-2003 Q2 SYS_SUBP499 1,072 5 0 0 0 9 YES NO 1,072 06-30-2003 Q1 SYS_SUBP498 1,341 5 0 0 0 9 YES NO 1,341 06-30-2003 Q2 SYS_SUBP500 1,784 5 0 0 0 9 YES NO 1,784 06-30-2003 Partition SubPartition Column Distinct Number Global User Sample Date Name Name Name Values Density Buckets NUM_NULLS Stats Stats Size MM-DD-YYYY --------------- --------------- ------------------------- -------- ------- ------- ---------- ------ ------ ---------- ---------- Q1 SYS_SUBP497 T1C1 803 0 1 0 YES NO 803 06-30-2003 SYS_SUBP498 T1C1 1,341 0 1 0 YES NO 1,341 06-30-2003 SYS_SUBP497 T1C2 3 0 1 0 YES NO 803 06-30-2003 SYS_SUBP498 T1C2 3 0 1 0 YES NO 1,341 06-30-2003 SYS_SUBP497 T1C3 3 0 1 0 YES NO 803 06-30-2003 SYS_SUBP498 T1C3 5 0 1 0 YES NO 1,341 06-30-2003 Q2 SYS_SUBP499 T1C1 1,072 0 1 0 YES NO 1,072 06-30-2003 SYS_SUBP500 T1C1 1,784 0 1 0 YES NO 1,784 06-30-2003 SYS_SUBP499 T1C2 4 0 1 0 YES NO 1,072 06-30-2003 SYS_SUBP500 T1C2 4 0 1 0 YES NO 1,784 06-30-2003 SYS_SUBP499 T1C3 3 0 1 0 YES NO 1,072 06-30-2003 SYS_SUBP500 T1C3 5 0 1 0 YES NO 1,784 06-30-2003 B Average Average Index Partition SubPartition Tree Leaf Distinct Number Leaf Blocks Data Blocks Cluster Global User Sample Date Name Name Name Level Blks Keys of Rows Per Key Per Key Factor Stats Stats Size MM-DD-YYY --------------- --------------- --------------- ----- ---- ---------- ---------- ----------- ----------- -------- ------ ------ ---------- --------- UN_T1 Q1 SYS_SUBP497 1 2 803 803 1 1 2 YES NO 803 06-30-200 UN_T1 SYS_SUBP498 1 3 1,341 1,341 1 1 3 YES NO 1,341 06-30-200 UN_T1 Q2 SYS_SUBP499 1 3 1,072 1,072 1 1 3 YES NO 1,072 06-30-200 UN_T1 SYS_SUBP500 1 4 1,784 1,784 1 1 4 YES NO 1,784 06-30-200
3.SQL 文字如下
（直接複製即可）
建議新建一個statistic.sql 文字，輸入以下資訊，使用 sqlplus 執行@ statistic.sql
***********************************************************
set echo off
set scan on
set lines 150
set pages 66
set verify off
set feedback off
set termout off
column uservar new_value Table_Owner noprint
select user uservar from dual;
set termout on
column TABLE_NAME heading "Tables owned by &Table_Owner" format a30
select table_name from dba_tables where owner=upper('&Table_Owner') order by 1
/
undefine table_name
undefine owner
prompt
accept owner prompt 'Please enter Name of Table Owner (Null = &Table_Owner): '
accept table_name prompt 'Please enter Table Name to show Statistics for: '
column TABLE_NAME heading "Table|Name" format a15
column PARTITION_NAME heading "Partition|Name" format a15
column SUBPARTITION_NAME heading "SubPartition|Name" format a15
column NUM_ROWS heading "Number|of Rows" format 9,999,999,999,990
column BLOCKS heading "Blocks" format 999,990
column EMPTY_BLOCKS heading "Empty|Blocks" format 999,999,990
column AVG_SPACE heading "Average|Space" format 9,990
column CHAIN_CNT heading "Chain|Count" format 999,990
column AVG_ROW_LEN heading "Average|Row Len" format 990
column COLUMN_NAME heading "Column|Name" format a25
column NULLABLE heading Null|able format a4
column NUM_DISTINCT heading "Distinct|Values" format 999,999,990
column NUM_NULLS heading "Number|Nulls" format 9,999,990
column NUM_BUCKETS heading "Number|Buckets" format 990
column DENSITY heading "Density" format 990
column INDEX_NAME heading "Index|Name" format a15
column UNIQUENESS heading "Unique" format a9
column BLEV heading "B|Tree|Level" format 90
column LEAF_BLOCKS heading "Leaf|Blks" format 990
column DISTINCT_KEYS heading "Distinct|Keys" format 9,999,999,990
column AVG_LEAF_BLOCKS_PER_KEY heading "Average|Leaf Blocks|Per Key" format 99,990
column AVG_DATA_BLOCKS_PER_KEY heading "Average|Data Blocks|Per Key" format 99,990
column CLUSTERING_FACTOR heading "Cluster|Factor" format 999,999,990
column COLUMN_POSITION heading "Col|Pos" format 990
column col heading "Column|Details" format a24
column COLUMN_LENGTH heading "Col|Len" format 9,990
column GLOBAL_STATS heading "Global|Stats" format a6
column USER_STATS heading "User|Stats" format a6
column SAMPLE_SIZE heading "Sample|Size" format 9,999,999,999,990
column to_char(t.last_analyzed,'MM-DD-YYYY') heading "Date|MM-DD-YYYY" format a10
prompt
prompt ***********
prompt Table Level
prompt ***********
prompt
select
TABLE_NAME,
NUM_ROWS,
BLOCKS,
EMPTY_BLOCKS,
AVG_SPACE,
CHAIN_CNT,
AVG_ROW_LEN,
GLOBAL_STATS,
USER_STATS,
SAMPLE_SIZE,
to_char(t.last_analyzed,'MM-DD-YYYY')
from dba_tables t
where
owner = upper(nvl('&&Owner',user))
and table_name = upper('&&Table_name')
/
select
COLUMN_NAME,
decode(t.DATA_TYPE,
'NUMBER',t.DATA_TYPE||'('||
decode(t.DATA_PRECISION,
null,t.DATA_LENGTH||')',
t.DATA_PRECISION||','||t.DATA_SCALE||')'),
'DATE',t.DATA_TYPE,
'LONG',t.DATA_TYPE,
'LONG RAW',t.DATA_TYPE,
'ROWID',t.DATA_TYPE,
'MLSLABEL',t.DATA_TYPE,
t.DATA_TYPE||'('||t.DATA_LENGTH||')') ||' '||
decode(t.nullable,
'N','NOT NULL',
'n','NOT NULL',
NULL) col,
NUM_DISTINCT,
DENSITY,
NUM_BUCKETS,
NUM_NULLS,
GLOBAL_STATS,
USER_STATS,
SAMPLE_SIZE,
to_char(t.last_analyzed,'MM-DD-YYYY')
from dba_tab_columns t
where
table_name = upper('&Table_name')
and owner = upper(nvl('&Owner',user))
/
select
INDEX_NAME,
UNIQUENESS,
BLEVEL BLev,
LEAF_BLOCKS,
DISTINCT_KEYS,
NUM_ROWS,
AVG_LEAF_BLOCKS_PER_KEY,
AVG_DATA_BLOCKS_PER_KEY,
CLUSTERING_FACTOR,
GLOBAL_STATS,
USER_STATS,
SAMPLE_SIZE,
to_char(t.last_analyzed,'MM-DD-YYYY')
from
dba_indexes t
where
table_name = upper('&Table_name')
and table_owner = upper(nvl('&Owner',user))
/
break on index_name
select
i.INDEX_NAME,
i.COLUMN_NAME,
i.COLUMN_POSITION,
decode(t.DATA_TYPE,
'NUMBER',t.DATA_TYPE||'('||
decode(t.DATA_PRECISION,
null,t.DATA_LENGTH||')',
t.DATA_PRECISION||','||t.DATA_SCALE||')'),
'DATE',t.DATA_TYPE,
'LONG',t.DATA_TYPE,
'LONG RAW',t.DATA_TYPE,
'ROWID',t.DATA_TYPE,
'MLSLABEL',t.DATA_TYPE,
t.DATA_TYPE||'('||t.DATA_LENGTH||')') ||' '||
decode(t.nullable,
'N','NOT NULL',
'n','NOT NULL',
NULL) col
from
dba_ind_columns i,
dba_tab_columns t
where
i.table_name = upper('&Table_name')
and owner = upper(nvl('&Owner',user))
and i.table_name = t.table_name
and i.column_name = t.column_name
order by index_name,column_position
/
prompt
prompt ***************
prompt Partition Level
prompt ***************
select
PARTITION_NAME,
NUM_ROWS,
BLOCKS,
EMPTY_BLOCKS,
AVG_SPACE,
CHAIN_CNT,
AVG_ROW_LEN,
GLOBAL_STATS,
USER_STATS,
SAMPLE_SIZE,
to_char(t.last_analyzed,'MM-DD-YYYY')
from
dba_tab_partitions t
where
table_owner = upper(nvl('&&Owner',user))
and table_name = upper('&&Table_name')
order by partition_position
/
break on partition_name
select
PARTITION_NAME,
COLUMN_NAME,
NUM_DISTINCT,
DENSITY,
NUM_BUCKETS,
NUM_NULLS,
GLOBAL_STATS,
USER_STATS,
SAMPLE_SIZE,
to_char(t.last_analyzed,'MM-DD-YYYY')
from
dba_PART_COL_STATISTICS t
where
table_name = upper('&Table_name')
and owner = upper(nvl('&Owner',user))
/
break on partition_name
select
t.INDEX_NAME,
t.PARTITION_NAME,
t.BLEVEL BLev,
t.LEAF_BLOCKS,
t.DISTINCT_KEYS,
t.NUM_ROWS,
t.AVG_LEAF_BLOCKS_PER_KEY,
t.AVG_DATA_BLOCKS_PER_KEY,
t.CLUSTERING_FACTOR,
t.GLOBAL_STATS,
t.USER_STATS,
t.SAMPLE_SIZE,
to_char(t.last_analyzed,'MM-DD-YYYY')
from
dba_ind_partitions t,
dba_indexes i
where
i.table_name = upper('&Table_name')
and i.table_owner = upper(nvl('&Owner',user))
and i.owner = t.index_owner
and i.index_name=t.index_name
/
prompt
prompt ***************
prompt SubPartition Level
prompt ***************
select
PARTITION_NAME,
SUBPARTITION_NAME,
NUM_ROWS,
BLOCKS,
EMPTY_BLOCKS,
AVG_SPACE,
CHAIN_CNT,
AVG_ROW_LEN,
GLOBAL_STATS,
USER_STATS,
SAMPLE_SIZE,
to_char(t.last_analyzed,'MM-DD-YYYY')
from
dba_tab_subpartitions t
where
table_owner = upper(nvl('&&Owner',user))
and table_name = upper('&&Table_name')
order by SUBPARTITION_POSITION
/
break on partition_name
select
p.PARTITION_NAME,
t.SUBPARTITION_NAME,
t.COLUMN_NAME,
t.NUM_DISTINCT,
t.DENSITY,
t.NUM_BUCKETS,
t.NUM_NULLS,
t.GLOBAL_STATS,
t.USER_STATS,
t.SAMPLE_SIZE,
to_char(t.last_analyzed,'MM-DD-YYYY')
from
dba_SUBPART_COL_STATISTICS t,
dba_tab_subpartitions p
where
t.table_name = upper('&Table_name')
and t.owner = upper(nvl('&Owner',user))
and t.subpartition_name = p.subpartition_name
and t.owner = p.table_owner
and t.table_name=p.table_name
/
break on partition_name
select
t.INDEX_NAME,
t.PARTITION_NAME,
t.SUBPARTITION_NAME,
t.BLEVEL BLev,
t.LEAF_BLOCKS,
t.DISTINCT_KEYS,
t.NUM_ROWS,
t.AVG_LEAF_BLOCKS_PER_KEY,
t.AVG_DATA_BLOCKS_PER_KEY,
t.CLUSTERING_FACTOR,
t.GLOBAL_STATS,
t.USER_STATS,
t.SAMPLE_SIZE,
to_char(t.last_analyzed,'MM-DD-YYYY')
from
dba_ind_subpartitions t,
dba_indexes i
where
i.table_name = upper('&Table_name')
and i.table_owner = upper(nvl('&Owner',user))
and i.owner = t.index_owner
and i.index_name=t.index_name
/
clear breaks
set echo on
---- end -----
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31529886/viewspace-2738028/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 檢視錶的統計資訊SQL指令碼SQL指令碼
- [sqlserver] 檢視錶的統計資訊SQLServer
- Oracle錶的歷史統計資訊檢視Oracle
- 【TUNE_ORACLE】檢視錶，列和索引的統計資訊SQL參考Oracle索引SQL
- oracle 統計資訊檢視與收集Oracle
- 11g新特性--檢視錶的歷史統計資訊差異
- 【統計資訊】Oracle統計資訊Oracle
- PostgreSQL統計資訊的幾個重要檢視SQL
- oracle檢視和更新統計表的資訊Oracle
- SQL Server 更新統計資訊SQLServer
- ORACLE表統計資訊與列統計資訊、索引統計資訊Oracle索引
- 【sql調優】系統資訊統計SQL
- oracle 11g 統計資訊 相關檢視Oracle
- Oracle檢視查詢慢之統計資訊收集Oracle
- ORACLE表統計資訊與列統計資訊Oracle
- 分享檢視統計資訊非常好的指令碼指令碼
- 檢視統計資訊分析每次經歷的時間
- 微課sql最佳化(3)、統計資訊收集(2)-如何收集統計資訊SQL
- 微課sql最佳化(8)、統計資訊收集(6)-統計資訊查詢SQL
- 【統計資訊】Oracle常用的收集統計資訊方式Oracle
- 【Oracle九大效能檢視】之9.v$sysstat_所有instance的統計資訊Oracle
- 統計資訊查詢檢視|全方位認識 sys 系統庫
- 【SCRIPT】Oracle統計資訊相關SQLOracleSQL
- 檢視系統資源資訊
- 檢視 Linux 系統資訊Linux
- Oracle 統計資訊Oracle
- MySQL 統計資訊MySql
- Oracle統計資訊Oracle
- 【TUNE_ORACLE】檢查統計資訊是否過期SQL參考OracleSQL
- SQL優化之統計資訊和索引SQL優化索引
- Oracle系統統計資訊Oracle
- 檢視作業系統基本資訊作業系統
- 檢視Linux系統版本資訊Linux
- linux檢視系統版本資訊Linux
- linux系統資訊檢視命令Linux
- Oracle收集統計資訊Oracle
- Oracle 統計資訊收集Oracle
- 收集oracle統計資訊Oracle