This discussion is archived
3 Replies Latest reply: Feb 22, 2013 4:10 AM by 959687 RSS

Performance tuning for the query

959687 Newbie
Currently Being Moderated
CURSOR c_exercise_list IS
       SELECT
              DECODE(v_mfd_mask_id ,'Y',' ',o.opt_id) opt_id,
              DECODE(v_mfd_mask_id ,'Y',' ',o.soc_sec) soc_sec,
              P.plan_id plan_id, E.exer_id exer_id, E.exer_num,
              DECODE(G.sar_flag, 0, DECODE(G.plan_type, 0, '1', 1, '2', 2, '3', 3, ' ', 4,'5', 5, '6', 6, '7', 7, '8', 8, '9', '0'), ' ') option_type,
              TO_CHAR(G.grant_dt, 'YYYYMMDD') grant_dt, TO_CHAR(E.exer_dt, 'YYYYMMDD') exer_dt,
              E.opts_exer opts_exer,
              E.mkt_prc   mkt_prc,
              E.swap_prc  swap_prc,
              E.shrs_swap shrs_swap, decode(e.exer_type,2,decode(xe.cash_partial,'Y','A','2'),TO_CHAR(E.exer_type)) exer_type,
              E.sar_shrs  sar_shrs,
              NVL(ROUND(((xe.sar_shrs_withld_optcost - (e.opts_exer * g.opt_prc) / e.mkt_prc) * e.mkt_prc),2),0)+e.sar_cash sar_cash,
              NVL(f.fixed_fee1,0) fixed_fee1,
              NVL(f.fixed_fee2,0) fixed_fee2,
              NVL(f.fixed_fee3,0) fixed_fee3,
              NVL(f.commission,0) commission,
              NVL(f.sec_fee,0)    sec_fee,
              NVL(f.fees_paid,0)  fees_paid,
              NVL(ct.amount,0)     cash_tend,
              E.shrs_tend  shrs_tend, G.grant_id grant_id, NVL(G.grant_cd, ' ') grant_cd,
              NVL(xg.child_symbol,' ') child_symbol,
              NVL(xg.opt_gain_deferred_flag,'N') defer_flag,
              o.opt_num opt_num,
              --XO.new_ssn,
              DECODE(v_mfd_mask_id ,'Y',' ',xo.new_ssn) new_ssn,
                      xo.use_new_ssn
              ,xo.tax_verification_eligible tax_verification_eligible
              ,(SELECT TO_CHAR(MIN(settle_dt),'YYYYMMDD') FROM tb_ml_exer_upload WHERE exer_num = E.exer_num AND user_id=E.user_id AND NVL(settle_dt,TO_DATE('19000101','YYYYMMDD'))>=E.exer_dt) AS settle_dt
              ,xe.rsu_type  AS rsu_type
              ,xe.trfbl_det_name AS trfbl_det_name
              ,o.user_txt1,o.user_txt2,xo.user_txt3,xo.user_txt4,xo.user_txt5,xo.user_txt6,xo.user_txt7
              ,xo.user_txt8,xo.user_txt9,xo.user_txt10,xo.user_txt11,
              xo.user_txt12,
              xo.user_txt13,
              xo.user_txt14,
              xo.user_txt15,
              xo.user_txt16,
              xo.user_txt17,
              xo.user_txt18,
              xo.user_txt19,
              xo.user_txt20,
              xo.user_txt21,
              xo.user_txt22,
              xo.user_txt23,
              xo.user_dt2,
              xo.adj_dt_hire_vt_svc,
              xo.adj_dt_hire_vt_svc_or,
              xo.adj_dt_hire_vt_svc_or_dt,
              xo.severance_plan_code,
              xo.severance_begin_dt,
              xo.severance_end_dt,
              xo.retirement_bridging_dt
              ,NVL(xg.pu_var_price ,0) v_pu_var_price
              ,NVL(xe.ficamed_override,'N') v_ficmd_ovrride
              ,NVL(xe.vest_shrs,0) v_vest_shrs
              ,NVL(xe.client_exer_id,' ') v_client_exer_id
              ,(CASE WHEN xg.re_tax_flag = 'Y' THEN pk_xop_reg_outbound.Fn_GetRETaxesWithheld(g.grant_num, E.exer_num, g.plan_type)
                     ELSE 'N'
                 END) re_tax_indicator -- 1.5V
              ,xe.je_bypass_flag
              ,xe.sar_shrs_withld_taxes   --Added for SAR july 2010 release
              ,xe.sar_shrs_withld_optcost --Added for SAR july 2010 release
        FROM
        (SELECT exer.* FROM exercise exer WHERE NOT EXISTS (SELECT s.exer_num FROM suspense s
            WHERE s.exer_num = exer.exer_num AND s.user_id = exer.user_id AND exer.mkt_prc = 0))E,
            grantz G,  xop_grantz xg, optionee o, xop_optionee xo, feeschgd f, cashtendered ct, planz P,xop_exercise xe
        WHERE
              E.grant_num  = G.grant_num
        AND   E.user_id    = G.user_id
        AND   E.opt_num    = o.opt_num
        AND   E.user_id    = o.user_id
        AND   (G.grant_num = xg.grant_num(+) AND G.user_id=xg.user_id(+))
        AND   (o.opt_num   = xo.opt_num(+)   AND o.user_id=xo.user_id(+))
        AND   E.plan_num = P.plan_num
        AND   E.user_id = P.user_id
        AND   E.exer_num = f.exer_num(+)
        AND   E.user_id = ct.user_id(+)
        AND   E.exer_num = ct.exer_num(+)
        AND   E.user_id = ct.user_id(+)
        AND   E.exer_num=xe.exer_num(+)
        AND   E.user_id=xe.user_id(+)
        AND   G.user_id = USER
        AND NOT EXISTS (
                    SELECT tv.exer_num
                      FROM tb_xop_tax_verification tv--,exercise ex
                     WHERE tv.exer_num = e.exer_num
                       AND tv.user_id = e.user_id
                       AND tv.user_id = v_cms_user
                       AND tv.status_flag IN (0,1,3,4, 5)) -- Not Processed
        ;
how to tune the query to impropve the performance, any1 help me ..thanks in advance

Edited by: BluShadow on 21-Feb-2013 08:14
corrected {noformat}
{noformat} tags. Please read {message:id=9360002} and learn how to post code correctly.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 1. Re: Performance tuning for the query
    Karthick_Arp Guru
    Currently Being Moderated
    Information provided by you is insufficient. Please read {message:id=9360003} from FAQ and update your post accordingly.
  • 2. Re: Performance tuning for the query
    959687 Newbie
    Currently Being Moderated
    i got CPU cost: 458.50 Elapsed time: 1542.90 so anything can tune to improve the performance, but there is no full table scan applied for none of the mentioned table. . and most of the columns are unique index scan takes place.. anybody can help me to find the solution

    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
    PL/SQL Release 10.2.0.4.0 - Production
    CORE     10.2.0.4.0     Production
    TNS for Solaris: Version 10.2.0.4.0 - Production
    NLSRTL Version 10.2.0.4.0 - Production

    Edited by: 956684 on Feb 22, 2013 4:09 AM
  • 3. Re: Performance tuning for the query
    Karthick_Arp Guru
    Currently Being Moderated
    956684 wrote:
    i got CPU cost: 458.50 Elapsed time: 1542.90 so anything can tune to improve the performance, but there is no full table scan applied for none of the mentioned table. . and most of the columns are unique index scan takes place.. anybody can help me to find the solution
    Its like asking "My car is not working, the color of the care is gray. Can you fix it?"

    Please read the FAQ i have already posted and follow the instructions.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points