Re: Oracle performance tuning question

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 ;
Go into the init.ora and set FAST=TRUE ... sorry ... just plagiarizing Tom Kyte.

Lots of questions ... not so many suggestions. Here they are in no particular order?

1. Whatis slow?
2. What hardware platform?
3. What O/S?
4. What version and edition of Oracle?
5. What indexes?
6. Have you verified the indexes are being used by running EXPLAIN PLAN?
7. Have you run TKPROF on a trace file?
8. Is the ORDER BY really needed?
9. Have statistics been generated
10. Are you using RBO or CBO?
11. Why is gl_sysdate stored as a string?

Daniel Morgan Received on Mon Jan 20 2003 - 10:49:01 CST