-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
teehee.
sorry but I gotta laugh at your object names
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
[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
-
I remember when this place was cool.
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|