Oracle里的统计信息

目次

一、什么是统计信息
二、oracle收集和检察统计信息的方法
1、使用analyze命令收集统计信息
2、使用dbms_stats包收集统计信息
3、analyze和dbms_stats的区别
4、检察统计信息

---

一、什么是统计信息

oracle数据库里的统计信息是如下的一组数据：他们存储在数据字典里，且从多个维度描述了oracle数据库数据对象的详细信息。
oracle数据库里的统计信息主要分为以下6种情况：
（1）表的统计信息。
（2）列的统计信息。
（3）索引的统计信息。
（4）体系统计信息。
（5）数据字典统计信息。
（6）内部对象统计信息。
二、oracle收集和检察统计信息的方法

oracle数据库收集统计信息一样平常有以下2种方法：
（1）analyze命令。
（2）dbms_stats包。
针对以上6种统计信息，此中“表的统计信息”，“索引统计信息”，“列统计信息”，“数据字典统计信息”使用analyze或dbms_stats包收集均可以，但是“体系统计信息”和“内部对象统计信息”必须要dbms_stats包来收集才可以。
1、使用analyze命令收集统计信息

从oralce7开始，analyze命令就用来收集表、索引和列的统计信息。从oracle10g开始，创建索引后oracle会自动为您收集目标索引统计信息。analyze命令收集统计信息不会抹掉之间analyze效果。
创建测试表：
SQL>create table t1 as select * from dba_objects;
SQL>create index idx_t1 on t1(object_id);
（1）analyze索引统计信息：
SQL>analyze index idx_t1 delete statistics;
（2）对表收集统计信息，而且以估算模式，采样比为15%：
SQL>analyze table t1 estimate statistics sample 15 percent for table;
（3）对表收集统计信息，以统计模式：
SQL>analyze table t1 compute statistics for table;
（4）对列收集统计信息，以盘算模式：
SQL>analyze table t1 compute statistics for columns object_name,object_id;
（5）以盘算模式对表和列同时收集统计信息：
SQL>analyze table t1 compute statistics for t1 for columns object_name,object_id;
（6）以盘算模式对索引收集统计信息：
SQL>analyze index idx_t1 compute statistics;
（7）删除表、表上的索引、表的全部列的统计信息：
SQL>analyze table t1 delete statistics;
（8）以盘算模式，同时收集表、表上的列、表上的索引的统计信息：
SQL>analyze table t1 compute statistics;
2、使用dbms_stats包收集统计信息

从oracle 8.1.5开始，dbms_stats包就被广泛用于统计信息的收集，用dbms_stats包收集统计信息也是oracle官方推荐的方式。在收集CBO所必要的统计信息方面，可以简朴的将dbms_stats包明白成是analyze命令的增强版。
DBMS_STATS包最常见的4个存储过程：
（1）dbms_stats.gather_table_stats：用于收集目标表，目标表上列及目标表上索引的统计信息。
（2）dbms_stats.gather_index_stats：用于收集指定索引的统计信息。
（3）dbms_stats.gather_schema_stats：用于收集schema下全部对象的统计信息。
（4）dbms_stats.gather_database_stats：用于收集全库统计对象的统计信息。
以下是dbms_stats包的详细用法：
（1）对表收集统计信息，而且以估算模式，采样比为15%：
SQL>exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T1',estimate_percent=>15,method_opt=>'FOR TABLE',cascade=>FALSE);
注意：method_opt参数指定了FOR TABLE不是在全部版本oracle下都是好用的。
（2）对表收集统计信息，以盘算模式：
SQL>exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T1',estimate_percent=>100,method_opt=>'FOR TABLE',cascade=>FALSE);
或
SQL>exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T1',estimate_percent=>NULL,method_opt=>'FOR TABLE',cascade=>FALSE);
（3）对列收集统计信息，以盘算模式：
SQL>exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T1',estimate_percent=>100,method_opt=>'FOR ALL CULUMNS SIZE 1 OBJECT_NAME OBJECT_ID',cascade=>FALSE);
注意：以上方法收集了列objec_name、object_id的统计信息，同时也会收集表的统计信息。
（4）以盘算模式对索引收集统计信息：
SQL>exec dbms_stats.gather_index_stats(ownname=>'SCOTT',indname=>'INDEX_T1',estimate_percent=>100);
（5）删除表、表上的索引、表的全部列的统计信息：
SQL>exec dbms_stats.delete_table_stats(ownname=>'SCOTT',tabname=>'T1');
（6）以盘算模式，同时收集表、表上的列、表上的索引的统计信息：
SQL>exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T1',estimate_percent=>15 ,cascade=>TRUE);
3、analyze和dbms_stats的区别

（1）analyze命令不能精确的收集分区表的统计信息，而dbms_stats包缺可以。
（2）analyze命令不能以并行收集统计信息，而dbms_stats包缺可以。
SQL>exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T1',estimate_percent=>100, cascade=>FALSE,degree=>4);
（3）dbms_stats包只能收集与CBO相关的统计信息，而与CBO无关的额外信息，好比行迁徙/行链接的数量（chain_cnt），校验表和索引的结构信息等，dbms_stats包就无能为力了，而analyze命令是可以用来分析和收集上述额外信息。好比：
SQL>analyze table XXX list chained rows into YYY; --用来分析和收集行迁徙/行链接的数量。
SQL>analyze index XXX validate structure; --用来分析索引结构。
4、检察统计信息

oracle里的统计信息存储在数据字典表中，可以通过脚原来查询对象的统计信息。
sosi.sh脚本如下（可以检察表、索引、列的统计信息）：

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

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。