Re: Oracle performance tuning question

2 things first:

Analyse the tables involved, then
Run an EXPLAIN PLAN on the query and let us see the results..

dmx_frank_at_yahoo.com (Frank Y) wrote:

>Hi all,
>
>I found the execute of following query in my DB is very slow, any one
>has suggestion to make it fast. Thanks very much.
> SELECT WT_SVR_LVL,
> USR_NTF_TY,
> WT_TY,
> WT_NO,
> WT_VER_NO,
> COMMO_DESC_100,
> IMPR_EXPR_NAME,
> IMPR_EXPR_CNAME,
> SHPR_CNGE_NAME,
> SHPR_CNGE_CNAME,
> IMPR_EXPR_ADDR_1||' '||IMPR_EXPR_ADDR_2||' '||
> IMPR_EXPR_ADDR_3||' '||IMPR_EXPR_ADDR_4||' '||
> IMPR_EXPR_ADDR_5,
> SHPR_CNGE_ADDR_1||' '||SHPR_CNGE_ADDR_2||' '||
> SHPR_CNGE_ADDR_3||' '||SHPR_CNGE_ADDR_4||' '||
> SHPR_CNGE_ADDR_5,
> IMP_CNSTR_CD,
> TSHPT_CNSTR_CD,
> EXP_CNSTR_CD,
> IMP_CNTY_CD,
> TSHPT_CNTY_CD_ORIG,
> TSHPT_CNTY_CD_DEST,
> EXP_CNTY_CD
> FROM VHTGTLT1
> WHERE ( WT_TY, WT_NO, WT_VER_NO ) IN
> (SELECT WT_TY, WT_NO, WT_VER_NO
> FROM VMTLNAME1
> WHERE (((SSA_NAME between :l_ie_srch_range.from[0] AND
>:l_ie_srch_range.to[0])
> AND NAME_TY=:l_ie_name_ty)
> OR ((SSA_NAME between :l_sc_srch_range.from[0] AND
>:l_sc_srch_range.to[0])
> AND NAME_TY=:l_sc_name_ty))
> AND (SHPT_MOD=:p_pri_shpt_mod OR
>SHPT_MOD=:p_sec_shpt_mod)
> AND EFF_STR_DT <= to_date(:gl_sysdate,'YYYYMMDD
>HH24:MI:SS')
> AND EFF_END_DT >= to_date(:gl_sysdate,'YYYYMMDD
>HH24:MI:SS')
> AND (RISK_GP in (:p_risk_gp, '#')
> OR RISK_GP_2 = :p_risk_gp
> OR RISK_GP_3 = :p_risk_gp
> OR RISK_GP_4 = :p_risk_gp
> OR RISK_GP_5 = :p_risk_gp)
> AND DEST in (:p_dest, '#')
> AND (PT_ORIG_1 in (:p_pt_orig, '#')
> OR PT_ORIG_2 = :p_pt_orig
> OR PT_ORIG_3 = :p_pt_orig)
> UNION
> SELECT WT_TY, WT_NO, WT_VER_NO
> FROM VMTLNAME1
> WHERE SSA_NAME='#'
> AND (SHPT_MOD=:p_pri_shpt_mod OR
>SHPT_MOD=:p_sec_shpt_mod)
> AND (NAME_TY=:l_ie_name_ty OR NAME_TY=:l_sc_name_ty)
> AND EFF_STR_DT <= to_date(:gl_sysdate,'YYYYMMDD
>HH24:MI:SS')
> AND EFF_END_DT >= to_date(:gl_sysdate,'YYYYMMDD
>HH24:MI:SS')
> AND (RISK_GP in (:p_risk_gp, '#')
> OR RISK_GP_2 = :p_risk_gp
> OR RISK_GP_3 = :p_risk_gp
> OR RISK_GP_4 = :p_risk_gp
> OR RISK_GP_5 = :p_risk_gp)
> AND DEST in (:p_dest, '#')
> AND (PT_ORIG_1 in (:p_pt_orig, '#')
> OR PT_ORIG_2 = :p_pt_orig
> OR PT_ORIG_3 = :p_pt_orig)
> GROUP BY WT_TY, WT_NO, WT_VER_NO ) //end of union
> AND (DEL_IND != 'Y' OR DEL_IND IS NULL )
> ORDER BY WT_TY, WT_NO, WT_VER_NO ;
Received on Wed Jan 22 2003 - 11:26:46 CST