Re: Slow query (only the first time)

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... Received on Fri Dec 08 2017 - 03:24:00 CET