Re: Query Slow in 10g, runs very fast in 8i

DBdude_From_NY_at_yahoo.com wrote:

> We have a 8i DB on VMS-VAX machine. We copied the DB to Unix on Oracle
> 10g. Unix machine is more powerful then VMS and its 10g. A SP which was
> taking less then 10 minutes on 8i is taking close to 11 hours (Yes,
> Hours) on Unix, 10g. From where should I start looking in order to make
> it faster.

Well you have a specific "tuning opportunity" (quote from the docs that) meaning something that you know is bad. The *most* effective approach that I have found to this sort of issue is

1. Check the obvious - plan/stats/what has changed and so on.
2. Trace the job and profile it.

which is broadly what I will recommend but see below.

> On Oracle 8i statistics was not being gathered and on 10 g we are
> gathering stats.
> BTW, the SP is written real bad, but it was finishing its job in less
> then 10 minutes and that also with 3 times more data because we have
> not migrated entire data. We do have migrated all tables, indexes, SPs
> etc.

There's a different platform, different data, different optimizer model and (likely from the sound of it) different database settings. So the what has changed question seems to be "everything". This will likely be the first of a number of issues in such a case... remember to record the things that improved as well.

If the SP is relatively straightforward it might be worth posting it together with any relevant explain plans.

Niall Received on Fri Jan 05 2007 - 15:42:28 CST