收集统计信息的SQL脚本（sosi.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群，学习最实用的数据库技术。
时间： 2024-09-29 11:00:02

收集统计信息的SQL脚本（sosi.sql）--崔华大师的相关文章

收集统计信息导致索引被监控

      对于索引的调整,我们可以通过Oracle提供的索引监控特性来跟踪索引是否被使用.尽管该特性并未提供索引使用的频度,但仍不失为我们参考的方式之一.然而,最近在Oracle 10.2.0.3中发现收集统计信息时导致索引也被监控,而不是用于sql查询引发的索引监控.如此这般,索引监控岂不是鸡肋?   1.基于Oracle 10g 收集统计信息索引被监控情形 scott@CNMMBO> select * from v$version where rownum<2; BANNER -----

Oracle 判断 并 手动收集 统计信息 脚本

CREATE OR REPLACE PROCEDURE SchameB.PRC_GATHER_STATS AUTHID CURRENT_USER IS BEGIN SYS.DBMS_STATS.GATHER_TABLE_STATS('SchName', 'TableName', CASCADE => TRUE); END; /   select owner,table_name,last_analyzed,num_rows from dba_tables where owner='SYSTEM'

oracle收集统计信息

什么是统计信息 统计信息主要是描述数据库中表,索引的大小,规模,数据分布状况等的一类信息.比如,表的行数,块数,平均每行的大小,索引的leaf blocks,索引字段的行数,不同值的大小等,都属于统计信息.CBO正是根据这些统计信息数据,计算出不同访问路径下,不同join 方式下,各种计划的成本,最后选择出成本最小的计划. 统计信息是存放在数据字段表中的,如dba_tab_statistics 如何搜集统计信息 统计信息搜集也是有多种方法,推荐大家使用DBMS_STATS 表来进行统计信息搜集及

Oracle收集统计信息之NO_INVALIDATE参数

Oracle收集统计信息之NO_INVALIDATE参数 Oracle统计量对于CBO执行是至关重要的.RBO是建立在数据结构的基础上的,DDL结构.约束会将SQL语句分为不同的成本结构等级.而CBO是在数据结构的基础上,加入数据表细粒度信息,将成本结构细化为成本cost值. 相对于数据表的DDL结构,统计量反映了当下数据表数据分布情况,可变性更强.我们经常遇到这样的场景,数据导入操作之后,原有一个运行良好的作业突然效率低下.当我们手工收集一下统计量之后,作业效率提升.这种现象也就是反映了统计量

Oracle并发（CONCURREMT）收集统计信息 (文档 ID 1555451.1)

 Oracle并发(CONCURREMT)收集统计信息 (文档 ID 1555451.1)   >                    >    >  >      >  >      >                          >   >>             >>>>>> >>>>>> >> > >>>>>&

DBMS_STATS收集统计信息的问题及解决

收集数据库的统计信息是dba工作的一部分,如果在数据快速增长的库上,统计信息如果收集的频率太慢,会对执行计划有一定的影响. 而对于逐渐客户饱和的系统来说,统计信息就可以很长时间收集或者尽量不收集. 对于统计信息的收集,如果是很大的表,收集100%也是不现实的,如果收集的百分比太小,统计信息又失真,对系统系统无疑是雪上加霜. 以上是我采用的方式,不一定对,可以参考.如果表的大小超过30G,算是很大的表了,统计信息的收集比例在30%到40%之间,我给了40%.以下类似. 巨型表(>30G),    

oracle 11g 收集统计信息的新特点介绍

对组合列创建统计信息 --检查表结构 select * from scott.emp where 1=2; --创建虚拟列 会输出虚拟列名 select dbms_stats.create_extended_stats('scott','emp','(empno,JOB)') from dual; select * from dba_tab_cols where column_name like 'SYS_STU%'; --查看列名 select * from dba_tab_cols a wh

两种方式来收集统计信息

一.手动1.利用analyze语句 analyze   table   table_name   estimate   statistics   sample  *  percent; analyze   table   table_name   compute   statistics   ;前者抽取*所代表的百分比来估算统计数据,而后者则是抽取整个表的信息,无疑后者比较精确,但是速度慢些 2.使用dbms_stats进行收集按照7楼calvin兄的建议,应该优先考虑使用dbms_stats.

MS SQL 统计信息浅析上篇

统计信息概念     统计信息是一些对象,这些对象包含在表或索引视图中一列或多列中的数据分布有关的统计信息.数据库查询优化器使用这些统计信息来估计查询结果中的基数或行 数. 通过这些基数估计,查询优化器可以生成高质量的执行计划. 例如,查询优化器可以使用基数估计选择索引查找运算符而不是耗费更多资源的索引扫描运算符,从而提高查询性能.[参考MSDN]     其实如果你以前没有接触过统计信息,你可以将其看做是数据库为了得到最优的执行计划,统计数据库里面表.索引等对象的一些数据,例如表的记录数.所有