Object Statistics - Troubleshooting Oracle Performance - page 215

SQL> EXPLAIN PLAN SET STATEMENT_ID '107' FOR SELECT * FROM t WHERE val2 = 107;
SQL> EXPLAIN PLAN SET STATEMENT_ID '109' FOR SELECT * FROM t WHERE val2 = 109;
SQL> EXPLAIN PLAN SET STATEMENT_ID '111' FOR SELECT * FROM t WHERE val2 = 111;
SQL> SELECT statement_id, cardinality
2 FROM plan_table
3 WHERE id = 0
4 ORDER BY statement_id;
STATEMENT_ID CARDINALITY
------------ -----------
096 1
098 20
100 40
103.5 50
107 40
109 20
111 1
Considering these essential characteristics of the two types of histograms, it's apparent that frequency histograms
are more accurate than height-balanced histograms. The main problem with height-balanced histograms is not only
that the precision is lower, but also that sometimes it might be by chance that a value is recognized as popular or not.
For example, in the histogram illustrated in Figure 8-4 , the split point between buckets 4 and 5 occurs very close to the
point where the value changes from 105 to 106.
Therefore, even a small change in the data distribution might lead to a different histogram and to different
estimations. The following example, where only 20 rows are updates (this is 2% of the total number of rows), illustrates
such a case:
SQL> UPDATE t SET val2 = 105 WHERE val2 = 106 AND rownum <= 20;
SQL> REMARK at this point object statistics are gathered
SQL> SELECT endpoint_value, endpoint_number
2 FROM user_tab_histograms
3 WHERE table_name = 'T'
4 AND column_name = 'VAL2'
5 ORDER BY endpoint_number;
ENDPOINT_VALUE ENDPOINT_NUMBER
-------------- ---------------
101 0
104 1
105 4
106 5
SQL> EXPLAIN PLAN SET STATEMENT_ID '101' FOR SELECT * FROM t WHERE val2 = 101;
SQL> EXPLAIN PLAN SET STATEMENT_ID '102' FOR SELECT * FROM t WHERE val2 = 102;
SQL> EXPLAIN PLAN SET STATEMENT_ID '103' FOR SELECT * FROM t WHERE val2 = 103;
SQL> EXPLAIN PLAN SET STATEMENT_ID '104' FOR SELECT * FROM t WHERE val2 = 104;
SQL> EXPLAIN PLAN SET STATEMENT_ID '105' FOR SELECT * FROM t WHERE val2 = 105;