Slow query (only the first time)

#1

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.reg ular_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_tab le_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.reg ular_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
--

#2

On May 13, 1:49*pm, "Álvaro G. Vicario"
<alvaro.NOSPAMTH... (AT) demogracia (DOT) com.invalid> wrote:

Quote:

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.reg ular_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_tab le_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.reg ular_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
--

Quote:

""Any idea of what to look next?

Query Plans?

Quote:

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

#3

On May 13, 7:49*am, "Álvaro G. Vicario"
<alvaro.NOSPAMTH... (AT) demogracia (DOT) com.invalid> wrote:

Quote:

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.reg ular_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_tab le_2.additional_table_2_i*d
........
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.reg ular_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 --

#4

On May 13, 4:49*am, "Álvaro G. Vicario"
<alvaro.NOSPAMTH... (AT) demogracia (DOT) com.invalid> wrote:

Quote:

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.reg ular_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_tab le_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.reg ular_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
--
@home.com is bogus.
http://groups.google.com/group/Is-So...d8e50abb93944#

#5

On May 13, 4:49*am, "Álvaro G. Vicario"
<alvaro.NOSPAMTH... (AT) demogracia (DOT) com.invalid> wrote:

MOS Bug 4169306: ANSI JOIN GIVES BAD PLAN COMPARED TO ORACLE JOIN

WORKAROUND:
-----------
Don't use ansi joins.

I'm sure that's not the only one, start googling...

jg
--
@home.com is bogus.
http://www.theregister.co.uk/2011/05...doop_cloudera/

#6

"Álvaro G. Vicario" wrote:

Quote:

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?

You can limit the number of permutations

#7

El 13/05/2011 14:54, Carlos escribió/wrote:

Quote:

Any idea of what to look next?

Query Plans?

It's not easy to get query plans when issuing an EXPLAIN PLAN FOR query
is likely to create a zombie connection

Quote:

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

In what real-life organisation is the DBA the person that makes
decisions about funding and large-scale migrations?

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

#8

El 18/05/2011 17:40, "Álvaro G. Vicario" escribió/wrote:

Quote:

El 13/05/2011 18:09, joel garry escribió/wrote:
On May 13, 4:49 am, "Álvaro G. Vicario"
alvaro.NOSPAMTH... (AT) demogracia (DOT) com.invalid> wrote:

MOS Bug 4169306: ANSI JOIN GIVES BAD PLAN COMPARED TO ORACLE JOIN

WORKAROUND:
-----------
Don't use ansi joins.

I'm sure that's not the only one, start googling...

Good point. Natural joins were introduced in Oracle 9 and Tom hates them.

I've made a simple test (ansi vs oracle) and both execution plans have
absolutely nothing in common. Curiously, the second one does not display
figures (rows, bytes and cost are empty... :-?) From my test, it looks
like the ANSI join does not use a single index: it's all TABLE ACCESS
FULL :-!

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

#9

On May 18, 9:49*am, "Álvaro G. Vicario"
<alvaro.NOSPAMTH... (AT) demogracia (DOT) com.invalid> wrote:

Quote:

El 18/05/2011 17:40, "Álvaro G. Vicario" escribió/wrote:
*> El 13/05/2011 18:09, joel garry escribió/wrote:
*>> On May 13, 4:49 am, "Álvaro G. Vicario"*>> <alvaro.NOSPAMTH... (AT) demogracia (DOT) com.invalid> wrote:

*
*>> MOS Bug 4169306: ANSI JOIN GIVES BAD PLAN COMPARED TO ORACLE JOIN
*
*>> WORKAROUND:
*>> -----------
*>> Don't use ansi joins.
*
*>> I'm sure that's not the only one, start googling...

Good point. Natural joins were introduced in Oracle 9 and Tom hates them.

I've made a simple test (ansi vs oracle) and both execution plans have
absolutely nothing in common. Curiously, the second one does not display
figures (rows, bytes and cost are empty... :-?) From my test, it looks
like the ANSI join does not use a single index: it's all TABLE ACCESS
FULL :-!

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

How are you getting the explain plan? From the 9.2 docs: "The NULL
in the Rows column indicates that the optimizer does not have any
statistics on the table." Very odd if you are looking at the same
table. If what Mark suggested doesn't do the trick, you may have to
10053 trace.

I agree with Carlos, by the way. No one should be running unpatched v.
9 Oracle.

jg
--
@home.com is bogus.
All your nerds are belong to us. http://comics.com/get_fuzzy/2011-05-17/

#10

El 18/05/2011 19:35, joel garry escribió/wrote:

Quote:

On May 18, 9:49 am, "Álvaro G. Vicario"
alvaro.NOSPAMTH... (AT) demogracia (DOT) com.invalid> wrote:
El 18/05/2011 17:40, "Álvaro G. Vicario" escribió/wrote:
El 13/05/2011 18:09, joel garry escribió/wrote:
On May 13, 4:49 am, "Álvaro G. Vicario">> <alvaro.NOSPAMTH... (AT) demogracia (DOT) com.invalid> wrote:

MOS Bug 4169306: ANSI JOIN GIVES BAD PLAN COMPARED TO ORACLE JOIN

WORKAROUND:
-----------
Don't use ansi joins.

I'm sure that's not the only one, start googling...

Good point. Natural joins were introduced in Oracle 9 and Tom hates them.

I've made a simple test (ansi vs oracle) and both execution plans have
absolutely nothing in common. Curiously, the second one does not display
figures (rows, bytes and cost are empty... :-?) From my test, it looks
like the ANSI join does not use a single index: it's all TABLE ACCESS
FULL :-!

How are you getting the explain plan? From the 9.2 docs: "The NULL
in the Rows column indicates that the optimizer does not have any
statistics on the table." Very odd if you are looking at the same
table. If what Mark suggested doesn't do the trick, you may have to
10053 trace.

I agree with Carlos, by the way. No one should be running unpatched v.
9 Oracle.

I'm slowing getting some basic info. I run this test query:

EXPLAIN PLAN FOR
SELECT DISTINCT EDIFICIOS.EDIFICIOS_ID AS C0,
PARCELAS.REF_CAT_PARCELA AS C1,
TO_CHAR(EDIFICIOS.FECHA_PROXIMA_ITC, 'DD/MM/YYYY') AS C7,
TMP_EDIFICIOS.ORDEN AS ORDEN_
FROM TMP_EDIFICIOS
INNER JOIN EDIFICIOS ON TMP_EDIFICIOS.EDIFICIOS_ID=EDIFICIOS.EDIFICIOS_ID
LEFT JOIN IMPORTACION ON EDIFICIOS.EDIFICIOS_ID=IMPORTACION.EDIFICIOS_ID
LEFT JOIN PARCELAS ON EDIFICIOS.PARCELAS_ID=PARCELAS.PARCELAS_ID
ORDER BY ORDEN_;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

In my local Oracle XE 10 the query plan is:

-------------------------------------------------------------------------------------------------

Quote:

-------------------------------------------------------------------------------------------------

Quote:

0 | SELECT STATEMENT | | 1 | 67
10 (30)| 00:00:01 |

Quote:

1 | SORT UNIQUE | | 1 | 67
9 (23)| 00:00:01 |

Quote:

2 | NESTED LOOPS OUTER | | 1 | 67
8 (13)| 00:00:01 |

Quote:

* 3 | HASH JOIN OUTER | | 1 | 48
7 (15)| 00:00:01 |

Quote:

4 | NESTED LOOPS | | 1 | 44
3 (0)| 00:00:01 |

Quote:

5 | TABLE ACCESS FULL | TMP_EDIFICIOS | 1 | 26
2 (0)| 00:00:01 |

Quote:

6 | TABLE ACCESS BY INDEX ROWID| EDIFICIOS | 1 | 18
1 (0)| 00:00:01 |

Quote:

* 7 | INDEX UNIQUE SCAN | EDIFICIOS_PK | 1 |
0 (0)| 00:00:01 |

Quote:

8 | TABLE ACCESS FULL | IMPORTACION | 1 | 4
3 (0)| 00:00:01 |

Quote:

9 | TABLE ACCESS BY INDEX ROWID | PARCELAS | 1 | 19
1 (0)| 00:00:01 |

Quote:

* 10 | INDEX UNIQUE SCAN | PARCELAS_PK | 1 |
0 (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------

It's using indexes and total cost ranges from 0 to 10.

In the client's 9i it looks like this:

----------------------------------------------------------------------------------

Quote:

----------------------------------------------------------------------------------

Quote:

0 | SELECT STATEMENT | | 98M| 6546M|
3382K|

1 | SORT UNIQUE | | 98M| 6546M|
14G| 1692K|

Quote:

* 2 | HASH JOIN OUTER | | 98M| 6546M|
137M| 2874 |

Quote:

3 | VIEW | | 2401K| 109M|
677 |

* 4 | HASH JOIN OUTER | | 2401K| 169M|
40M| 677 |

Quote:

5 | VIEW | | 587K| 34M|
24 |

* 6 | HASH JOIN | | 587K| 34M|
24 |

7 | TABLE ACCESS FULL| TMP_EDIFICIOS | 8168 | 207K|
10 |

8 | TABLE ACCESS FULL| EDIFICIOS | 7188 | 245K|
9 |

9 | TABLE ACCESS FULL | IMPORTACION | 409 | 5317 |
1 |

10 | TABLE ACCESS FULL | PARCELAS | 4084 | 89848 |
5 |

----------------------------------------------------------------------------------

The cost of 3382K says it all...

If I rewrite the query as:

SELECT DISTINCT EDIFICIOS.EDIFICIOS_ID AS C0,
PARCELAS.REF_CAT_PARCELA AS C1,
TO_CHAR(EDIFICIOS.FECHA_PROXIMA_ITC, 'DD/MM/YYYY') AS C7,
TMP_EDIFICIOS.ORDEN AS ORDEN_
FROM TMP_EDIFICIOS, EDIFICIOS, IMPORTACION, PARCELAS
WHERE TMP_EDIFICIOS.EDIFICIOS_ID=EDIFICIOS.EDIFICIOS_ID
AND EDIFICIOS.EDIFICIOS_ID=IMPORTACION.EDIFICIOS_ID
AND EDIFICIOS.PARCELAS_ID=PARCELAS.PARCELAS_ID
ORDER BY ORDEN_;

.... I finally get a very similar plan in both servers:

-------------------------------------------------------------------------------------

Quote:

-------------------------------------------------------------------------------------

Quote:

-------------------------------------------------------------------------------------

The plan improves by several orders of magnitude if I remove DISTINCT
*and* ORDER BY but it still won't use indexes:

---------------------------------------------------------------------------------

Quote:

---------------------------------------------------------------------------------

Quote:

0 | SELECT STATEMENT | | 98M| 6546M|
2874 |

* 1 | HASH JOIN OUTER | | 98M| 6546M|
137M| 2874 |

Quote:

2 | VIEW | | 2401K| 109M|
677 |

* 3 | HASH JOIN OUTER | | 2401K| 169M|
40M| 677 |

Quote:

4 | VIEW | | 587K| 34M|
24 |

* 5 | HASH JOIN | | 587K| 34M|
24 |

6 | TABLE ACCESS FULL| TMP_EDIFICIOS | 8168 | 207K|
10 |

7 | TABLE ACCESS FULL| EDIFICIOS | 7188 | 245K|
9 |

8 | TABLE ACCESS FULL | IMPORTACION | 409 | 5317 |
1 |

9 | TABLE ACCESS FULL | PARCELAS | 4084 | 89848 |
5 |

---------------------------------------------------------------------------------

So far, I believe that my options are:

1. Rewrite the complete module to avoid ANSI joins.

Not a nice option at this time but...

2. Use optimizer hints to make Oracle use indexes.

I need to research on this...

3. Retrieve unsorted results into a temporary table and sort that table
when reading it.

The final result set will only have a few thousand lines and
needs to be sorted by one integer column.

4. Retrieve unsorted results and use PHP to sort.

Not a nice option at this time...

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