Re: Plan change after moving to 10g

From: jaromir nemec <>
Date: Thu, 18 Dec 2008 13:09:25 +0100 (CET)
Message-ID: <>

Hello Hrishy,

from the NL plan
> |* 6 | INDEX UNIQUE SCAN | tab2_PK |
21348 | 1 | 21348
From the HJ plan
> |* 5 | TABLE ACCESS FULL | tab2 |
1 | 12M| 12M|00:00:37.20 | 196K

The main problem what I see is that the CBO things that reading of the 196K blocks in full table scan is cheaper than reading 21K rows in loop via index.
I thing this is not a problem of statistics as the difference between A and E rows is pretty small.

In the first place I’d check the system statistics and compare them with the 9i DB.
Do you use system statistics in both systems? In 10g wokload/noworkload?

If the costs of reading one block in FTS (MREADTIM / MBRC) are much lower then the cost of reading a single block (SREADTIM) then of course the FTS are preferred.
But of course other causes (statistics and/or parameter setting) are possible.

A good starting point for further investigation is simple to compare the two execution plans in 10g (the one of the hash join and the one with nested loop – forced by hint).
Checking the costs of both plans I guess you will see that the cost of full table scan are unrealistically underestimated.


Jaromir D.B. Nemec

Received on Thu Dec 18 2008 - 06:09:25 CST

Original text of this message