Re: Slow query (only the first time)
Date: Fri, 13 May 2011 07:47:44 -0700 (PDT)
Message-ID: <b0889a7e-a749-47c8-bc63-ebfb323f0c81_at_r33g2000prh.googlegroups.com>
On May 13, 7: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
> --
There can be numerous issues why one specific query works well on one database and poorly on another. The quantity of data itself, the condition of the statistics, and competing load.
Also an application developed on one version of Oracle might also have issues on another version of Oracle due to how that specific version handles certain SQL statement structures like sub-query pushing and view merging due to CBO design features or bugs.
I would start by asking the site having an issue to verify the physical structure of the objects in question, that is, verify that all the indexes for the objects in question exist and have the correct column lists.
Next have the age and accuracy of the CBO object statistics verified.
Then if the above checks out ask for a trace of the process to be ran using waits and binds and for the DBA to send you both the raw trace and a tkprof report (with explain) for the trace. Have the DBA take a statspace snapshop right before and after the task runs and send the statspack report.
The use of the term cluster probably means the database in question is using RAC and RAC would introduce GC lock traffic and instance parallelism as potential issues to be looked at.
HTH -- Mark D Powell -- Received on Fri May 13 2011 - 09:47:44 CDT