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"(+))

David Fitzjarrell provided good suggestions that will help identify where the system is slow. The 10046 trace will likely show that the temp tablespace is being used heavily.

Looking over the SQL statement, it appears that you are trying to compare one year's data with the same period in the prior year. Both year's data are stored in the same JOB_VNTXPO_UGA table, without restrictions on the date range that is of interest. You may want to, for instance, limit a.mois to be greater than TRUNC(SYSDATE-400), and b.mois to be between TRUNC(SYSDATE-765) AND TRUNC(SYSDATE-365) - or even more tightly restrict the data date range.

The calls to DBMS_UTILITY.get_hash_value may be affecting performance.

If you slide zwxp.job_vntxpo_uga b into an inline view, pre-joined with inline views that you aliased as c and d, you may be able to eliminate the outer join between those three objects. You could then join the result of this with zwxp.job_vntxpo_uga a using an outer join.

Another option is to see if any of the analytical functions are of use.  In my database, I have a table that records the on hand part counts at the end of each month. If I want to compare the quantity on hand for one month with the quantity on hand for the same month in the previous year, I can use the following (assuming that there are exactly 1 record per part per month) to retrieve the previous year's data: SELECT
  PART_ID,
  POSTING_DATE,
  QTY,
  LEAD(POSTING_DATE,12) OVER (PARTITION BY PART_ID ORDER BY POSTING_DATE DESC) POSTING_DATE_THEN,
  LEAD(QTY,12) OVER (PARTITION BY PART_ID ORDER BY POSTING_DATE DESC) QTY_THEN
FROM
  INVENTORY_BALANCE; The LEAD analytical function looks ahead the specified number of records (12) when the records are separated by PART_ID and sorted in descending order by POSTING_DATE.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Jan 18 2007 - 16:23:08 CST