Query running slower on new server

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?

Post the execution plans of this query from the new and old server.

Plus post the initialization parameters that are not identicaldifferent on both databases.

teehee.

sorry but I gotta laugh at your object names :D

Here is the execution plan. It is the same on both servers.

Operation Name Rows Bytes Cost PStart PStop
SELECT STATEMENT
FILTER
SORT GROUP BY
TABLE ACCESS FULL CHEESE

Also the initialisation parameters are exactly the same.
This ones got me stumped.

Have you tried taking out the rule hint?

DB_BLOCK_SIZE ?
DB_FILE_MULTIBLOCK_READ_COUNT ?
SORT_AREA_SIZE ?
... ?

Are you sure they are the same on both servers? If yes, then you should probably check your I/O subsystem efficiency on the new server.

[guess] It's probably going to use a FTS no matter what. [/guess]
Have you looked at the stats? Could the table have got a badly set high-water-mark?

P.S. it would run quicker with Swiss cheese :D

Analyzed?

Thanks for all your help. Rechecked initialisation file and some donkey has put two entries in for log_buffer and dml_locks. Couldn't possibly have been me ;-)
Bit strange that only one query/process was affected.

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

=======
<> condition stops using index.
You could rewrite the query as given below:

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

Create an index on typeid column. Check the plan.

Tamil

Tamil, that depends how (block)-selective "WHERE typeid <> 0" is. My [guess] above is that it isn't selective.

I did wonder about creating an index with EVERY column referenced in the query - do it with an IFFS . . . . !

Your guess may be right or wrong.
We do not know selectivity of the column (or even block selectivity). I do not agree with you for creating an index on all columns used in group by clause + columns in select.
If you do it, then the index will become big, that leads to space wastage.

Tamil

Hi Tamil,

Quote:

---

Originally posted by tamilselvan
Your guess may be right or wrong.
We do not know selectivity of the column (or even block selectivity).

---

So might yours! I'd do a quick "count(*) where type_ip <> 0" to get a feel of it before creating and analyzing indexes.
(teriyaki probably knows the answer).

Quote:

---

Originally posted by tamilselvan
I do not agree with you for creating an index on all columns used in group by clause + columns in select.

If you do it, then the index will become big, that leads to space wastage.

---

Sorry, British humour - this was not really a serious suggestion. :)