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.
