troubleshooting Question

Query tuning help needed

Avatar of Aravindan GP
Aravindan GPFlag for United States of America asked on
Oracle Database
7 Comments1 Solution532 ViewsLast Modified:
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..
ASKER CERTIFIED SOLUTION
yuching

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros