收集統計資訊的SQL指令碼(sosi.sql)--崔華大師
收集統計資訊的SQL指令碼(sosi.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,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,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
About Me
...............................................................................................................................
● 本文整理自網路
● 本文在itpub(http://blog.itpub.net/26736162)、部落格園(http://www.cnblogs.com/lhrbest)和個人微信公眾號(xiaomaimiaolhr)上有同步更新
● 本文itpub地址:http://blog.itpub.net/26736162/abstract/1/
● 本文部落格園地址:http://www.cnblogs.com/lhrbest
● 本文pdf版及小麥苗雲盤地址:http://blog.itpub.net/26736162/viewspace-1624453/
● 資料庫筆試面試題庫及解答:http://blog.itpub.net/26736162/viewspace-2134706/
● QQ群:230161599 微信群:私聊
● 聯絡我請加QQ好友(646634621),註明新增緣由
● 於 2017-06-02 09:00 ~ 2017-06-30 22:00 在魔都完成
● 文章內容來源於小麥苗的學習筆記,部分整理自網路,若有侵權或不當之處還請諒解
● 版權所有,歡迎分享本文,轉載請保留出處
...............................................................................................................................
拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號:xiaomaimiaolhr,掃描右邊的二維碼加入小麥苗的QQ群,學習最實用的資料庫技術。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26736162/viewspace-2140227/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle運維指令碼-收集統計資訊Oracle運維指令碼
- Oracle 11g 統計資訊收集指令碼Oracle指令碼
- 檢視錶的統計資訊SQL指令碼SQL指令碼
- Oracle 判斷 並 手動收集 統計資訊 指令碼Oracle指令碼
- 微課sql最佳化(3)、統計資訊收集(2)-如何收集統計資訊SQL
- SQL Server 監控統計阻塞指令碼資訊SQLServer指令碼
- 【TABLE】Oracle表資訊收集指令碼Oracle指令碼
- MySQL對所有表收集統計資訊(Python 2指令碼)MySqlPython指令碼
- 【統計資訊】Oracle常用的收集統計資訊方式Oracle
- 微課sql最佳化(8)、統計資訊收集(6)-統計資訊查詢SQL
- Oracle收集統計資訊Oracle
- Oracle 統計資訊收集Oracle
- 收集oracle統計資訊Oracle
- ORACLE 收集統計資訊Oracle
- 收集全庫統計資訊
- mysql如收集統計資訊MySql
- MySQL 5.5 統計資訊收集MySql
- 收集統計資訊的簡單操作
- ORACLE 統計資訊的收集與管理Oracle
- 統計資訊收集不完的解決
- 【SCRIPT】Oracle資料庫基本資訊收集指令碼Oracle資料庫指令碼
- 【TUNE_ORACLE】定製化收集統計資訊SQL參考OracleSQL
- 收集資料庫統計資訊需要收集直方圖資訊.資料庫直方圖
- MySQL系統如何收集統計資訊MySql
- 10g_crt_gather_table_stats(生成表的統計資訊收集指令碼,關鍵在引數)指令碼
- 9i_crt_gather_table_stats(生成表的統計資訊收集指令碼,關鍵在引數)指令碼
- Oracle統計資訊自動收集Oracle
- oracle收集統計資訊job停止Oracle
- oracle統計資訊--直方圖的收集:Oracle直方圖
- 重新收集oracle表的統計資訊Oracle
- Oracle統計資訊的收集和維護Oracle
- [指令碼例項]——統計系統資訊指令碼指令碼
- 表統計資訊匯出匯入指令碼指令碼
- 分享檢視統計資訊非常好的指令碼指令碼
- oracle 統計資訊檢視與收集Oracle
- 6 收集資料庫統計資訊資料庫
- Fixed Objects Statistics統計資訊收集 - 2Object
- oracle 11g統計資訊收集Oracle