Oracle performance Tuning

Question

I have below oracle query. It is taking very long time to run. Can you please suggest some performance tuning for this query.

select ss.registration_id as REGISTRATION_ID,
       ss.batch_id as BATCH_ID
  from submtd_srvc PARTITION(SUBMTD_SRVC_821370) ss,
       (select a.exceptn_criteria_val,
               a.exceptn_criteria_rtrn_val 
          from EXCEPTN_CRITERIA a,
               EXCEPTN_EXPRESSION b
          where a.EXCEPTN_EXPRESSION_ID = b.EXCEPTN_EXPRESSION_ID
            and b.EXCEPTN_EXPRESSION_NAME = 'NC_CUSTOM_REV_CAT'
            and b.IS_CURRENT_INDCTR = 1
            and a.IS_CURRENT_INDCTR = 1) sub_query
  where ss.REVENUE_CD = sub_query.exceptn_criteria_val
    and ss.batch_id =  821370
    and exists (select 'x'
                  from submtd_srvc PARTITION(SUBMTD_SRVC_821370) ss2,
                       (select a.exceptn_criteria_val,
                               a.exceptn_criteria_rtrn_val 
                          from EXCEPTN_CRITERIA a,
                               EXCEPTN_EXPRESSION b
                          where a.EXCEPTN_EXPRESSION_ID = b.EXCEPTN_EXPRESSION_ID
                            and b.EXCEPTN_EXPRESSION_NAME = 'NC_CUSTOM_REV_CAT'
                            and b.IS_CURRENT_INDCTR = 1
                            and a.IS_CURRENT_INDCTR = 1) sub_query2
                  where ss2.REVENUE_CD = sub_query2.exceptn_criteria_val
                    and ss2.registration_id = ss.registration_id
                    and ss2.batch_id = ss.batch_id
                    and ss2.batch_id = 821370
                    and sub_query2.exceptn_criteria_rtrn_val <> sub_query.exceptn_criteria_rtrn_val)
  Order By Ss.Registration_Id,
           ss.batch_id;

It would be useful if you could post your explain plan and the structure of your tables and indexes — Aleksej, Mar 10 '16 at 14:47
I personally would get rid of `exists` clause and use `join`. In my situation it worked faster. Apart from it see the explain plan and find where are you doing full table scans. If possible create index on them. — Utsav, Mar 10 '16 at 14:51
To post the query is typically not enough information; [here](http://stackoverflow.com/questions/34975406/how-to-describe-performance-issue-in-relational-database) you may find some hints what you should post. — Marmite Bomber, Mar 10 '16 at 14:53

score 2 · Answer 1 · answered Mar 10 '16 at 14:56

If I understood correctly you use correlation subquery for finding rows with more than one distinct exceptn_criteria_rtrn_val value. If I'm right will be better use analytic function:

select vw.registration_id as REGISTRATION_ID, vw.batch_id as BATCH_ID
from
 (select ss.registration_id as REGISTRATION_ID, ss.batch_id as BATCH_ID
        ,count(distinct sub_query.exceptn_criteria_rtrn_val) over(partition by ss.registration_id, ss.batch_id) as cnt
    from submtd_srvc PARTITION(SUBMTD_SRVC_821370) ss,
         (select a.exceptn_criteria_val, a.exceptn_criteria_rtrn_val
            from EXCEPTN_CRITERIA a, EXCEPTN_EXPRESSION b
           where a.EXCEPTN_EXPRESSION_ID = b.EXCEPTN_EXPRESSION_ID
             and b.EXCEPTN_EXPRESSION_NAME = 'NC_CUSTOM_REV_CAT'
             and b.IS_CURRENT_INDCTR = 1
             and a.IS_CURRENT_INDCTR = 1) sub_query
   where ss.REVENUE_CD = sub_query.exceptn_criteria_val
     and ss.batch_id = 821370) vw
where cnt > 1
Order By vw.Registration_Id, vw.batch_id;

score 1 · Answer 2 · answered Mar 10 '16 at 15:14

You can replace the EXISTS with GROUP BY and HAVING:

SELECT ss.registration_id,
       ss.batch_id
FROM   submtd_srvc PARTITION(SUBMTD_SRVC_821370) ss
       INNER JOIN
       EXCEPTN_CRITERIA a
       ON ( ss.REVENUE_CD = a.exceptn_criteria_val )
       INNER JOIN
       EXCEPTN_EXPRESSION b
       ON ( a.EXCEPTN_EXPRESSION_ID = b.EXCEPTN_EXPRESSION_ID )
WHERE  b.EXCEPTN_EXPRESSION_NAME = 'NC_CUSTOM_REV_CAT'
AND    b.IS_CURRENT_INDCTR = 1
AND    a.IS_CURRENT_INDCTR = 1
AND    ss.batch_id =  821370
GROUP BY
       ss.Registration_Id,
       ss.batch_id
HAVING COUNT( DISTINCT a.exceptn_criteria_rtrn_val ) = 1
ORDER BY
       ss.Registration_Id,
       ss.batch_id;

I've also got rid of all the sub-queries and used ANSI standard joins rather than the legacy Oracle joins.

Oracle performance Tuning

2 Answers2

Linked