Solved

Query tuning same query different question

Posted on 2012-03-22
9
375 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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
"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
 

Author Comment

by:neoarwin
Comment Utility
It takes around 6 to 10 seconds and I wanted my query to give results immediately.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>>>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
Comment Utility
- 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
Comment Utility
Thanks for your insights..

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

Accepted Solution

by:
sujith80 earned 400 total points
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
can you give answers to the questions in http:#a37755807?

also, please revisit previous question about grading issue
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now