?
Solved

Query tuning same query different question

Posted on 2012-03-22
9
Medium Priority
?
385 Views
Last Modified: 2012-06-21
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.
0
Comment
Question by:neoarwin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37755166
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?
0
 

Author Comment

by:neoarwin
ID: 37755244
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.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37755323
"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?
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:neoarwin
ID: 37755753
It takes around 6 to 10 seconds and I wanted my query to give results immediately.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37755807
>>>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?
0
 
LVL 27

Expert Comment

by:sujith80
ID: 37760404
- 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)
0
 

Author Comment

by:neoarwin
ID: 37762469
Thanks for your insights..

So mege cartesian in this query is inevitable right..
0
 
LVL 27

Accepted Solution

by:
sujith80 earned 1200 total points
ID: 37762588
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?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37763823
can you give answers to the questions in http:#a37755807?

also, please revisit previous question about grading issue
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question