Slow query (only the first time)

From: Álvaro G. Vicario <alvaro.NOSPAMTHANX_at_demogracia.com.invalid>
Date: Fri, 13 May 2011 13:49:16 +0200
Message-ID: <iqj5rt$2e6$1_at_dont-email.me>

I maintain a PHP-driven web app that's been deployed for several customers that have a variety of Oracle environments. One of them has reported that one of the modules is not working and I've traced back the issue to a very specific SQL query. The query itself does not use a complicate syntax; it's only a bunch of table joins:

SELECT DISTINCT regular_table.regular_table_id AS c0,

	additional_table_1.foo AS c1,
	additional_table_2.bar AS c2,
	TO_CHAR(regular_table.start_date, 'DD/MM/YYYY') AS c3,
	........
	temporary_table.sort_order AS sort_order_

FROM temporary_table
INNER JOIN regular_table ON
temporary_table.regular_table_id=regular_table.regular_table_id LEFT JOIN additional_table_1 ON
regular_table.regular_table_id=additional_table_1.regular_table_id LEFT JOIN additional_table_2 ON
regular_table.additional_table_2_id=additional_table_2.additional_table_2_id ........
ORDER BY sort_order_;

I assume the issue is probably related to having an insane amount of table joins. I'm testing from SQL*Plus with an empty "temporary_table" table so no rows should be returned. When I run a simplified version of the query the result comes out immediately:

SELECT DISTINCT regular_table.regular_table_id AS c0,

	additional_table_1.foo AS c1,
	TO_CHAR(regular_table.start_date, 'DD/MM/YYYY') AS C3,
	temporary_table.sort_order AS sort_order_

FROM temporary_table
INNER JOIN regular_table ON
temporary_table.regular_table_id=regular_table.regular_table_id LEFT JOIN additional_table_1 ON
regular_table.regular_table_id=additional_table_1.regular_table_id ORDER BY sort_order_;

As I add additional LEFT JOIN clauses one by one, the execution time increase exponentially. Once I manage to successfully run a query that takes, e.g., 10 minutes, successive executions run almost instantly! That leads me to think that the bottleneck is the query optimizer itself... But, is that even possible?

The server runs "Oracle9i Release 9.2.0.1.0 - 64bit Production"; I heard the DBA mention something about "cluster" but that's all I know.

Any idea of what to look next?

--

• http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
• Mi sitio sobre programación web: http://borrame.com
• Mi web de humor satinado: http://www.demogracia.com --

Received on Fri May 13 2011 - 06:49:16 CDT