Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Query tuning same query different question

Posted on 2012-03-22
9
Medium Priority
?
393 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
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 78

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 78

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

564 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