3 Replies Latest reply: Feb 22, 2013 6:10 AM by 959687 RSS

    Performance tuning for the query

    959687
      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
          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
            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
              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.