Query tuning help needed

Hi,

Query :
SELECT a.acct_nr, d.cmpgn_yr, d.cmpgn_nr, a.ord_id, b.vol_nr, b.fnsh_stk_cd, fcr.rordr_shrt_rqst_strt_ts, b.prod_qty, a.zone_cd, a.colctn_typ,
trunc(d.inspctn_dt) LAST_MVMNT_DT, d.in68_cd, a.wrhs_cd,a.FSCL_NTE_DT,
(SELECT cmpgn_nr FROM (SELECT vcc.cmpgn_nr FROM sir_vw_cmpgn_calndr vcc WHERE (TRUNC (SYSDATE) BETWEEN vcc.init_dt AND vcc.end_dt ) ORDER BY vcc.cmpgn_nr)WHERE ROWNUM = 1) currnt_cmpgn_nr,
(SELECT cmpgn_yr FROM (SELECT vcc.cmpgn_yr FROM sir_vw_cmpgn_calndr vcc WHERE (TRUNC (SYSDATE) BETWEEN vcc.init_dt AND vcc.end_dt ) ORDER BY vcc.cmpgn_nr)WHERE ROWNUM = 1) currnt_cmpgn_yr,
a.julian_dt, fcr.actn_desc, fcr.fsc_desc
FROM sir_invc a, sir_carr c, sir_invc_item b, sir_mnfst d , SIR_FSC_CNCLTN_RQST FCR
WHERE a.julian_dt = 58
AND a.ord_id = 120320
AND b.vol_nr = 001
AND a.wrhs_cd = c.wrhs_cd
AND a.acct_nr = b.acct_nr
AND a.cmpgn_yr = b.cmpgn_yr
AND a.cmpgn_nr = b.cmpgn_nr
AND a.ord_id = b.ord_id
AND a.mnfst_nr = d.mnfst_nr
AND b.fnsh_stk_cd = FCR.fnsh_stk_cd
AND FCR.WORK_DT = trunc(sysdate)
AND FCR.RORDR_SHRT_RQST_END_TS is null
ORDER BY b.fnsh_stk_cd DESC

Explain Plan :
PLAN_TABLE_OUTPUT

Plan hash value: 2545911630

---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 166 | 173 (3)| 00:00:02 | | |
| 1 | REMOTE | | | | | | LCLOD~ | R->S |
| 2 | REMOTE | | | | | | LCLOD~ | R->S |
| 3 | SORT ORDER BY | | 1 | 166 | 173 (3)| 00:00:02 | | |
|* 4 | HASH JOIN | | 1 | 166 | 172 (2)| 00:00:02 | | |
| 5 | NESTED LOOPS | | 1 | 142 | 7 (0)| 00:00:01 | | |
| 6 | NESTED LOOPS | | 1 | 138 | 6 (0)| 00:00:01 | | |
| 7 | MERGE JOIN CARTESIAN | | 1 | 109 | 5 (0)| 00:00:01 | | |
|* 8 | TABLE ACCESS FULL | FSC_CNCLTN_RQST | 1 | 67 | 4 (0)| 00:00:01 | | |
| 9 | BUFFER SORT | | 1 | 42 | 1 (0)| 00:00:01 | | |
|* 10 | TABLE ACCESS BY INDEX ROWID| INVC | 1 | 42 | 1 (0)| 00:00:01 | | |
|* 11 | INDEX RANGE SCAN | IX5_INVC | 1 | | 1 (0)| 00:00:01 | | |
| 12 | TABLE ACCESS BY INDEX ROWID | INVC_ITEM | 1 | 29 | 1 (0)| 00:00:01 | | |
|* 13 | INDEX UNIQUE SCAN | PK_INVC_ITEM | 1 | | 1 (0)| 00:00:01 | | |
|* 14 | INDEX UNIQUE SCAN | PK_CARR | 1 | 4 | 1 (0)| 00:00:01 | | |
| 15 | TABLE ACCESS FULL | MNFST | 45708 | 1071K| 165 (2)| 00:00:02 | | |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("A"."MNFST_NR"="D"."MNFST_NR")
8 - filter("FCR"."WORK_DT"=TRUNC(SYSDATE@!) AND "FCR"."RORDR_SHRT_RQST_END_TS" IS NULL)
10 - filter("A"."MNFST_NR" IS NOT NULL)
11 - access("A"."ORD_ID"=120320 AND "A"."JULIAN_DT"=58)
13 - access("A"."ACCT_NR"="B"."ACCT_NR" AND "A"."CMPGN_YR"="B"."CMPGN_YR" AND
"A"."CMPGN_NR"="B"."CMPGN_NR" AND "B"."ORD_ID"=120320 AND "B"."VOL_NR"=001 AND
"B"."FNSH_STK_CD"=TO_NUMBER("FCR"."FNSH_STK_CD"))
14 - access("A"."WRHS_CD"="C"."WRHS_CD")

Remote SQL Information (identified by operation id):
----------------------------------------------------

1 - EXPLAIN PLAN INTO PLAN_TABLE@! FOR SELECT "A1"."CMPGN_NR" FROM (SELECT "A2"."OPERTNL_CMPGN_NR"
"CMPGN_NR" FROM "OW_ODSQ"."OPERTNL_CMPGN" "A2" WHERE "A2"."OPERTNL_CMPGN_STRT_DT"<=TRUNC(SYSDATE@!) AND
"A2"."OPERTNL_CMPGN_END_DT">=TRUNC(SYSDATE@!) AND "A2"."OPERTNL_CMPGN_NR"<>20 ORDER BY
"A2"."OPERTNL_CMPGN_NR") "A1" WHERE ROWNUM=1 (accessing 'LCLODSQ_SIRQX2' )

2 - EXPLAIN PLAN INTO PLAN_TABLE@! FOR SELECT "A1"."CMPGN_YR" FROM (SELECT "A2"."OPERTNL_CMPGN_YR_NR"
"CMPGN_YR" FROM "OW_ODSQ"."OPERTNL_CMPGN" "A2" WHERE "A2"."OPERTNL_CMPGN_STRT_DT"<=TRUNC(SYSDATE@!) AND
"A2"."OPERTNL_CMPGN_END_DT">=TRUNC(SYSDATE@!) AND "A2"."OPERTNL_CMPGN_NR"<>20 ORDER BY
"A2"."OPERTNL_CMPGN_NR") "A1" WHERE ROWNUM=1 (accessing 'LCLODSQ_SIRQX2' )

Please help me what can be done to make this faster.

Best Regards..

Oracle Database

Last Comment

David VanZandt

8/22/2022 - Mon

David VanZandt

3/17/2012

What can be done includes statement hints, indexes, table statistics, system load, partitioned tables, etc. The first (real) issue that catches my eye is your Cartesian Join (full table scan). Very, very bad for performance, see http://www.dba-oracle.com/t_merge_join_cartesian.htm et.al.

TOAD, and perhaps the latest version of SQL*Developer, have ways to suggest code rewrites for better utilization. But the bottom line is if you reading all rows, find a way to cut down the number of rows required (purge, partition).

ASKER CERTIFIED SOLUTION

yuching

3/18/2012

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Aravindan GP

3/25/2012

ASKER

Hi, Based on your suggestion , i went for the change in query and due to some functionality changes, i had to drop two tables already used in query and below is my latest query.

SELECT a.acct_nr, a.ord_id, b.vol_nr, b.fnsh_stk_cd,
fcr.rordr_shrt_rqst_strt_ts, b.prod_qty, a.zone_cd, a.colctn_typ,
a.wrhs_cd, a.fscl_nte_dt, e.currnt_cmpgn_nr, e.currnt_cmpgn_yr,
a.julian_dt, fcr.actn_desc, fcr.fsc_desc
FROM sir_invc a INNER JOIN sir_invc_item b
ON a.acct_nr = b.acct_nr
AND a.cmpgn_yr = b.cmpgn_yr
AND a.cmpgn_nr = b.cmpgn_nr
AND a.ord_id = b.ord_id
INNER JOIN
(SELECT MAX (rordr_shrt_rqst_strt_ts) AS rordr_shrt_rqst_strt_ts,
actn_desc, fnsh_stk_cd, fsc_desc, in68_cd
FROM sir_fsc_cncltn_rqst
WHERE work_dt = TRUNC (SYSDATE)
AND rordr_shrt_rqst_end_ts IS NULL
AND in68_cd = 125
GROUP BY actn_desc,
fsc_desc,
fnsh_stk_cd,
rordr_shrt_rqst_strt_ts,
in68_cd) fcr ON b.fnsh_stk_cd = fcr.fnsh_stk_cd
INNER JOIN
(SELECT MIN (vcc.cmpgn_nr) AS currnt_cmpgn_nr,
MIN (vcc.cmpgn_yr) AS currnt_cmpgn_yr
FROM sir_vw_cmpgn_calndr vcc
WHERE TRUNC (SYSDATE) BETWEEN vcc.init_dt AND vcc.end_dt) e
ON 1 = 1
WHERE a.julian_dt = 58 AND a.ord_id = 120320 AND b.vol_nr = 001
ORDER BY b.fnsh_stk_cd DESC

Query execution plan :

PLAN_TABLE_OUTPUT

Plan hash value: 3514400056

------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 165 | 10 (20)| 00:00:01 | | |
| 1 | SORT ORDER BY | | 1 | 165 | 10 (20)| 00:00:01 | | |
| 2 | NESTED LOOPS | | 1 | 165 | 9 (12)| 00:00:01 | | |
| 3 | MERGE JOIN CARTESIAN | | 1 | 131 | 8 (13)| 00:00:01 | | |
| 4 | NESTED LOOPS | | 1 | 60 | 4 (0)| 00:00:01 | | |
| 5 | VIEW | | 1 | 26 | 3 (0)| 00:00:01 | | |
| 6 | REMOTE | | | | | | LCLOD~ | R->S |
| 7 | TABLE ACCESS BY INDEX ROWID | INVC | 1 | 34 | 1 (0)| 00:00:01 | | |
|* 8 | INDEX RANGE SCAN | IX5_INVC | 1 | | 1 (0)| 00:00:01 | | |
| 9 | BUFFER SORT | | 1 | 71 | 7 (15)| 00:00:01 | | |
| 10 | VIEW | | 1 | 71 | 4 (25)| 00:00:01 | | |
| 11 | HASH GROUP BY | | 1 | 122 | 4 (25)| 00:00:01 | | |
|* 12 | TABLE ACCESS BY INDEX ROWID| FSC_CNCLTN_RQST | 1 | 122 | 3 (0)| 00:00:01 | | |
|* 13 | INDEX RANGE SCAN | PK_FSC_CNCLTN_RQST | 286 | | 1 (0)| 00:00:01 | | |
| 14 | TABLE ACCESS BY INDEX ROWID | INVC_ITEM | 1 | 34 | 1 (0)| 00:00:01 | | |
|* 15 | INDEX UNIQUE SCAN | PK_INVC_ITEM | 1 | | 1 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

8 - access("A"."ORD_ID"=120320 AND "A"."JULIAN_DT"=58)
12 - filter("RORDR_SHRT_RQST_END_TS" IS NULL AND "WORK_DT"=TRUNC(SYSDATE@!))
13 - access("IN68_CD"=125)
15 - access("A"."ACCT_NR"="B"."ACCT_NR" AND "A"."CMPGN_YR"="B"."CMPGN_YR" AND "A"."CMPGN_NR"="B"."CMPGN_NR"
AND "B"."ORD_ID"=120320 AND "B"."VOL_NR"=001 AND "B"."FNSH_STK_CD"=TO_NUMBER("FCR"."FNSH_STK_CD"))

Remote SQL Information (identified by operation id):
----------------------------------------------------

6 - EXPLAIN PLAN INTO PLAN_TABLE@! FOR SELECT MIN("A1"."OPERTNL_CMPGN_NR"),MIN("A1"."OPERTNL_CMPGN_YR_NR")
FROM "OW_ODSD"."OPERTNL_CMPGN" "A1" WHERE "A1"."OPERTNL_CMPGN_STRT_DT"<=TRUNC(SYSDATE@!) AND
"A1"."OPERTNL_CMPGN_END_DT">=TRUNC(SYSDATE@!) AND "A1"."OPERTNL_CMPGN_NR"<>20 (accessing 'LCLODSD_SIRDX2' )

Can we reduce the cost further on this?
Please help me , thanks.

yuching

3/26/2012

Hi neoarwin, the explain plan looks just fine. All are using index scan.
What's the time to run the query?
Can you also post the number of records in each tables?

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

Aravindan GP

4/9/2012

ASKER

I've requested that this question be closed as follows:

Accepted answer: 0 points for neoarwin's comment #37762763

for the following reason:

good

David VanZandt

4/9/2012

The author states the solution is based upon experts' suggestions.

David VanZandt

4/13/2012

My objections are that the author did not provide a workable solution to his/her problem, but instead replied "good" in http:#a37823137. I recommend yu's answer in http:#37736245.

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.