Re: Optimize query ...

astalavista wrote:
> Hi
>
> Can you help me to optimize this query ( 46 min on 9.2.0.4)
> thanks in advance ...
>
> SELECT a.mois, DBMS_UTILITY.get_hash_value (a.uga, 1, POWER (2, 30)) AS
> uga,
> 3 SUM (a.caxv) caxv, SUM (a.unxv * c.coepdt) unxv, SUM (a.pxv)
> pxv,
> 4 SUM (a.caxh) caxh, SUM (a.unxh * c.coepdt) unxh, SUM (a.pxh)
> pxh,
> 5 SUM (DECODE (b.caxv, NULL, 0, b.caxv)) caxv_a1, d.idmar,
> 6 SUM (DECODE (b.unxv, NULL, 0, b.unxv * c.coepdt)) unxv_a1,
> 7 SUM (DECODE (b.pxv, NULL, 0, b.pxv)) pxv_a1,
> 8 SUM (DECODE (b.caxh, NULL, 0, b.caxh)) caxh_a1,
> 9 SUM (DECODE (b.unxh, NULL, 0, b.unxh * c.coepdt)) unxh_a1,
> 10 SUM (DECODE (b.pxh, NULL, 0, b.pxh)) pxh_a1
> 11 FROM zwxp.job_vntxpo_uga a,
> 12 zwxp.job_vntxpo_uga b,
> 13 (SELECT codpdt, coepdt, idmar, amm
> 14 FROM xponent_pdtmar) c,
> 15 (SELECT idmar
> 16 FROM xponent_mar) d
> 17 WHERE a.uga = b.uga(+)
> 18 AND a.codcip = b.codcip(+)
> 19 AND b.codcip = c.amm(+)
> 20 AND c.idmar = d.idmar(+)
> 21 AND b.mois(+) = ADD_MONTHS (a.mois, -12)
> 22 GROUP BY d.idmar, a.mois, a.uga
> 23
>
> PLAN_TABLE_OUTPUT
> ----------------------------------------------------------------------------------------------------
>
> ----------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes
> |TempSpc| Cost |
> ----------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 65188 | 5538K|
> | 710K|
> | 1 | SORT GROUP BY | | 65188 | 5538K|
> 1343M| 710K|
> | 2 | NESTED LOOPS OUTER | | 14M| 1206M|
> | 303K|
> |* 3 | HASH JOIN OUTER | | 14M| 1179M|
> 860M| 303K|
> | 4 | MERGE JOIN OUTER | | 10M| 740M|
> | 284K|
> | 5 | SORT JOIN | | 10M| 370M|
> 1204M| 142K|
> | 6 | TABLE ACCESS FULL | JOB_VNTXPO_UGA | 10M| 370M|
> | 3776 |
> |* 7 | SORT JOIN | | 10M| 370M|
> 1204M| 142K|
> | 8 | TABLE ACCESS FULL | JOB_VNTXPO_UGA | 10M| 370M|
> | 3776 |
> | 9 | INDEX FAST FULL SCAN| XPONENT_PDTMAR_IDX1 | 781 | 8591 |
> | 2 |
> |* 10 | INDEX UNIQUE SCAN | XPONENT_MAR_PK | 1 | 2 |
> | |
> ----------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 3 - access("B"."CODCIP"="XPONENT_PDTMAR"."AMM"(+))
> 7 - access("A"."UGA"="B"."UGA"(+))
> filter("B"."MOIS"(+)=ADD_MONTHS("A"."MOIS",-12) AND
> "A"."CODCIP"="B"."CODCIP"(+)
> AND "A"."UGA"="B"."UGA"(+))
> 10 - access("XPONENT_PDTMAR"."IDMAR"="XPONENT_MAR"."IDMAR"(+))

You should run a 10046 trace at level 8 or 12 to see what is really happening with this query, and also a 10053 trace to see what the optimiser is really doing at runtime. Post those results and we'll probably have better information to work with. From what you've posted, though, I'd see why you're running full table scans on JOB_VNTXPO_UGA; either you have no indexes or you don't have current statistics.

I'll wait for the 10046 and 10053 outputs as there may be more 'under the covers' than this query plan reveals.

David Fitzjarrell Received on Thu Jan 18 2007 - 14:31:39 CST