Oracle Scratchpad

May 28, 2007

Version Dependency

Filed under: CBO,Execution plans,Troubleshooting — Jonathan Lewis @ 9:48 pm BST May 28,2007

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.

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,089 other followers