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.
> 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.
>

WOOOHOOOO VMS ROCKS!!!!
what does the explain plan look like for the query(ies)? in 8i you were probably using rules-based and in 10g it does not exist. You will need to look at your indexes and do some *real* tuning. You may also want to take the time to update your SQL to be more ANSI compliant -- FWIW, I always hated the (+) join syntax from Oracle.

Are you using LMT? (locally managed tablespaces? - I believe in 10g the old DM tablespaces are on their way out or already gone... - Another fine feature from Rdb).

-- 
Michael Austin.
Database Consultant