Query running slower on new server
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Query running slower on new server

  1. #1
    Join Date
    Nov 2003
    Posts
    5

    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?

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  3. #3
    Join Date
    Mar 2002
    Location
    Manchester, England
    Posts
    202
    teehee.

    sorry but I gotta laugh at your object names

  4. #4
    Join Date
    Nov 2003
    Posts
    5
    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.

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,015
    Have you tried taking out the rule hint?

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    [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

  8. #8
    Join Date
    Jan 2001
    Posts
    3,131
    Analyzed?
    I remember when this place was cool.

  9. #9
    Join Date
    Nov 2003
    Posts
    5
    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.

  10. #10
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    =========
    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
  •  



Click Here to Expand Forum to Full Width