2

Background:

Oracle 11g running on Windows 2003 x64 SP2 on a 3.06GHz Xeon X5675 with 16GB RAM

Over the past 3 weeks, a query I used to run approximately every 5 minutes has started to respond very very poorly - moving from an execution time of around 50 seconds to sometimes over 11 minutes. The data has hardly changed and returns approximately 69000 records If I restart the service, I find the query runs very quickly the first time but subsequent runs take an age to run and lock the CPU on the server at nearly 100%. This server is used for test/development purposes so is rarely under any serious load.

This ONLY happens when I run the query from a remote machine - running the query in SQL Plus on the server and dumping the result to a file gives a 25MB text file in about 1 second which made me think I have a network problem....

When I stop the service and use iperf.exe to transfer 25MB across the network on the same port of 1521, it takes 50 seconds no matter how many times I run it. It's not bandwidth related - Oracle just seems to choke.

I'll include the SQL statement but it is important to know that other similar queries which join the same tables are struggling to run in a timely fashion and yet run very quickly when ran locally. Again, those queries suffer from serious slow-down after their first run.

If anyone can give me any pointers on where to look for potential issues, I'd be eternally grateful!

SELECT 
    (CASE 
        WHEN t10.COURSE_ID IS NULL THEN t3.COURSENAME
        ELSE t10.COURSENAME
     END) AS C1, t3.LANGUAGE_CODE AS "LanguageCode"
FROM MYSERVERNAME.T_COURSETYPE t1
INNER JOIN MYSERVERNAME.T_CURRENCY t2 ON t1.CURRENCY_ID = t2.CURRENCY_ID
INNER JOIN MYSERVERNAME.T_COURSENAME_LANG t3 ON t1.COURSECODE_ID = t3.COURSECODE_ID
INNER JOIN MYSERVERNAME.T_COURSE t4 ON t1.COURSETYPE_ID = t4.COURSETYPE_ID
INNER JOIN MYSERVERNAME.T_PROFITCENTER t5 ON t4.PROFITCENTER_ID = t5.PROFITCENTER_ID
INNER JOIN MYSERVERNAME.T_COUNTRY t6 ON t5.COUNTRY_ID = t6.COUNTRY_ID
INNER JOIN MYSERVERNAME.T_COMPANYUNIT t7 ON t5.COMPANYUNIT_ID = t7.COMPANYUNIT_ID
INNER JOIN MYSERVERNAME.T_VENDOR t8 ON t7.VENDOR_ID = t8.VENDOR_ID
INNER JOIN MYSERVERNAME.T_COURSELOCATION t9 ON t4.COURSELOCATION_ID = t9.COURSELOCATION_ID
LEFT OUTER JOIN MYSERVERNAME.T_COURSENAME t10 ON (t4.COURSE_ID = t10.COURSE_ID) AND ((t3.LANGUAGE_CODE = t10.LANGUAGE_CODE) OR 
((t3.LANGUAGE_CODE IS NULL) AND (t10.LANGUAGE_CODE IS NULL)))
LEFT OUTER JOIN MYSERVERNAME.T_ESHOP_COUNTRY t11 ON t6.COUNTRY_ID = t11.COUNTRY_ID
WHERE (t4.FROMDATE > DATE '2014-06-17') AND (t4.HIDE_ONLINEBOOKING = 'N') AND (t1.ACTIVE = 'A') AND ((t1.TMS IS NULL) OR (t1.TMS = 'N'))
AND (t8.EXTERNAL_VENDOR = 'N') AND (t1.VENDOR_ID <> 66) AND (t4.TYPEOFFEE = 'D') AND (t9.HIDE_ONLINEBOOKING IS NOT NULL) AND
(t9.HIDE_ONLINEBOOKING = 'N') AND (((t11.ACTIVE IS NULL) AND (t6.ONLINEBOOKING = 'Y')) OR (t11.ACTIVE = 'Y'));
1

You really need to generate an explain plan. Since your query has an OR there may be situations where the query is being short circuited based on data not existing, hence the performance is not consistent. Also, don't join everything together, subqueries with low cardinality put at the top in a WITH subquery_name clause, and join similar tables together, then join them with other tables.

|improve this answer|||||
  • The above SQL is generated from an ORM which generates SQL from a .NET linq query so I have little control over how the SQL is generated. However, with the same data and same parameters I get the drop-off in performance after the first run post-db startup consistently which tells me that although query optimisation is important, it's not the issue in this case. – Neil Trodden Jun 20 '14 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.