This is the final part of my 4 part series on Optimizer. The previous 3 parts can be viewed clicking the following links:
In this blog, I will go through the 12c enhancements. Before we go through the 12c enhancement, let me also briefly cover the real life example that motivated me to write this series. I covered this in Part III. In that post, I missed to paste the relevant output of 10053 trace file, so let me take this up again in this post.
The problem query and it’s run time plan is pasted below.
select count(*) from nca.s_p_attributes a1
WHERE a1.value='olwassenen';
COUNT(*)
------------
591168
SQL> select plan_table_output from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
----------------------------------------------------------
SQL_ID 79dfpvydpk710, child number 0
-------------------------------------
select count(*) from nca.s_p_attributes a1 WHERE
a1.value='olwassenen’
----------------------------------------------------------
| Id | Operation | Name | Rows |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1|
|* 2 | INDEX SKIP SCAN | SP_P_IND3 | 8|
----------------------------------------------------------
As can be seen, the optimizer calculation is way out (Actuals = 591168 v/s Assumptions=8). Believe me, the table and the column used in the WHERE predicate has a height balanced histogram on it. This will be clearly visible in the 10053 trace. The relevant portion of the 10053 trace is as under.
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for S_P_ATTRIBUTES[A1]
Column (#3):
NewDensity:0.000000, OldDensity:0.001202 BktCnt:254, PopBktCnt:122, PopValCnt:20, NDV:35078144
Column (#3): VALUE(
AvgLen: 11 NDV: 35078144 Nulls: 0 Density: 0.000000
Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 153
Table: S_P_ATTRIBUTES Alias: A1
Card: Original: 541600373.000000 Rounded: 8 Computed: 8.02 Non Adjusted: 8.02
This was a Non-Popular value and therefore, the selectivity calculation for Non-Popular value (NewDensity in 10053) is as under:
[(NPBKTCNT)/(BKTCNT * (NDV – POPVALCNT))]
[(254-122)/(254 * (35078144-20))] = 132/(254 * 35078124) = 132/8909843496 = 0.0000000148
The calculated selectivity multiplied by the number of rows in the table is the expected cardinality i.e. 0.0000000148*541600373=8.
Now, let’s briefly discuss the 12c Enhancements. Oracle Database 12c introduced additional histograms. These are : Top-n Frequency and Hybrid Histograms. There are few criteria’s for these histograms to be generated. The criteria is :
Following variables are used -
n - Number of Buckets either explicitly specified or left to default (254)
NDV - Number of Distinct Values in the Column
p - Internal Percentage, which is calculated as (1-(1/n))*100
If Data Skew Observed ?
If NDV > n ?
If estimate_percent => auto_sample_size ?
if %of rows for topn frequent values >= p ?
generate topn frequency histogram;
else if
generate hybrid histogram;
end if;
else if
generate height balanced histogram;
end if;
else if
generate frequency histogram;
end if;
end if;
For the simplicity, I have color coded each IF-ELSE-ENDIF statement. From the preceding pseudo-code, it is clear that to generate 12c specific histograms, we need to specify estimate_percent as auto_sample_size, which is a default. So, if your statistics gathering policy states manual percentage for estimate, then it won’t gather these new histograms. The new auto_sample_size algorithm has improved a lot and therefore, you can safely change the manual estimate to auto.
The value of p requires some explanation. It is calculated as (1-(1/n))*100, where n is the number of buckets specified during stats gathering. Consider following 4 examples :
Example 1 : PROD_ID has 72 Distinct Values. As the bucket size specified is 100, which is more than the number of distinct values, Optimizer will generate Frequency Histogram.
execute dbms_stats.gather_table_stats(ownname=>'SH',tabname=>'SALES',method_opt=>'for columns PROD_ID size 100');
Example 2 : PROD_ID has 72 Distinct Values. In this case, the number of bucket (n) is less than the number of distinct values (72). However, the script explicitly estimates the percent as 30 and therefore, it will generate Height Balanced Histogram.
execute dbms_stats.gather_table_stats(ownname=>'SH',tabname=>'SALES',method_opt=>'for columns PROD_ID size 50', estimate_percent=>30);
Example 3 : PROD_ID has 72 Distinct Values. In this case, the number of bucket (n) is less than the number of distinct values (72). No estimate_percent specified means, it is left to default, which is AUTO_SAMPLE_SIZE. In this case, the decision on Hybrid or Top-N Frequency will be basis the value of p. The calcution for p is (1-(1/50))*100 = 98%. This means, if the top 50 distinct PROD_ID occupy more than 98% rows then, it will be a TopN Frequency, else it will be a Hybrid Histogram. We shall see this in action.
execute dbms_stats.gather_table_stats(ownname=>'SH',tabname=>'SALES',method_opt=>'for columns PROD_ID size 50');
Example : Our own table (TEST_SELECTIVITY) that we created for testing purpose in Optimizer – Part I. In this case, I am generating statistics using method_opt=>’for all columns size AUTO’. The columns will be considered basis the data available in COL_USAGE$, which is populated post execution of any query against a table and column. Column AMOUNT_SOLD has 636 Distinct values, which is higher than 254 (default buckets). The value of n is 254 and p will be (1-(1/254))*100 = 99.61. This means, if the number of rows occupied by top 254 distinct values is more than 99.61%, then it will be TopN Frequency, else it will be Hybrid.
execute dbms_stats.gather_table_stats(user,'TEST_SELECTIVITY',method_opt=>'FOR ALL COLUMNS SIZE AUTO');
select column_id, column_name, num_distinct, num_nulls,
density, histogram
from dba_tab_columns
where owner='SCOTT'
and table_name='TEST_SELECTIVITY'
and column_name='AMOUNT_SOLD'
order by 1;
COLUMN_ID COLUMN_NAME NUM_DISTINCT NUM_NULLS DENSITY HISTOGRAM
---------- ------------------------------ ------------ ---------- ---------- -------------------------
6 AMOUNT_SOLD 636 0 .000361 HYBRID
SQL> select round((1-(1/254))*100,2) value_p from dual;
VALUE_P
----------
99.61
## I have 800000 Rows in the table. So, 99.61% of 800000 is 796850.
## My Statistics Gathering command generated HYBRID, which means,
## the top 254 distinct values occupy less than 99.61% or less than 796850 rows
SQL> select round((1-(1/254))*800000,0) value_p from dual;
VALUE_P
----------
796850
## Query to check Running Count and %Age
## My Top 254 values occupy 776524, which is less than 796850
## My Top 254 values occupy 97.09%, which is less than 99.61%
## Therefore, the script generated HYBRID Histogram.
SQL> select rownum, amount_sold, cnt, running_cnt, running_perc from (
2 select amount_sold, cnt, running_cnt,
3 sum(r_perc) over(order by cnt desc) running_perc from (
4 select amount_sold, cnt, sum(cnt) over(order by cnt desc) running_cnt,
5 round(ratio_to_report(cnt) over()*100,2) r_perc from (
6 select amount_sold, count(*) cnt from test_selectivity group by amount_sold order by 2 desc))
7 order by cnt desc);
ROWNUM AMOUNT_SOLD CNT RUNNING_CNT RUNNING_PERC
---------- ----------- ---------- ----------- ------------
1 10 33600 33600 4.2
2 9 31964 65564 8.2
3 11 28850 94414 11.81
4 13 26968 121382 15.18
5 47 26934 148316 18.55
6 48 26114 174430 21.81
7 8 24906 199336 24.92
8 49 24239 223575 27.95
9 46 18784 242359 30.3
10 23 17014 259373 32.43
11 21 16965 276338 34.55
12 24 16246 292584 36.58
13 22 15954 308538 38.57
14 25 15782 324320 40.54
15 51 14906 339226 42.4
16 30 12932 352158 44.02
17 7 12030 364188 45.52
18 26 11958 376146 47.01
19 17 11585 387731 48.46
20 12 11547 399278 49.9
21 52 11344 410622 51.32
22 28 11227 421849 52.72
23 29 10728 432577 54.06
24 14 10180 442757 55.33
25 53 10059 452816 56.59
26 31 9907 462723 57.83
27 19 9682 472405 59.04
28 39 9571 481976 60.24
29 33 9145 491121 61.38
30 38 9027 500148 62.51
31 54 8468 508616 63.57
32 42 8177 516793 64.59
33 32 8017 524810 65.59
34 27 7651 532461 66.55
35 34 7617 540078 67.5
36 63 6607 546685 68.33
37 20 6582 553267 69.15
38 64 6575 559842 69.97
39 56 6204 566046 70.75
40 40 6060 572106 71.51
41 57 5723 577829 72.23
42 50 5511 583340 72.92
43 35 5388 588728 73.59
44 15 5219 593947 74.24
45 60 5195 599142 74.89
46 36 5062 604204 75.52
47 58 5031 609235 76.15
48 16 4903 614138 76.76
49 41 4831 618969 77.36
50 18 4562 623531 77.93
51 62 4542 628073 78.5
52 59 4421 632494 79.05
53 45 4311 636805 79.59
54 43 3946 640751 80.08
55 65 3625 644376 80.53
56 55 3372 647748 80.95
57 123 2852 650600 81.31
58 44 2327 652927 81.6
59 126 2296 655223 81.89
60 203 2286 657509 82.18
61 115 2200 659709 82.46
62 61 2187 661896 82.73
63 211 2105 664001 82.99
64 213 2032 666033 83.24
65 91 2022 668055 83.49
66 128 1953 670008 83.73
67 113 1837 671845 83.96
68 629 1799 673644 84.18
69 73 1656 675300 84.39
70 100 1641 676941 84.6
71 72 1596 678537 84.8
72 602 1577 680114 85
73 37 1547 681661 85.19
74 101 1533 683194 85.38
75 66 1450 684644 85.56
76 127 1402 686046 85.74
77 71 1333 687379 85.91
78 74 1314 688693 86.07
79 163 1243 689936 86.23
80 94 1237 691173 86.38
81 210 1214 692387 86.53
82 121 1139 693526 86.67
83 160 1127 694653 86.81
84 69 1111 695764 86.95
85 70 1106 696870 87.09
86 225 1101 697971 87.23
87 168 1097 699068 87.37
88 116 1088 700156 87.51
89 307 1069 701225 87.64
90 152 1063 702288 87.77
91 134 1055 703343 87.9
92 170 1034 704377 88.03
93 77 1020 705397 88.16
94 97 1012 706409 88.29
95 151 1011 707420 88.42
96 594 992 708412 88.54
97 630 919 709331 88.65
98 67 906 711143 88.87
99 214 906 711143 88.87
100 76 905 712048 88.98
101 96 897 712945 89.09
102 180 864 713809 89.2
103 136 855 714664 89.31
104 208 847 715511 89.42
105 114 835 716346 89.52
106 135 829 717175 89.62
107 610 809 717984 89.72
108 167 803 718787 89.82
109 202 787 719574 89.92
110 133 784 720358 90.02
111 600 779 721137 90.12
112 120 774 721911 90.22
113 84 771 722682 90.32
114 178 769 723451 90.42
115 117 764 724215 90.52
116 95 761 724976 90.62
117 1000 758 725734 90.71
118 306 735 726469 90.8
119 199 734 727203 90.89
120 125 718 727921 90.98
121 303 688 728609 91.07
122 112 686 729295 91.16
123 639 679 729974 91.24
124 179 670 730644 91.32
125 228 665 731309 91.4
126 177 663 731972 91.48
127 1577 646 732618 91.56
128 159 630 733248 91.64
129 539 628 733876 91.72
130 205 623 734499 91.8
131 89 614 735113 91.88
132 90 599 735712 91.95
133 216 582 736294 92.02
134 547 579 736873 92.09
135 1050 564 737437 92.16
136 557 557 737994 92.23
137 119 552 738546 92.3
138 99 536 739082 92.37
139 1016 528 739610 92.44
140 78 521 740131 92.51
141 93 510 740641 92.57
142 217 485 741126 92.63
143 304 481 741607 92.69
144 1053 480 742087 92.75
145 1068 477 742564 92.81
146 1496 476 743040 92.87
147 1014 475 743515 92.93
148 206 474 743989 92.99
149 1004 468 744457 93.05
150 1065 462 744919 93.11
151 1566 461 745380 93.17
152 156 458 745838 93.23
153 207 457 746295 93.29
154 140 452 746747 93.35
155 142 451 747198 93.41
156 1260 448 747646 93.47
157 552 447 748093 93.53
158 1599 442 748535 93.59
159 222 433 748968 93.64
160 212 424 749392 93.69
161 1698 422 749814 93.74
162 215 416 750230 93.79
163 1240 415 750645 93.84
164 85 408 751053 93.89
165 155 403 751456 93.94
166 148 401 751857 93.99
167 554 399 752256 94.04
168 900 395 752651 94.09
169 556 391 753042 94.14
170 1118 388 753430 94.19
171 75 384 753814 94.24
172 88 382 754196 94.29
173 302 375 754571 94.34
174 1057 372 754943 94.39
175 1029 368 755311 94.44
176 1321 366 755677 94.49
177 1109 365 756042 94.54
178 79 356 756398 94.58
179 102 354 756752 94.62
180 1729 344 757096 94.66
181 204 341 757437 94.7
182 659 339 758454 94.82
183 195 339 758454 94.82
184 1551 339 758454 94.82
185 98 337 758791 94.86
186 169 323 759114 94.9
187 562 322 759436 94.94
188 1195 320 759756 94.98
189 1215 318 760074 95.02
190 936 317 760391 95.06
191 68 313 760704 95.1
192 187 310 761014 95.14
193 548 309 761632 95.22
194 1714 309 761632 95.22
195 1217 308 761940 95.26
196 468 307 762554 95.34
197 1297 307 762554 95.34
198 1656 305 762859 95.38
199 608 303 763162 95.42
200 124 301 763463 95.46
201 150 299 763762 95.5
202 1206 294 764056 95.54
203 181 293 764642 95.62
204 106 293 764642 95.62
205 288 288 764930 95.66
206 1078 286 765216 95.7
207 158 283 765499 95.74
208 1633 282 765781 95.78
209 154 277 766058 95.81
210 1556 274 766606 95.87
211 1061 274 766606 95.87
212 161 273 766879 95.9
213 1176 270 767149 95.93
214 914 268 767417 95.96
215 490 267 767684 95.99
216 289 261 767945 96.02
217 1758 256 768713 96.11
218 1353 256 768713 96.11
219 1045 256 768713 96.11
220 1508 255 768968 96.14
221 196 249 769217 96.17
222 183 248 769465 96.2
223 1674 246 769711 96.23
224 1237 244 769955 96.26
225 1531 243 770198 96.29
226 1421 242 771166 96.41
227 118 242 771166 96.41
228 544 242 771166 96.41
229 1501 242 771166 96.41
230 1304 235 771636 96.47
231 1076 235 771636 96.47
232 842 232 771868 96.5
233 561 230 772098 96.53
234 143 228 772326 96.56
235 632 222 772548 96.59
236 193 216 772980 96.65
237 305 216 772980 96.65
238 596 215 773195 96.68
239 194 209 773613 96.74
240 1003 209 773613 96.74
241 131 207 773820 96.77
242 1553 206 774026 96.8
243 937 202 774430 96.86
244 580 202 774430 96.86
245 1125 201 774631 96.89
246 200 194 774825 96.91
247 524 193 775018 96.93
248 947 192 775594 96.99
249 141 192 775594 96.99
250 1709 192 775594 96.99
251 1488 189 775783 97.01
252 1334 187 775970 97.03
253 281 186 776156 97.05
254 1227 184 776524 97.09 <-- Top 254 Distinct Values
255 1703 184 776524 97.09
.....
.....
.....
.....
624 1231 1 800000 99.94
625 1177 1 800000 99.94
626 1638 1 800000 99.94
627 1189 1 800000 99.94
628 162 1 800000 99.94
629 81 1 800000 99.94
630 647 1 800000 99.94
631 584 1 800000 99.94
632 619 1 800000 99.94
633 1036 1 800000 99.94
634 708 1 800000 99.94
635 518 1 800000 99.94
636 522 1 800000 99.94
Next, I gather statistics on PRODUCTS table under SH Schema. This table has 72 rows with 22 Distinct values in PROD_SUBCATEGORY_ID column. First, I gather statistics using 10 Buckets. Optimizer will generate HYBRID Histogram, as the number of rows occupied by Top 10 Distinct values is less than than the value of p (1-(1/10)) = 90%. Then, I will generate using 18 Buckets and this time, it will be TopN Frequency, as the number of rows occupied by Top 18 Distinct Values is more than the value of p (1-(1/18)) = 94%.
SQL> select table_name, num_rows from dba_tables where table_name='PRODUCTS';
TABLE_NAME NUM_ROWS
------------------------------ ----------
PRODUCTS 72
SQL> select column_name, num_distinct from dba_tab_columns
2 where table_name='PRODUCTS'
3 and owner='SH'
4 and column_name='PROD_SUBCATEGORY_ID';
COLUMN_NAME NUM_DISTINCT
------------------------------ ------------
PROD_SUBCATEGORY_ID 22
SQL> select 1-(1/10) from dual;
1-(1/10)
----------
.9
SQL> select round((1-(1/10))*72,0) from dual;
ROUND((1-(1/10))*72,0)
----------------------
65
SQL> compute sum of running_sum on report
SQL> break on report
SQL> select rownum, prod_subcategory_id, cnt, running_cnt, running_perc from (
2 select prod_subcategory_id, cnt,
3 sum(cnt) over(order by cnt desc) running_cnt,
4 sum(r_perc) over(order by cnt desc) running_perc from (
5 select prod_subcategory_id, cnt, round(ratio_to_report(cnt) over()*100,2) r_perc from (
6 select prod_subcategory_id, count(*) cnt from products group by prod_subcategory_id order by 2 desc))
7 order by cnt desc);
ROWNUM PROD_SUBCATEGORY_ID CNT RUNNING_CNT RUNNING_PERC
---------- ------------------- ---------- ----------- ------------
1 2014 8 8 11.11
2 2055 7 15 20.83
3 2032 6 27 37.49
4 2054 6 27 37.49
5 2056 5 47 65.25
6 2051 5 47 65.25
7 2031 5 47 65.25
8 2042 5 47 65.25
9 2036 4 51 70.81
10 2043 3 54 74.98 <-- Less than 90% or 65 Rows (HYBRID)
11 2033 2 66 91.66
12 2035 2 66 91.66
13 2053 2 66 91.66
14 2012 2 66 91.66
15 2013 2 66 91.66
16 2034 2 66 91.66
17 2021 1 72 100
18 2011 1 72 100
19 2044 1 72 100
20 2041 1 72 100
21 2022 1 72 100
22 2052 1 72 100
22 rows selected.
## STATS WITH BUCKET 10
exec dbms_stats.gather_table_stats(ownname=>'SH',tabname=>'PRODUCTS',method_opt=>'for columns prod_subcategory_id size 10');
SQL> select column_id, column_name, num_distinct, num_nulls,
2 density, histogram
3 from dba_tab_columns
4 where owner='SH'
5 and table_name='PRODUCTS'
6 and column_name='PROD_SUBCATEGORY_ID';
COLUMN_ID COLUMN_NAME NUM_DISTINCT NUM_NULLS DENSITY HISTOGRAM
---------- ------------------------------ ------------ ---------- ---------- -------------------------
5 PROD_SUBCATEGORY_ID 22 0 .044976 HYBRID
SQL> select (1-(1/18)) from dual;
(1-(1/18))
----------
.944444444
SQL> select round((1-(1/18))*72,0) from dual;
ROUND((1-(1/18))*72,0)
----------------------
68
SQL> select rownum, prod_subcategory_id, cnt, running_cnt, running_perc from (
2 select prod_subcategory_id, cnt,
3 sum(cnt) over(order by cnt desc) running_cnt,
4 sum(r_perc) over(order by cnt desc) running_perc from (
5 select prod_subcategory_id, cnt, round(ratio_to_report(cnt) over()*100,2) r_perc from (
6 select prod_subcategory_id, count(*) cnt from products group by prod_subcategory_id order by 2 desc))
7 order by cnt desc);
ROWNUM PROD_SUBCATEGORY_ID CNT RUNNING_CNT RUNNING_PERC
---------- ------------------- ---------- ----------- ------------
1 2014 8 8 11.11
2 2055 7 15 20.83
3 2032 6 27 37.49
4 2054 6 27 37.49
5 2056 5 47 65.25
6 2051 5 47 65.25
7 2031 5 47 65.25
8 2042 5 47 65.25
9 2036 4 51 70.81
10 2043 3 54 74.98
11 2033 2 66 91.66
12 2035 2 66 91.66
13 2053 2 66 91.66
14 2012 2 66 91.66
15 2013 2 66 91.66
16 2034 2 66 91.66
17 2021 1 72 100
18 2011 1 72 100 <-- More than 94% or 68 Rows
19 2044 1 72 100
20 2041 1 72 100
21 2022 1 72 100
22 2052 1 72 100
22 rows selected.
SQL> exec dbms_stats.gather_table_stats(ownname=>'SH',tabname=>'PRODUCTS',method_opt=>'for columns prod_subcategory_id size 18');
PL/SQL procedure successfully completed.
SQL> select column_id, column_name, num_distinct, num_nulls,
2 density, histogram
3 from dba_tab_columns
4 where owner='SH'
5 and table_name='PRODUCTS'
6 and column_name='PROD_SUBCATEGORY_ID';
COLUMN_ID COLUMN_NAME NUM_DISTINCT NUM_NULLS DENSITY HISTOGRAM
---------- ------------------------------ ------------ ---------- ---------- -------------------------
5 PROD_SUBCATEGORY_ID 22 0 .006944444 TOP-FREQUENCY
In this blog, I have tried to explain the underlying calculation by way of which 12c introduced histograms are generated. Hope, the explanation were clear. As always, comments are welcome.
