Re: Plan change after moving to 10g 10.2.0.3
- From: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
- To: <hrishys@xxxxxxxxxxx>
- Date: Thu, 18 Dec 2008 13:09:25 +0100 (CET)
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.
Regards,
Jaromir D.B. Nemec
--
//www.freelists.org/webpage/oracle-l
Other related posts:
