Solved

Query tuning same query different question

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

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
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.

 

Author Comment

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

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 400 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 73

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
report returning null 21 92
Oracle DATE Column Space 11 75
Performance issue with case statement in oracle 11G 7 60
Oracle query output question 4 43
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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 shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

805 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