Re: Slow query (only the first time)
From: joel garry <joel-garry_at_home.com>
Date: Fri, 13 May 2011 08:59:42 -0700 (PDT)
Message-ID: <e06de8c8-c0a5-4fb1-9d7d-0c061e1e0ce0_at_r35g2000prj.googlegroups.com>
On May 13, 4:49 am, "Á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
> --
Date: Fri, 13 May 2011 08:59:42 -0700 (PDT)
Message-ID: <e06de8c8-c0a5-4fb1-9d7d-0c061e1e0ce0_at_r35g2000prj.googlegroups.com>
On May 13, 4:49 am, "Á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
> --
In addition to what the others said, please tell us which hardware you are on, whether you are using raw devices and/or asynchronous I/O. Part of the issue could be the first time things are being read into a file system buffer cache as well as the PGA, subsequent times are already there in memory. So run two statspacks, covering each situation, as well as the plans.
The distinct may also have sort performance issues, let us know things like sort_area_size. I'm rusty on 9, but I seem to recall the OEM has statistics about sorts spilling to disk and PGA, though I think those would be stable performance issues. But you might be hitting some temp segment bug.
jg
-- _at_home.com is bogus. http://groups.google.com/group/Is-Something-Broken/browse_thread/thread/e65d8e50abb93944#Received on Fri May 13 2011 - 10:59:42 CDT