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'));