收集统计信息的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