We have recently migrated our Oracle 8 database running on a Server with a pair of Pentium 2 300Mhz processors, 512mb RAM running on windows NT to Oracle 8i on a Server with a pair of Pentium IV Xeon 2.8Mhz with 4 Gb of RAM running Windows Server 2003. We migrated using the Oracle Migration Tool on the old server and moved all the files across to the new server. At this point both databases on the old server and new server are identical. As you can imagine processing times on the database on the new server has dramatically decreased except in one case where we are running a very simple query and it is taking about 6 times longer to run on the new database on the new server than on the old server.

This is the query (altered table names and fields have been changed to protect the innocent)

SELECT/*+ RULE */MAX(cheeseid) FROM cheese
WHERE typeid <> 0
GROUP BY currency, cost, smell, classification, typeid, creationdate
HAVING count(cheeseid) > 1;

I have run this query on the old server running exactly the same database as the new server and this query takes 10 minutes to finish. On the new server this query takes about 60 minutes. Every other query we run regularly has dramatically decresed in time except for this one query which has dramatically incresed.

Any ideas?