收集統計資訊的SQL指令碼（sosi.sql）--崔華大師

lhrbest發表於2017-06-02
原文網址 : http://blog.itpub.net/26736162/viewspace-2140227/
SQL指令碼

收集統計資訊的SQL指令碼（sosi.sql）--崔華大師


點選(此處)摺疊或開啟

  1. set echo off
  2. set scan on
  3. set lines 150
  4. set pages 66
  5. set verify off
  6. set feedback off
  7. set termout off
  8. column uservar new_value Table_Owner noprint
  9. select user uservar from dual;
  10. set termout on
  11. column TABLE_NAME heading "Tables owned by &Table_Owner" format a30
  12. select table_name from dba_tables where owner=upper('&Table_Owner') order by 1
  13. /
  14. undefine table_name
  15. undefine owner
  16. prompt
  17. accept owner prompt 'Please enter Name of Table Owner (Null = &Table_Owner): '
  18. accept table_name prompt 'Please enter Table Name to show Statistics for: '
  19. column TABLE_NAME heading "Table|Name" format a15
  20. column PARTITION_NAME heading "Partition|Name" format a15
  21. column SUBPARTITION_NAME heading "SubPartition|Name" format a15
  22. column NUM_ROWS heading "Number|of Rows" format 9,999,999,990
  23. column BLOCKS heading "Blocks" format 999,990
  24. column EMPTY_BLOCKS heading "Empty|Blocks" format 999,999,990
  25.   
  26. column AVG_SPACE heading "Average|Space" format 9,990
  27. column CHAIN_CNT heading "Chain|Count" format 999,990
  28. column AVG_ROW_LEN heading "Average|Row Len" format 990
  29. column COLUMN_NAME heading "Column|Name" format a25
  30. column NULLABLE heading Null|able format a4
  31. column NUM_DISTINCT heading "Distinct|Values" format 999,999,990
  32. column NUM_NULLS heading "Number|Nulls" format 9,999,990
  33. column NUM_BUCKETS heading "Number|Buckets" format 990
  34. column DENSITY heading "Density" format 990
  35. column INDEX_NAME heading "Index|Name" format a15
  36. column UNIQUENESS heading "Unique" format a9
  37. column BLEV heading "B|Tree|Level" format 90
  38. column LEAF_BLOCKS heading "Leaf|Blks" format 990
  39. column DISTINCT_KEYS heading "Distinct|Keys" format 9,999,999,990
  40. column AVG_LEAF_BLOCKS_PER_KEY heading "Average|Leaf Blocks|Per Key" format 99,990
  41. column AVG_DATA_BLOCKS_PER_KEY heading "Average|Data Blocks|Per Key" format 99,990
  42. column CLUSTERING_FACTOR heading "Cluster|Factor" format 999,999,990
  43. column COLUMN_POSITION heading "Col|Pos" format 990
  44. column col heading "Column|Details" format a24
  45. column COLUMN_LENGTH heading "Col|Len" format 9,990
  46. column GLOBAL_STATS heading "Global|Stats" format a6
  47. column USER_STATS heading "User|Stats" format a6
  48. column SAMPLE_SIZE heading "Sample|Size" format 9,999,999,990
  49. column to_char(t.last_analyzed,'MM-DD-YYYY') heading "Date|MM-DD-YYYY" format a10
  50.   
  51. prompt
  52. prompt ***********
  53. prompt Table Level
  54. prompt ***********
  55. prompt
  56. select
  57.     TABLE_NAME,
  58.     NUM_ROWS,
  59.     BLOCKS,
  60.     EMPTY_BLOCKS,
  61.     AVG_SPACE,
  62.     CHAIN_CNT,
  63.     AVG_ROW_LEN,
  64.     GLOBAL_STATS,
  65.     USER_STATS,
  66.     SAMPLE_SIZE,
  67.     to_char(t.last_analyzed,'MM-DD-YYYY')
  68. from dba_tables t
  69. where
  70.     owner = upper(nvl('&&Owner',user))
  71. and table_name = upper('&&Table_name')
  72. /
  73. select
  74.     COLUMN_NAME,
  75.     decode(t.DATA_TYPE,
  76.            'NUMBER',t.DATA_TYPE||'('||
  77.            decode(t.DATA_PRECISION,
  78.                   null,t.DATA_LENGTH||')',
  79.                   t.DATA_PRECISION||','||t.DATA_SCALE||')'),
  80.                   'DATE',t.DATA_TYPE,
  81.                   'LONG',t.DATA_TYPE,
  82.                   'LONG RAW',t.DATA_TYPE,
  83.                   'ROWID',t.DATA_TYPE,
  84.                   'MLSLABEL',t.DATA_TYPE,
  85.                   t.DATA_TYPE||'('||t.DATA_LENGTH||')') ||' '||
  86.     decode(t.nullable,
  87.               'N','NOT NULL',
  88.               'n','NOT NULL',
  89.               NULL) col,
  90.     NUM_DISTINCT,
  91.     DENSITY,
  92.     NUM_BUCKETS,
  93.     NUM_NULLS,
  94.     GLOBAL_STATS,
  95.     USER_STATS,
  96.     SAMPLE_SIZE,
  97.     to_char(t.last_analyzed,'MM-DD-YYYY')
  98. from dba_tab_columns t
  99. where
  100.     table_name = upper('&Table_name')
  101. and owner = upper(nvl('&Owner',user))
  102. /
  103.   
  104. select
  105.     INDEX_NAME,
  106.     UNIQUENESS,
  107.     BLEVEL BLev,
  108.     LEAF_BLOCKS,
  109.     DISTINCT_KEYS,
  110.     NUM_ROWS,
  111.     AVG_LEAF_BLOCKS_PER_KEY,
  112.     AVG_DATA_BLOCKS_PER_KEY,
  113.     CLUSTERING_FACTOR,
  114.     GLOBAL_STATS,
  115.     USER_STATS,
  116.     SAMPLE_SIZE,
  117.     to_char(t.last_analyzed,'MM-DD-YYYY')
  118. from
  119.     dba_indexes t
  120. where
  121.     table_name = upper('&Table_name')
  122. and table_owner = upper(nvl('&Owner',user))
  123. /
  124. break on index_name
  125. select
  126.     i.INDEX_NAME,
  127.     i.COLUMN_NAME,
  128.     i.COLUMN_POSITION,
  129.     decode(t.DATA_TYPE,
  130.            'NUMBER',t.DATA_TYPE||'('||
  131.            decode(t.DATA_PRECISION,
  132.                   null,t.DATA_LENGTH||')',
  133.                   t.DATA_PRECISION||','||t.DATA_SCALE||')'),
  134.                   'DATE',t.DATA_TYPE,
  135.                   'LONG',t.DATA_TYPE,
  136.                   'LONG RAW',t.DATA_TYPE,
  137.                   'ROWID',t.DATA_TYPE,
  138.                   'MLSLABEL',t.DATA_TYPE,
  139.                   t.DATA_TYPE||'('||t.DATA_LENGTH||')') ||' '||
  140.            decode(t.nullable,
  141.                   'N','NOT NULL',
  142.                   'n','NOT NULL',
  143.                   NULL) col
  144. from
  145.     dba_ind_columns i,
  146.     dba_tab_columns t
  147. where
  148.     i.table_name = upper('&Table_name')
  149. and owner = upper(nvl('&Owner',user))
  150. and i.table_name = t.table_name
  151. and i.column_name = t.column_name
  152. order by index_name,column_position
  153. /
  154.   
  155. prompt
  156. prompt ***************
  157. prompt Partition Level
  158. prompt ***************
  159.   
  160. select
  161.     PARTITION_NAME,
  162.     NUM_ROWS,
  163.     BLOCKS,
  164.     EMPTY_BLOCKS,
  165.     AVG_SPACE,
  166.     CHAIN_CNT,
  167.     AVG_ROW_LEN,
  168.     GLOBAL_STATS,
  169.     USER_STATS,
  170.     SAMPLE_SIZE,
  171.     to_char(t.last_analyzed,'MM-DD-YYYY')
  172. from
  173.     dba_tab_partitions t
  174. where
  175.     table_owner = upper(nvl('&&Owner',user))
  176. and table_name = upper('&&Table_name')
  177. order by partition_position
  178. /
  179.   
  180.   
  181. break on partition_name
  182. select
  183.     PARTITION_NAME,
  184.     COLUMN_NAME,
  185.     NUM_DISTINCT,
  186.     DENSITY,
  187.     NUM_BUCKETS,
  188.     NUM_NULLS,
  189.     GLOBAL_STATS,
  190.     USER_STATS,
  191.     SAMPLE_SIZE,
  192.     to_char(t.last_analyzed,'MM-DD-YYYY')
  193. from
  194.     dba_PART_COL_STATISTICS t
  195. where
  196.     table_name = upper('&Table_name')
  197. and owner = upper(nvl('&Owner',user))
  198. /
  199.   
  200. break on partition_name
  201. select
  202.     t.INDEX_NAME,
  203.     t.PARTITION_NAME,
  204.     t.BLEVEL BLev,
  205.     t.LEAF_BLOCKS,
  206.     t.DISTINCT_KEYS,
  207.     t.NUM_ROWS,
  208.     t.AVG_LEAF_BLOCKS_PER_KEY,
  209.     t.AVG_DATA_BLOCKS_PER_KEY,
  210.     t.CLUSTERING_FACTOR,
  211.     t.GLOBAL_STATS,
  212.     t.USER_STATS,
  213.     t.SAMPLE_SIZE,
  214.     to_char(t.last_analyzed,'MM-DD-YYYY')
  215. from
  216.     dba_ind_partitions t,
  217.     dba_indexes i
  218. where
  219.     i.table_name = upper('&Table_name')
  220. and i.table_owner = upper(nvl('&Owner',user))
  221. and i.owner = t.index_owner
  222. and i.index_name=t.index_name
  223. /
  224.   
  225.   
  226. prompt
  227. prompt ***************
  228. prompt SubPartition Level
  229. prompt ***************
  230.   
  231. select
  232.     PARTITION_NAME,
  233.     SUBPARTITION_NAME,
  234.     NUM_ROWS,
  235.     BLOCKS,
  236.     EMPTY_BLOCKS,
  237.     AVG_SPACE,
  238.     CHAIN_CNT,
  239.     AVG_ROW_LEN,
  240.     GLOBAL_STATS,
  241.     USER_STATS,
  242.     SAMPLE_SIZE,
  243.     to_char(t.last_analyzed,'MM-DD-YYYY')
  244. from
  245.     dba_tab_subpartitions t
  246. where
  247.     table_owner = upper(nvl('&&Owner',user))
  248. and table_name = upper('&&Table_name')
  249. order by SUBPARTITION_POSITION
  250. /
  251. break on partition_name
  252. select
  253.     p.PARTITION_NAME,
  254.     t.SUBPARTITION_NAME,
  255.     t.COLUMN_NAME,
  256.     t.NUM_DISTINCT,
  257.     t.DENSITY,
  258.     t.NUM_BUCKETS,
  259.     t.NUM_NULLS,
  260.     t.GLOBAL_STATS,
  261.     t.USER_STATS,
  262.     t.SAMPLE_SIZE,
  263.     to_char(t.last_analyzed,'MM-DD-YYYY')
  264. from
  265.     dba_SUBPART_COL_STATISTICS t,
  266.     dba_tab_subpartitions p
  267. where
  268.     t.table_name = upper('&Table_name')
  269. and t.owner = upper(nvl('&Owner',user))
  270. and t.subpartition_name = p.subpartition_name
  271. and t.owner = p.table_owner
  272. and t.table_name=p.table_name
  273. /
  274.   
  275. break on partition_name
  276. select
  277.     t.INDEX_NAME,
  278.     t.PARTITION_NAME,
  279.     t.SUBPARTITION_NAME,
  280.     t.BLEVEL BLev,
  281.     t.LEAF_BLOCKS,
  282.     t.DISTINCT_KEYS,
  283.     t.NUM_ROWS,
  284.     t.AVG_LEAF_BLOCKS_PER_KEY,
  285.     t.AVG_DATA_BLOCKS_PER_KEY,
  286.     t.CLUSTERING_FACTOR,
  287.     t.GLOBAL_STATS,
  288.     t.USER_STATS,
  289.     t.SAMPLE_SIZE,
  290.     to_char(t.last_analyzed,'MM-DD-YYYY')
  291. from
  292.     dba_ind_subpartitions t,
  293.     dba_indexes i
  294. where
  295.     i.table_name = upper('&Table_name')
  296. and i.table_owner = upper(nvl('&Owner',user))
  297. and i.owner = t.index_owner
  298. and i.index_name=t.index_name
  299. /
  300.   
  301. clear breaks
  302. 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群，學習最實用的資料庫技術。

收集統計資訊的SQL指令碼（sosi.sql）--崔華大師
 DBA筆試面試講解
 歡迎與我聯絡

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2140227/，如需轉載，請註明出處，否則將追究法律責任。

相關文章