Running a query over the network multiple times seems to choke performance, how can I pinpoint the problem

oracleoracle-11g

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

Best Answer

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.

Related Solutions

Does an IOT Guarantee Order in a SELECT Query in Oracle?

No.

The only thing that guarantees result set order is an ORDER BY clause in your query.

This is a popular question about SQL so it's worth repeating what I've written in response to similar questions about SQL Server and MySQL:

In the SQL world, order is not an inherent property of a set of data. Thus, you get no guarantees from your RDBMS that your data will come back in a certain order -- or even in a consistent order -- unless you query your data with an ORDER BY clause.

In Oracle, an Index-Organized Table (IOT) will minimize the amount of work the database has to do to get you the data sorted in the index's sort order. Though you may find that Oracle tends to return rows selected from an IOT in that same order, you are only guaranteed that order when you query the IOT with an ORDER BY clause.

LEFT JOIN conversion to INNER JOIN, can’t change FROM clause

That one caused me quite a bit of headache before I found a halfway elegant solution:

WITH x AS (
    SELECT BASE.id AS b_id
          ,BASE.start_date AS b_start_date
          ,BASE.end_date AS b_end_date
          ,CHILD.id AS c_id
          ,CHILD.start_date AS c_start_date
          ,CHILD.end_date AS c_end_date
          ,CASE WHEN DATE '2011-1-1' BETWEEN CHILD.start_date AND CHILD.end_date
                                     OR CHILD.id IS NULL THEN 0 ELSE 1 END AS c_hit
    FROM   BASE LEFT OUTER JOIN CHILD ON (BASE.ID=CHILD.ID)
    WHERE  BASE.ID = 1
    AND    DATE '2011-1-1' BETWEEN BASE.start_date AND BASE.end_date
    )
SELECT b_id, b_start_date, b_end_date
      ,c_id, c_start_date, c_end_date
FROM   x
WHERE  x.c_hit = 0

UNION ALL
SELECT b_id, b_start_date, b_end_date
      ,NULL, NULL,         NULL
FROM   x
GROUP  BY b_id, b_start_date, b_end_date
HAVING min(c_hit) = 1;

Of course, the better solution would be to get rid of the restrictions and adjust the JOIN in the base query. As @a_horse expressed it so poetically in the comments above:

the perils of ORM frameworks

Anyway, to solve the question at hand:
After selecting rows where BASE.id and BASE.start_date / BASE.end_date match, we have four different cases:

No child at all, so CHILD.id is NULL.
Child matches.
Child does not match and no other matching child.
Child does not match but other matching child.

This is hard to solve on one query level. You could do it with window functions and CASE statements and DISTINCT, in fact I had a basic working query, but it gets monstrous.

Using a CTE dramatically simplifies the syntax.
In the CTE:

Filter BASE on id and date range. Still includes rows with non-matching children.
Assign aliases for identical column names
Tag rows where child matches the date

In the final SELECT:

Return all rows with matching children
Return one row where no matching children exist, with NULLs for child-columns.

Here is working demo on sqlfiddle.com for those who want to toy with it.

Best Answer

Related Solutions

Does an IOT Guarantee Order in a SELECT Query in Oracle?

LEFT JOIN conversion to INNER JOIN, can’t change FROM clause

Related Question