Optimize query ...
astalavista
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"(+))
fitzj...@cox.net
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
Charles Hooper
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.