RE: xplan row source 9i

Hi

>This job runs in under 2 hours on 8.1.7.4, and 6.5 hours on 9.2.0.4
>(both on VMS 7.3-1)
>The data is identical (exact copy of the data files), but the cpu is
>different. It's possible that the cpu difference accounts for all 4
>hours difference.

The 8.1.7 execution plan is the same as in 9.2.0?

>The row source operations shows an index full scan, while the
>execution plan shows 2 index range scans concatenated. My
>understanding is that the row source is what actually occurred.=20

Yes.

>Would the differences between 8i and 9i account for this difference?

Since you don't provide the 8i execution plan isn't possible to answer = your question.

> Did version 9i do some "bind variable peeking" that 8i did not do?

Yes, bind variables peeking doesn't exists in 8i.

> If so, is there a hint to turn it off?

Setting OPTIMIZER_FEATURES_ENABLE to 8.1.7 or less should disable it.

>select /*+ INDEX(WOD PK_WOD) */ rowid ,WO_KEY ,XRF_KEY
>from
> WOD where (WO_KEY>:b1 or (WO_KEY=3D:b1 and XRF_KEY>=3D:b2))

How much rows are returned if you apply the two restrictions separately?

Chris

--
http://www.freelists.org/webpage/oracle-l