On Friday, May 13, 2011 at 7:49:16 PM UTC+8, "Álvaro G. Vicario" wrote:

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
--

You can try https://tosska.com/tosska-sql-tuning-expert-tse-oracle/
it is free...

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)