Here’s a very simple query, hinted to follow a particular execution plan.
select
/*+ ordered use_nl(t1) index(t1) */
t2.n1, t1.n2
from
t2,t1
where
t2.n2 = 45
and t2.n1 = t1.n1
;
And here’s the execution plan – from 9.2.0.8 autotrace (with CPU costing disabled)
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=44 Card=225 Bytes=3600)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2 Card=15 Bytes=120)
2 1 NESTED LOOPS (Cost=44 Card=225 Bytes=3600)
3 2 TABLE ACCESS (FULL) OF 'T2' (Cost=14 Card=15 Bytes=120)
4 2 INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=1 Card=15)
And here’s the same test run on 10.2.0.1 (with CPU costing disabled)
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 225 | 3600 | 37 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 15 | 120 | 2 |
| 2 | NESTED LOOPS | | 225 | 3600 | 37 |
|* 3 | TABLE ACCESS FULL | T2 | 15 | 120 | 14 |
|* 4 | INDEX RANGE SCAN | T1_I1 | 15 | | 1 |
---------------------------------------------------------------------
And here it is again on 10.2.0.3 (with CPU costing disabled).
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 225 | 3600 | 43 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 15 | 120 | 2 |
| 2 | NESTED LOOPS | | 225 | 3600 | 43 |
|* 3 | TABLE ACCESS FULL | T2 | 15 | 120 | 13 |
|* 4 | INDEX RANGE SCAN | T1_I1 | 15 | | 1 |
---------------------------------------------------------------------
Spot the critical difference. By the way, the access and filter predicates were the same in all tree cases, I just haven’t bothered to print them.
Think about the cost calculation for the nested loop. It’s normally
cost of first access + (cardinality of first access * cost of second access).
But when we work the formula out here, we get:
- 9.2.0.8: 14 + (15 * 2) = 44 … which is as expected
- 10.2.0.1: 14 + (15 * 2) = 44 … not the 37 reported.
- 10.2.0.3: 13 + (15 * 2) = 43 … which is as expected
So what’s happened ? The first time I ran this particular test suite I hypothesised that the second component of cost was limited by the clustering_factor of the index used; then I ran the next test suite, and though that maybe it was limited by the number of blocks in the second table.
Finally, after a few more tests I decided that the optimizer was simply not rounding the index-based part of the cost calculation in the way that it usually does. This, of course, was a sensible improvement – for anyone who hadn’t set the optimizer_index_caching parameter.
With the advent of 10.2.0.3, I now realise that this was probably a very transient bug. But if you do see, or have seen, odd problems with Oracle switching from hash joins to nested loops and back again as you work through releases, this may be the cause.
