Solved

Query tuning

Posted on 2012-03-22
13
323 Views
Last Modified: 2012-03-25
Query :

select a.ord_id from sir_INVC_CNCLTN_RQST cncl,sir_invc a ,sir_invc_vol b
 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 cncl.ORD_ID=a.ord_id
 and cncl.ACCOUNT_NUMBER=a.acct_nr

Explain plan:

PLAN_TABLE_OUTPUT

Plan hash value: 1107352177
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |     1 |    50 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |                     |     1 |    50 |     3   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |                     |     1 |    33 |     2   (0)| 00:00:01 |
|*  3 |    INDEX SKIP SCAN           | PK_INVC_CNCLTN_RQST |     1 |    11 |     1   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | PK_INVC_VOL         |     1 |    22 |     1   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| INVC                |     1 |    17 |     1   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN         | PK_INVC             |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("CNCL"."ORD_ID"=844689)
       filter("CNCL"."ORD_ID"=844689)
   4 - access("B"."ACCT_NR"="CNCL"."ACCOUNT_NUMBER" AND "B"."ORD_ID"=844689 AND
              "B"."VOL_NR"=001)
       filter("B"."ORD_ID"=844689 AND "B"."VOL_NR"=001 AND "B"."ORD_ID"="CNCL"."ORD_ID")
   5 - filter("A"."JULIAN_DT"=206)
   6 - access("CNCL"."ACCOUNT_NUMBER"="A"."ACCT_NR" AND "A"."CMPGN_YR"="B"."CMPGN_YR" AND
              "A"."CMPGN_NR"="B"."CMPGN_NR" AND "A"."ORD_ID"=844689)
       filter("A"."ACCT_NR"="B"."ACCT_NR")

Please let me know why this nested loop is shwoing in execution plan, how to resolve it.

Regards.
0
Comment
Question by:neoarwin
  • 7
  • 5
13 Comments
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 350 total points
ID: 37754373
what would you expect?  

the expected cardinalities (rows) of each table's filters is 1.  So  looping seems very efficient.

if the row count is wrong, perhaps you need to gather new statistics
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37754377
another option,  set your optimizer_dynamic_sampling to 4 or higher and let it gather join statistics for you
0
 

Author Comment

by:neoarwin
ID: 37754419
I am of the perception that looping is a reason for delaying a query, result set is okay with this query. Thanks for explanation.

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
 
LVL 8

Expert Comment

by:Christoffer Swanström
ID: 37754424
Why do you think the nested loop join is a problem? How many rows do you expect from each table given the WHERE conditions, and how many rows are there in total in each table?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37754472
chaining additional queries within a single question isn't appropriate use of the EE system.

if you have nothing further on the original question, please close this one and open a new question for your new query.
0
 

Author Comment

by:neoarwin
ID: 37754487
I get your point if the fetching number of rows increases then I have a problem with nested loops, but on my execution plan, it shows only one row and I need only row only as result set.

Can you please take a look at the above query and tell me why this MERGE JOIN CARTESIAN is happening?? How to resolve this..Usually all the tables I have used are high volume tables,its only the joins and filter conditions makes it one row.
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
ID: 37754493
>>> Can you please take a look at the above query and tell me why this MERGE JOIN CARTESIAN is happening??

if you have nothing further on the original question, please close this one and open a new question for your new query.
0
 

Author Comment

by:neoarwin
ID: 37754611
Above query is the upgraded version of the first query I posted. The query and the tables used are grown. Can you please explain me about merge join cartesian, instead of asking me about posting new questions. I am not finding any one actively participating in solving my issue.

I need help and I am asking questions, do i have to upgrade my query and post new questions everyt time especially when my first question is not even answered properly.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37754623
>>> especially when my first question is not even answered properly.

please let me know what else you'd like to follow up on with respect to the original query.
0
 

Author Comment

by:neoarwin
ID: 37754652
how to set optmizer dynamic sampling and run the execution plan ??
does this counts as another question?

I opened another question for the above query now check and let me know please.
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 350 total points
ID: 37755798
I'd say it's a reasonable followup as it pertains to comments posted to address the original question.

alter session set optimizer_dynamic_sampling=4;

looks like you already have plans, but here's how


explain plan for
select ....;   -- put your select statement here


select * from table(dbms_xplan.display);
0
 

Author Closing Comment

by:neoarwin
ID: 37762764
Thanks
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37763505
why the B?
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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup

743 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

14 Experts available now in Live!

Get 1:1 Help Now