Database Reference
In-Depth Information
From the output, you can confirm that not only are the object statistics correct but that the data is also strongly
skewed. As a result, a histogram is absolutely essential for correct estimations. With the following query, you can
confirm that no histogram exists in this case:
SQL> SELECT histogram, num_buckets
2 FROM user_tab_col_statistics
3 WHERE table_name = 'T1' AND column_name = 'COL1';
HISTOGRAM NUM_BUCKETS
--------- -----------
NONE 1
After gathering the missing histogram, the query optimizer managed to correctly estimate cardinalities and, as a
result, considered another execution plan to be the most efficient:
---------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |
|* 2 | HASH JOIN | | 1 | 80000 | 75808 |
|* 3 | TABLE ACCESS FULL| T1 | 1 | 80000 | 80016 |
| 4 | TABLE ACCESS FULL| T2 | 1 | 151K| 151K|
---------------------------------------------------------------
2 - access("T1"."ID"="T2"."ID")
3 - filter("T1"."COL1"=666)
Note that when the
wrong_estimations.sql
script is executed in version 12.1 without disabling adaptive
execution plans, the query optimizer generates an adaptive execution plan and, as a result, automatically detects at
runtime that, for this particular query, a hash join is better than a nested loop.
Restriction Not Recognized
I must warn you that the check presented in the previous section is superior to this one. I usually use this second
check only in addition to the first one. The idea of this check is to verify whether the query optimizer correctly
recognized the restriction in the SQL statement and, as a result, applied it as soon as possible. In other words, you
check whether the execution plan leads to unnecessary processing.
Let me illustrate this concept with an example based on the following excerpt of the output produced by the
restriction_not_recognized.sql
script. From it, you can see that the query optimizer decided to start joining the
t1
and
t2
tables. This first join returned a result set of 40,000 rows. Later, the result set was joined to the
t3
table. A
result set of only 100 rows was generated, even though the operation reading the
t3
table returned 80,000 rows. This
simply means that the query optimizer didn't recognize the restriction and applied it too late when a lot of processing
was already being performed. Estimating join cardinalities is,
en passant
, one of the most difficult tasks the query
optimizer has to perform:
SELECT count(t1.pad), count(t2.pad), count(t3.pad)
FROM t1, t2, t3
WHERE t1.id = t2.t1_id AND t2.id = t3.t2_id
