Query tuning same query different question

Please take a look at the below query. Merge cartesian is happening, how to resolve it?

Query:
 SELECT /*+ index(d,PK_MNFST) */   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_invc_item b, sir_mnfst d,
(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)
    -- to_date(' + workDate + ', 'dd/MM/yyyy')
    AND RORDR_SHRT_RQST_END_TS is null
    group by actn_desc,fsc_desc,fnsh_stk_cd,rordr_shrt_rqst_strt_ts,in68_cd)FCR
WHERE a.julian_dt = 206
AND a.ord_id = 844689
AND b.vol_nr = 001
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.in68_cd= 121

Explain Plan :
PLAN_TABLE_OUTPUT

Plan hash value: 1616848067
 
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                    |     1 |   167 |     5  (20)| 00:00:01 |        |      |
|   1 |  REMOTE                           |                    |       |       |            |          | LCLOD~ | R->S |
|   2 |  REMOTE                           |                    |       |       |            |          | LCLOD~ | R->S |
|   3 |  TABLE ACCESS BY INDEX ROWID      | MNFST              |     1 |    24 |     1   (0)| 00:00:01 |        |      |
|   4 |   NESTED LOOPS                    |                    |     1 |   167 |     5  (20)| 00:00:01 |        |      |
|   5 |    NESTED LOOPS                   |                    |     1 |   143 |     4  (25)| 00:00:01 |        |      |
|   6 |     MERGE JOIN CARTESIAN          |                    |     1 |   109 |     3  (34)| 00:00:01 |        |      |
|   7 |      VIEW                         |                    |     1 |    71 |     2  (50)| 00:00:01 |        |      |
|   8 |       HASH GROUP BY               |                    |     1 |   108 |     2  (50)| 00:00:01 |        |      |
|*  9 |        TABLE ACCESS BY INDEX ROWID| FSC_CNCLTN_RQST    |     1 |   108 |     1   (0)| 00:00:01 |        |      |
|* 10 |         INDEX RANGE SCAN          | PK_FSC_CNCLTN_RQST |     5 |       |     1   (0)| 00:00:01 |        |      |
|  11 |      BUFFER SORT                  |                    |     1 |    38 |     3  (34)| 00:00:01 |        |      |
|* 12 |       TABLE ACCESS BY INDEX ROWID | INVC               |     1 |    38 |     1   (0)| 00:00:01 |        |      |
|* 13 |        INDEX RANGE SCAN           | IX5_INVC           |     1 |       |     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 |        |      |
|* 16 |    INDEX FULL SCAN                | PK_MNFST           |     1 |       |     1   (0)| 00:00:01 |        |      |
------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   9 - filter("RORDR_SHRT_RQST_END_TS" IS NULL AND "WORK_DT"=TRUNC(SYSDATE@!))
  10 - access("IN68_CD"=121)
  12 - filter("A"."MNFST_NR" IS NOT NULL)
  13 - access("A"."ORD_ID"=844689 AND "A"."JULIAN_DT"=206)
  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"=844689 AND "B"."VOL_NR"=001 AND "B"."FNSH_STK_CD"=TO_NUMBER("FCR"."FNSH_STK_CD"))
  16 - access("A"."MNFST_NR"="D"."MNFST_NR")
       filter("A"."MNFST_NR"="D"."MNFST_NR")
 
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_ODSD"."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 'LCLODSD_SIRDX2' )
 
   2 - EXPLAIN PLAN INTO PLAN_TABLE@! FOR SELECT "A1"."CMPGN_YR" FROM  (SELECT "A2"."OPERTNL_CMPGN_YR_NR"
       "CMPGN_YR" FROM "OW_ODSD"."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 'LCLODSD_SIRDX2' )
 
Take a look and let me know your comments.
Aravindan GPAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Why do you think you need to?

The optimizer can chose a cartesian if it is quicker.  They are not always bad.

Are you trying to tune based on the explain plan?  If so, why?
Aravindan GPAuthor Commented:
Then how can I tune a query , I need to minimize the cost.
I tried minimizing it and the above is where I ended with, I still need to reduce it little bit and am just looking at the way I can reduce. since few things like Cartesian join etc which are costly and not desirable, I wanted to eliminate it.
That Cartesian join happening between two different tables and I don see the reason why they get joined like that.
slightwv (䄆 Netminder) Commented:
"faster" is not really a tuning goal.  Again, a cartesian when done properly is not a bad thing.  The Oracle optimizer can snooze to use them in certain situations.

Now long does the query take now and what is your goal?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Aravindan GPAuthor Commented:
It takes around 6 to 10 seconds and I wanted my query to give results immediately.
sdstuberCommented:
>>>I need to minimize the cost.

Don't use cost as a tuning measure.

As with your previous question,  are the estimated cardinalities reasonably accurate?

Are your expectations reasonable?

You have a hint in the query, what happens if you take it out?  What are the changes in performance and the plan?
SujithData ArchitectCommented:
- You are getting a CARTESIAN because you group FCR by a number of columns and join only on fnsh_stk_cd, try to restrict the join by including more join conditions with other tables
- from FCR you seem to be using only in68_cd = 121, you may restrict this in the sub query itself
- You are using two scalar sub queries on VCC, are they really required in this query, if possible take them out and run independently

Try the below,
WITH FCR as
(select /*+ MATERIALIZE*/ 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)
    	-- to_date(' + workDate + ', 'dd/MM/yyyy')
    	AND RORDR_SHRT_RQST_END_TS is null
    	AND FCR.in68_cd= 121
    	group by actn_desc,fsc_desc,fnsh_stk_cd,rordr_shrt_rqst_strt_ts,in68_cd	)
, CCN as
(SELECT /*+ MATERIALIZE*/ cmpgn_nr currnt_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) 
, CCY as
(SELECT /*+ MATERIALIZE*/ cmpgn_yr currnt_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) 
 SELECT /*+ index(d,PK_MNFST) */   
 a.acct_nr, a.ord_id, a.zone_cd, a.colctn_typ, a.wrhs_cd,a.FSCL_NTE_DT,  a.julian_dt, 
 b.vol_nr, b.fnsh_stk_cd, b.prod_qty, 
 d.cmpgn_yr, d.cmpgn_nr, 
 fcr.rordr_shrt_rqst_strt_ts, fcr.actn_desc, fcr.fsc_desc ,
trunc(d.inspctn_dt) LAST_MVMNT_DT, d.in68_cd, 
	CCN.currnt_cmpgn_nr, 
	CCY.currnt_cmpgn_yr 
FROM sir_invc a, sir_invc_item b, sir_mnfst d,	FCR , CCN, CCY
WHERE a.julian_dt = 206
AND a.ord_id = 844689
AND b.vol_nr = 001
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 

Open in new window

( fix syntax issues if any)
Aravindan GPAuthor Commented:
Thanks for your insights..

So mege cartesian in this query is inevitable right..
SujithData ArchitectCommented:
I cant really say if its inevitable. Because, i don't know the nature and statistics of your data and your data model. You may play around a bit on my suggestions above, and as mentioned in earlier posts, does the cardinalities in the plan match your data? are the stats up to date?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdstuberCommented:
can you give answers to the questions in http:#a37755807?

also, please revisit previous question about grading issue
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.