Re: Slow query (only the first time)
From: Álvaro G. Vicario <alvaro.NOSPAMTHANX_at_demogracia.com.invalid>
Date: Thu, 19 May 2011 10:08:26 +0200
Message-ID: <ir2j5t$4qf$1_at_dont-email.me>
El 18/05/2011 19:35, joel garry escribió/wrote:
> On May 18, 9:49 am, "Álvaro G. Vicario"
> <alvaro.NOSPAMTH..._at_demogracia.com.invalid> wrote:
> 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.
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:
Date: Thu, 19 May 2011 10:08:26 +0200
Message-ID: <ir2j5t$4qf$1_at_dont-email.me>
El 18/05/2011 19:35, joel garry escribió/wrote:
> On May 18, 9:49 am, "Álvaro G. Vicario"
> <alvaro.NOSPAMTH..._at_demogracia.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.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:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 67| 10 (30)| 00:00:01 |
| 1 | SORT UNIQUE | | 1 | 67 | 9 (23)| 00:00:01 | | 2 | NESTED LOOPS OUTER | | 1 | 67 | 8 (13)| 00:00:01 | |* 3 | HASH JOIN OUTER | | 1 | 48 | 7 (15)| 00:00:01 | | 4 | NESTED LOOPS | | 1 | 44 | 3 (0)| 00:00:01 | | 5 | TABLE ACCESS FULL | TMP_EDIFICIOS | 1 | 26 | 2 (0)| 00:00:01 | | 6 | TABLE ACCESS BY INDEX ROWID| EDIFICIOS | 1 | 18 | 1 (0)| 00:00:01 | |* 7 | INDEX UNIQUE SCAN | EDIFICIOS_PK | 1 | | 0 (0)| 00:00:01 | | 8 | TABLE ACCESS FULL | IMPORTACION | 1 | 4 | 3 (0)| 00:00:01 | | 9 | TABLE ACCESS BY INDEX ROWID | PARCELAS | 1 | 19 | 1 (0)| 00:00:01 | |* 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:
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
| 0 | SELECT STATEMENT | | 98M| 6546M|| 3382K|
| 1 | SORT UNIQUE | | 98M| 6546M|14G| 1692K|
|* 2 | HASH JOIN OUTER | | 98M| 6546M|137M| 2874 |
| 3 | VIEW | | 2401K| 109M|| 677 |
|* 4 | HASH JOIN OUTER | | 2401K| 169M|40M| 677 |
| 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:
| Id | Operation | Name | Rows |Bytes | Cost |
| 0 | SELECT STATEMENT | | |
| |
| 1 | SORT UNIQUE | | |
| |
| 2 | NESTED LOOPS | | |
| |
| 3 | NESTED LOOPS | | |
| |
| 4 | NESTED LOOPS | | |
| |
| 5 | TABLE ACCESS FULL | IMPORTACION | |
| |
| 6 | TABLE ACCESS BY INDEX ROWID| EDIFICIOS | |
| |
|* 7 | INDEX UNIQUE SCAN | EDIFICIOS_PK | |
| |
| 8 | TABLE ACCESS BY INDEX ROWID | PARCELAS | |
| |
|* 9 | INDEX UNIQUE SCAN | PARCELAS_PK | |
| |
| 10 | TABLE ACCESS BY INDEX ROWID | TMP_EDIFICIOS | |
| |
|* 11 | INDEX UNIQUE SCAN | TMP_EDIFICIOS_PK | |
| |
-------------------------------------------------------------------------------------
The plan improves by several orders of magnitude if I remove DISTINCT *and* ORDER BY but it still won't use indexes:
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
| 0 | SELECT STATEMENT | | 98M| 6546M|| 2874 |
|* 1 | HASH JOIN OUTER | | 98M| 6546M|137M| 2874 |
| 2 | VIEW | | 2401K| 109M|| 677 |
|* 3 | HASH JOIN OUTER | | 2401K| 169M|40M| 677 |
| 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:
- 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 --Received on Thu May 19 2011 - 03:08:26 CDT