Re: Slow query (only the first time)

From: Carlos <miotromailcarlos_at_netscape.net>
Date: Fri, 13 May 2011 05:54:20 -0700 (PDT)
Message-ID: <dd29e38e-a7c1-4e32-9d02-66a78aaa80a1_at_p6g2000vbn.googlegroups.com>

On May 13, 1:49�pm, "�lvaro G. Vicario"
<alvaro.NOSPAMTH..._at_demogracia.com.invalid> 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
> --

>>""Any idea of what to look next?

Query Plans?

>>"Oracle9i Release 9.2.0.1.0 - 64bit Production"; I heard the DBA mention..."

9.2.0.1? Get this supposed DBA fired right now.

Cheers.

Carlos. Received on Fri May 13 2011 - 07:54:20 CDT