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.
Aravindan GPAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
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
Aravindan GPAuthor 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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
Aravindan GPAuthor 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
Aravindan GPAuthor 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
Aravindan GPAuthor 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
sdstuberCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Aravindan GPAuthor Commented:
Thanks
0
sdstuberCommented:
why the B?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.