Query tuning

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.
neoarwinAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
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
 
sdstuberConnect With a Mentor Commented:
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
 
sdstuberCommented:
another option,  set your optimizer_dynamic_sampling to 4 or higher and let it gather join statistics for you
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
neoarwinAuthor Commented:
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
 
Christoffer SwanströmPartnerCommented:
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
 
sdstuberCommented:
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
 
neoarwinAuthor Commented:
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
 
sdstuberCommented:
>>> 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
 
neoarwinAuthor Commented:
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
 
sdstuberCommented:
>>> 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
 
neoarwinAuthor Commented:
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
 
neoarwinAuthor Commented:
Thanks
0
 
sdstuberCommented:
why the B?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.