【Oracle】oracle直方图

大数据

直方图
当某列数据分布不均衡，为了让CBO能生成最佳的执行计划，我们可能需要对表收集直方图，直方图最大的桶数(Bucket)是254。收集直方图是一个很耗时的过程，如无必要，千万别去收集直方图。Oracle的直方图有两种：一种是频率直方图(FREQUENCY HISTOGRAM)，当列中Distinct_keys 较少(小于254)，如果不手工指定直方图桶数(BUCKET)，Oracle就会自动的创建频率直方图，并且桶数(BUCKET)等于Distinct_Keys。一种是高度平衡直方图(HEIGHT BALANCED)，当列中Distinct_keys大于254，如果不手工指定直方图桶数(BUCKET)，Oracle就会自动的创建高度平衡直方图。直方图用在什么情况下？  列的值分布非常不均衡的时候，并且where条件中经常用到这个列。直方图都准吗？  不一定。如果一个字段distinct值的个数非常多，基本接近主键的distinct值的个数，就没必要做直方图，直方图也不一定100%准确。

1. SQL> drop table a;
   
2. 
   
3. 表已删除。
   
4. 
   
5. SQL> create table a as select * from dba_objects where rownum<=10000;
   
6. 
   
7. 表已创建。
   
8. 
   
9. SQL> @anatab                        --常规的表分析
   
10. 输入 ownname 的值:  ggs
    
11. 输入 tabname 的值:  a
    
12. 输入 estimate_percent 的值:  100
    
13. 输入 skewonly_repeat_auto 的值:  auto
    
14. 输入 degree 的值:  4
    
15. 
    
16. PL/SQL 过程已成功完成。
    
17. 
    
18. 已用时间:  00: 00: 00.26
    
19. SQL> @getcolstat                    --字段的直方图
    
20. 输入 owner 的值:  ggs
    
21. 输入 table_name 的值:  a
    
22. 
    
23. COLUMN_NAME        NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS LAST_ANALYZED   
    
24. ---------------- ---------- ----------- ----------- --------------------- --------------  
    
25. SECONDARY             10000           1         .01 NONE                1 28-7月 -14      
    
26. GENERATED             10000           2         .02 NONE                1 28-7月 -14      
    
27. TEMPORARY             10000           2         .02 NONE                1 28-7月 -14      
    
28. STATUS                10000           1         .01 NONE                1 28-7月 -14      
    
29. TIMESTAMP             10000         350         3.5 NONE                1 28-7月 -14      
    
30. LAST_DDL_TIME         10000         385        3.85 NONE                1 28-7月 -14      
    
31. CREATED               10000         303        3.03 NONE                1 28-7月 -14      
    
32. OBJECT_TYPE           10000          34         .34 NONE                1 28-7月 -14      
    
33. DATA_OBJECT_ID        10000        1836       18.36 NONE                1 28-7月 -14      
    
34. OBJECT_ID             10000       10000         100 NONE                1 28-7月 -14      
    
35. SUBOBJECT_NAME        10000          27         .27 NONE                1 28-7月 -14      
    
36. OBJECT_NAME           10000        7725       77.25 NONE                1 28-7月 -14      
    
37. OWNER                 10000           9         .09 NONE                1 28-7月 -14      
    
38. 
    
39. 已选择13行。
    
40. 
    
41. SQL>
    
42. SQL> select object_type,count(*) from a group by object_type;
    
43. 
    
44. OBJECT_TYPE           COUNT(*)
    
45. ------------------- ----------
    
46. INDEX                      946
    
47. JOB CLASS                    2
    
48. CONTEXT                      2
    
49. TYPE BODY                   82
    
50. PROCEDURE                   50
    
51. RESOURCE PLAN                3
    
52. RULE                         1
    
53. SCHEDULE                     1
    
54. TABLE PARTITION             52
    
55. WINDOW                       2
    
56. WINDOW GROUP                 1
    
57. TABLE                      841
    
58. TYPE                      1088
    
59. VIEW                      2953
    
60. LIBRARY                    113
    
61. FUNCTION                    68
    
62. TRIGGER                      5
    
63. PROGRAM                      3
    
64. CLUSTER                     10
    
65. SYNONYM                   2458
    
66. PACKAGE BODY               470
    
67. QUEUE                       21
    
68. CONSUMER GROUP               5
    
69. EVALUATION CONTEXT           8
    
70. RULE SET                    11
    
71. DIRECTORY                    2
    
72. UNDEFINED                    6
    
73. OPERATOR                    15
    
74. SEQUENCE                   102
    
75. LOB                        128
    
76. PACKAGE                    485
    
77. JOB                          6
    
78. INDEX PARTITION             59
    
79. LOB PARTITION                1
    
80. 
    
81. 已选择34行。
    
82. 
    
83. SQL> explain plan for select count(*) from a where object_type='INDEX';
    
84. 
    
85. 已解释。
    
86. 
    
87. SQL> @getplan
    
88. 'general,outline,starts'
    
89. 
    
90. Enter value for plan type:general
    
91. 
    
92. PLAN_TABLE_OUTPUT
    
93. ---------------------------------------------------------------------------------------------------------------------------------------
    
94. 
    
95. Plan hash value: 2223038180
    
96. 
    
97. ---------------------------------------------------------------------------
    
98. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    
99. ---------------------------------------------------------------------------
    
100. |   0 | SELECT STATEMENT   |      |     1 |     7 |    25   (0)| 00:00:01 |
     
101. |   1 |  SORT AGGREGATE    |      |     1 |     7 |            |          |
     
102. |*  2 |   TABLE ACCESS FULL| A    |   294 |  2058 |    25   (0)| 00:00:01 | --跟实际不一致，上面查出来的是946
     
103. ---------------------------------------------------------------------------
     
104. 
     
105. Predicate Information (identified by operation id):
     
106. ---------------------------------------------------
     
107. 
     
108.    2 - filter("OBJECT_TYPE"='INDEX')
     
109. SQL> select 10000/34 from dual;    --说明rows中的294是 估算值=总行数/字段distinct值的个数
     
110. 
     
111.   10000/34
     
112. ----------
     
113. 294.117647
     
114. 
     
115. 已选择 1 行。
     
116. 
     
117. SQL>
     
118. SQL> @anatab_col
     
119. 输入 owner 的值:  ggs
     
120. 输入 table_name 的值:  a
     
121. 输入 columns 的值:  object_type     --做object_type字段的直方图
     
122. 
     
123. PL/SQL 过程已成功完成。
     
124. 
     
125. SQL> explain plan for select count(*) from a where object_type='INDEX';
     
126. 
     
127. 已解释。
     
128. 
     
129. SQL> @getplan
     
130. 'general,outline,starts'
     
131. 
     
132. Enter value for plan type:general
     
133. 
     
134. PLAN_TABLE_OUTPUT
     
135. ---------------------------------------------------------------------------------------------------------------------------------------
     
136. 
     
137. Plan hash value: 2223038180
     
138. 
     
139. ---------------------------------------------------------------------------
     
140. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
     
141. ---------------------------------------------------------------------------
     
142. |   0 | SELECT STATEMENT   |      |     1 |     7 |    25   (0)| 00:00:01 |
     
143. |   1 |  SORT AGGREGATE    |      |     1 |     7 |            |          |
     
144. |*  2 |   TABLE ACCESS FULL| A    |   946 |  6622 |    25   (0)| 00:00:01 |   --这里返回的是真实的行数,做object_type字段的直方图后，执行计划非常准。
     
145. ---------------------------------------------------------------------------
     
146. 
     
147. Predicate Information (identified by operation id):
     
148. ---------------------------------------------------
     
149. 
     
150.    2 - filter("OBJECT_TYPE"='INDEX')
     
151. SQL>
     
152. SQL>
     
153. SQL>
     
154. SQL>
     
155. SQL> @getcolstat
     
156. 输入 owner 的值:  ggs
     
157. 输入 table_name 的值:  a
     
158. 
     
159. 
     
160. COLUMN_NAME         NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM       NUM_BUCKETS LAST_ANALYZED
     
161. ---------------- ---------- ----------- ----------- --------- ----------- --------------      
     
162. SECONDARY             10000           1         .01 NONE                1 28-7月 -14         
     
163. GENERATED             10000           2         .02 NONE                1 28-7月 -14         
     
164. TEMPORARY             10000           2         .02 NONE                1 28-7月 -14         
     
165. STATUS                10000           1         .01 NONE                1 28-7月 -14         
     
166. TIMESTAMP             10000         350         3.5 NONE                1 28-7月 -14         
     
167. LAST_DDL_TIME         10000         385        3.85 NONE                1 28-7月 -14         
     
168. CREATED               10000         303        3.03 NONE                1 28-7月 -14         
     
169. OBJECT_TYPE           10000          34         .34 FREQUENCY          34 28-7月 -14   --刚好等于distinct值
     
170. DATA_OBJECT_ID        10000        1836       18.36 NONE                1 28-7月 -14         
     
171. OBJECT_ID             10000       10000         100 NONE                1 28-7月 -14         
     
172. SUBOBJECT_NAME        10000          27         .27 NONE                1 28-7月 -14         
     
173. OBJECT_NAME           10000        7725       77.25 NONE                1 28-7月 -14         
     
174. OWNER                 10000           9         .09 NONE                1 28-7月 -14      
     
175.    
     
176. 已选择13行。
     
177. 
     
178. 
     
179. SQL> select count(distinct object_name) from a;    --总共才10000行，可以看出object_name的选择性是比较高的
     
180. 
     
181. COUNT(DISTINCTOBJECT_NAME)
     
182. --------------------------
     
183.                       7725
     
184. 
     
185. 已选择 1 行。
     
186. 
     
187. SQL> @anatab_col
     
188. 输入 owner 的值:  ggs
     
189. 输入 table_name 的值:  a
     
190. 输入 columns 的值:   object_name
     
191. 
     
192. PL/SQL 过程已成功完成。
     
193. 
     
194. SQL> @getcolstat
     
195. 输入 owner 的值:  ggs
     
196. 输入 table_name 的值:  a
     
197. 
     
198. COLUMN_NAME         NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM       NUM_BUCKETS LAST_ANALYZED  
     
199. ---------------- ---------- ----------- ----------- --------------- ----------- --------------  
     
200. SECONDARY             10000           1         .01 NONE                      1 28-7月 -14      
     
201. GENERATED             10000           2         .02 NONE                      1 28-7月 -14      
     
202. TEMPORARY             10000           2         .02 NONE                      1 28-7月 -14      
     
203. STATUS                10000           1         .01 NONE                      1 28-7月 -14      
     
204. TIMESTAMP             10000         350         3.5 NONE                      1 28-7月 -14      
     
205. LAST_DDL_TIME         10000         385        3.85 NONE                      1 28-7月 -14      
     
206. CREATED               10000         303        3.03 NONE                      1 28-7月 -14      
     
207. OBJECT_TYPE           10000          34         .34 FREQUENCY                34 28-7月 -14      
     
208. DATA_OBJECT_ID        10000        1836       18.36 NONE                      1 28-7月 -14      
     
209. OBJECT_ID             10000       10000         100 NONE                      1 28-7月 -14      
     
210. SUBOBJECT_NAME        10000          27         .27 NONE                      1 28-7月 -14      
     
211. OBJECT_NAME           10000        7725       77.25 HEIGHT BALANCED          75 28-7月 -14      
     
212. OWNER                 10000           9         .09 NONE                      1 28-7月 -14      
     
213. 
     
214. 已选择13行。
     
215. 
     
216. 
     
217. SQL> select count(*) from a where object_name like '%A%';
     
218. 
     
219.   COUNT(*)
     
220. ----------
     
221.       6404
     
222. 
     
223. 已选择 1 行。
     
224. 
     
225. SQL> explain plan for select count(*) from a where object_name like '%A%';
     
226. 
     
227. 已解释。
     
228. 
     
229. SQL> @getplan
     
230. 'general,outline,starts'
     
231. 
     
232. Enter value for plan type:general
     
233. 
     
234. PLAN_TABLE_OUTPUT
     
235. --------------------------------------------------------------------------------------------------------------
     
236. 
     
237. Plan hash value: 2223038180
     
238. 
     
239. ---------------------------------------------------------------------------
     
240. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
     
241. ---------------------------------------------------------------------------
     
242. |   0 | SELECT STATEMENT   |      |     1 |    19 |    25   (0)| 00:00:01 |
     
243. |   1 |  SORT AGGREGATE    |      |     1 |    19 |            |          |
     
244. |*  2 |   TABLE ACCESS FULL| A    |   500 |  9500 |    25   (0)| 00:00:01 |
     
245. ---------------------------------------------------------------------------
     
246. 
     
247. Predicate Information (identified by operation id):
     
248. ---------------------------------------------------
     
249. 
     
250.    2 - filter("OBJECT_NAME" LIKE '%A%')     --LIKE '%A%'对于cbo而言太复杂了，没有真正跑的话，cbo根本不知道真正返回多少行。
     
251. SQL>
     
252. 
     
253. 
     
254. 已选择13行。
     
255. 
     
256. 
     
257. SQL> col OBJECT_NAME for a30
     
258. SQL> select OBJECT_NAME,count(*) from a group by OBJECT_NAME having count(*)>3 order by count(*) desc;
     
259. 
     
260. OBJECT_NAME                      COUNT(*)
     
261. ------------------------------ ----------
     
262. DBMS_REPCAT_AUTH                        5
     
263. 
     
264. 已选择 1 行。
     
265. 
     
266. 
     
267. SQL> explain plan for select count(*) from a where OBJECT_NAME='DBMS_REPCAT_AUTH';
     
268. 
     
269. 已解释。
     
270. 
     
271. SQL> @getplan
     
272. 'general,outline,starts'
     
273. 
     
274. Enter value for plan type:general
     
275. 
     
276. PLAN_TABLE_OUTPUT
     
277. ---------------------------------------------------------------------------------------------------------
     
278. 
     
279. Plan hash value: 2223038180
     
280. 
     
281. ---------------------------------------------------------------------------
     
282. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
     
283. ---------------------------------------------------------------------------
     
284. |   0 | SELECT STATEMENT   |      |     1 |    19 |    25   (0)| 00:00:01 |
     
285. |   1 |  SORT AGGREGATE    |      |     1 |    19 |            |          |
     
286. |*  2 |   TABLE ACCESS FULL| A    |     1 |    19 |    25   (0)| 00:00:01 |
     
287. ---------------------------------------------------------------------------
     
288. 
     
289. Predicate Information (identified by operation id):
     
290. ---------------------------------------------------
     
291. 
     
292.    2 - filter("OBJECT_NAME"='DBMS_REPCAT_AUTH')   --这个不复杂了吧，rows=1，一样不准，直方图也不可能保证100%准确的
     
293. 
     
294. 所以说，并不是所有字段都适合做直方图。distinct值非常多的，根本不适合做直方图，默认的桶数也装不下。
     
295. 只有字段值倾斜非常严重，distinct值少，而且用到的sql中where条件包含了这个字段。如果sql中都没有用到这个字段，那也没必要做直方图，
     
296. 因为做直方图是非常cpu性能的。
     
297. 
     
298. @脚本
     
299. --anatab.sql
     
300. set timing on
     
301. BEGIN
     
302.   DBMS_STATS.GATHER_TABLE_STATS(ownname          => '&ownname',
     
303.                                 tabname          => '&tabname' ,
     
304.                                 estimate_percent => &estimate_percent,
     
305.                                 method_opt       => 'for all columns size &skewonly_repeat_auto',
     
306.                                 no_invalidate    => FALSE,
     
307.                                 degree           => °ree,
     
308.                                 cascade          => TRUE);
     
309. END;
     
310. /
     
311. set timing off
     
312. 
     
313. 
     
314. --anatab_col.sql
     
315. BEGIN
     
316.   DBMS_STATS.GATHER_TABLE_STATS(ownname          => '&owner',
     
317.                                 tabname          => '&table_name',
     
318.                                 estimate_percent => 100,
     
319.                                 method_opt       => 'for columns &columns ',  --such as:col1,col2,col3...
     
320.                                 no_invalidate    => FALSE,
     
321.                                 degree           => 4,
     
322.                                 granularity      => 'ALL',
     
323.                                 cascade          => TRUE);
     
324. END;
     
325. /
     
326. 
     
327. --getcolstat.sql
     
328. col COLUMN_NAME for a30
     
329. select a.column_name,
     
330.        b.num_rows,
     
331.        a.num_distinct Cardinality,
     
332.        round(a.num_distinct / b.num_rows * 100, 2) selectivity,
     
333.        a.histogram,
     
334.        a.num_buckets,
     
335.        a.last_analyzed
     
336.   from dba_tab_col_statistics a, dba_tables b
     
337. where a.owner = b.owner
     
338.    and a.table_name = b.table_name
     
339.    and a.owner = upper('&owner')
     
340.    and a.table_name = upper('&table_name');  
     
341. 
     
342. 
     
343. 
     
344. --getplan.sql
     
345. set feedback off
     
346. pro 'general,outline,starts'
     
347. pro
     
348. acc type prompt 'Enter value for plan type:' default 'general'
     
349. select * from table(dbms_xplan.display) where '&&type'='general';
     
350. select * from table(dbms_xplan.display(null, null,'advanced -projection')) where '&&type'='outline';
     
351. SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')) where '&&type'='starts';
     
352. set feedback on
     
353. undef type
     

复制代码