Home » RDBMS Server » Performance Tuning » Tuning (linux)
Tuning [message #642942] Wed, 23 September 2015 06:12 Go to next message
eml2raghu
Messages: 11
Registered: April 2007
Location: CHENNAI
Junior Member
CAn any one help me to tune the below query.


SELECT DISTINCT DECODE(RTRIM(RAT.CMY_CLS_CD), '9999','99',RAT.CMY_CLS_CD) sTierNumber,
RAT.DEL_ZN_NR,
CEIL (RAT.WGT_CGY_MIN_WGT_QY) MinWeight,
RAT.WGT_CGY_MAX_WGT_QY,
SUM (RAT.AC_SPL_BIL_TER_PR + RAT.CNS_SPL_BIL_TER_PR) Rate,
CCL_MTH_TYP_CD
FROM TRASTD RAT,TSVCZNE ZNE, TMRCHZN B
WHERE RAT.SVC_RA_CHT_NR = sChartNum
AND RAT.WGT_MS_UNT_TYP_CD = psWeightUnit
AND RAT.SVC_RA_CHT_STS_CD IN('05', '06')
AND RAT.CCL_MTH_TYP_CD IN ('M ', 'F ')
AND (sTmpProductCat != 'CWT'
AND SUBSTR(sTmpStateCode,1,2) = 'US'
AND SUBSTR(sTmpStateCode,3,2) != 'PR' )
AND RAT.SVC_RA_CHT_EFF_DT <= SYSDATE
AND RAT.SVC_RA_CHT_END_DT >= SYSDATE
AND ZNE.PKG_CHA_TYP_CD = psContainer
AND ZNE.SVC_FEA_TYP_CD = psProductCat
AND ZNE.SVM_TYP_CD = psDlvryMode
AND ZNE.PKG_ACQ_MTH_TYP_CD = psAcqSource
AND ZNE.ASY_SVC_TYP_CD = psAccessMode
AND ZNE.MVM_DRC_CD = psMovementIr
AND ZNE.AD_POL_DIV_1_NA IN (psCntCd)
AND ZNE.CNY_CD = psCntCd
AND ZNE.DEL_ZN_NR = RAT.DEL_ZN_NR
AND ZNE.PKG_TNS_NRS_IR = sInterIntra1
AND ZNE.SVC_ZNE_EFF_DT <= SYSDATE
AND ZNE.SVC_ZNE_END_DT >= SYSDATE
AND ZNE.cus_csf_typ_cd = x_ZN_NCV_TYP_CD
AND RAT.DEL_ZN_NR = B.DEL_ZN_NR(+)
AND ZNE.PKG_CHA_TYP_CD = B.PKG_CHA_TYP_CD(+)
AND ZNE.SVC_FEA_TYP_CD = B.SVC_FEA_TYP_CD(+)
AND ZNE.cus_csf_typ_cd = B.ZN_NCV_TYP_CD(+)
AND ZNE.DEL_ZN_NR = B.DEL_ZN_NR(+)
AND ZNE.SVM_TYP_CD = B.SVC_TYP_CD(+)
AND ZNE.MVM_DRC_cd = B.MVM_DRC_cd(+)
AND B.MVM_DRC_cd(+) = psMovementIr
AND B.ZN_NCV_TYP_CD(+) = x_ZN_NCV_TYP_CD
AND DECODE(psMovementIr,'E',B.ORG_CNY_CD(+),'I',B.DTN_CNY_CD(+), B.ORG_CNY_CD(+)) = psCntCd
AND DECODE(psMovementIr,'E',B.ORG_CNY_CD(+),'I',B.DTN_CNY_CD(+), B.ORG_CNY_CD(+)) = ZNE.CNY_CD
AND B.SVC_TYP_CD(+) = psDlvryMode
AND B.BIL_TER_TYP_CD(+) = sBillTermTypeCode
AND B.REC_EFF_STT_DT(+) <= sysdate
AND B.REC_EFF_END_DT(+) >= sysdate
GROUP BY RAT.CMY_CLS_CD,
RAT.DEL_ZN_NR,
RAT.WGT_CGY_MIN_WGT_QY,
RAT.WGT_CGY_MAX_WGT_QY,
CCL_MTH_TYP_CD
ORDER BY sTierNumber,
RAT.DEL_ZN_NR,
MinWeight,
RAT.WGT_CGY_MAX_WGT_QY,
CCL_MTH_TYP_CD;

regards,
rags
Re: Tuning [message #642954 is a reply to message #642942] Wed, 23 September 2015 07:27 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Go to the forum guide here, find the section on Performance tuning, post back with the relevant details.
tuning [message #643023 is a reply to message #642942] Fri, 25 September 2015 12:04 Go to previous messageGo to next message
eml2raghu
Messages: 11
Registered: April 2007
Location: CHENNAI
Junior Member
CAn any one help me to tune the below query.

SELECT DISTINCT Decode(Rtrim(RAT.cmy_cls_cd), '9999', '99', 
                                              RAT.cmy_cls_cd) sTierNumber, 
                RAT.del_zn_nr, 
                Ceil (RAT.wgt_cgy_min_wgt_qy)                 MinWeight, 
                RAT.wgt_cgy_max_wgt_qy, 
                SUM (RAT.ac_spl_bil_ter_pr 
                     + RAT.cns_spl_bil_ter_pr)                Rate, 
                ccl_mth_typ_cd 
FROM   trastd RAT, 
       tsvczne ZNE, 
       tmrchzn B 
WHERE  RAT.svc_ra_cht_nr = schartnum 
       AND RAT.wgt_ms_unt_typ_cd = psweightunit 
       AND RAT.svc_ra_cht_sts_cd IN( '05', '06' ) 
       AND RAT.ccl_mth_typ_cd IN ( 'M ', 'F ' ) 
       AND ( stmpproductcat != 'CWT' 
             AND Substr(stmpstatecode, 1, 2) = 'US' 
             AND Substr(stmpstatecode, 3, 2) != 'PR' ) 
       AND RAT.svc_ra_cht_eff_dt <= SYSDATE 
       AND RAT.svc_ra_cht_end_dt >= SYSDATE 
       AND ZNE.pkg_cha_typ_cd = pscontainer 
       AND ZNE.svc_fea_typ_cd = psproductcat 
       AND ZNE.svm_typ_cd = psdlvrymode 
       AND ZNE.pkg_acq_mth_typ_cd = psacqsource 
       AND ZNE.asy_svc_typ_cd = psaccessmode 
       AND ZNE.mvm_drc_cd = psmovementir 
       AND ZNE.ad_pol_div_1_na IN ( pscntcd ) 
       AND ZNE.cny_cd = pscntcd 
       AND ZNE.del_zn_nr = RAT.del_zn_nr 
       AND ZNE.pkg_tns_nrs_ir = sinterintra1 
       AND ZNE.svc_zne_eff_dt <= SYSDATE 
       AND ZNE.svc_zne_end_dt >= SYSDATE 
       AND ZNE.cus_csf_typ_cd = x_zn_ncv_typ_cd 
       AND RAT.del_zn_nr = B.del_zn_nr(+) 
       AND ZNE.pkg_cha_typ_cd = B.pkg_cha_typ_cd(+) 
       AND ZNE.svc_fea_typ_cd = B.svc_fea_typ_cd(+) 
       AND ZNE.cus_csf_typ_cd = B.zn_ncv_typ_cd(+) 
       AND ZNE.del_zn_nr = B.del_zn_nr(+) 
       AND ZNE.svm_typ_cd = B.svc_typ_cd(+) 
       AND ZNE.mvm_drc_cd = B.mvm_drc_cd(+) 
       AND B.mvm_drc_cd(+) = psmovementir 
       AND B.zn_ncv_typ_cd(+) = x_zn_ncv_typ_cd 
       AND Decode(psmovementir, 'E', B.org_cny_cd(+), 
                                'I', B.dtn_cny_cd(+), 
                                B.org_cny_cd(+)) = pscntcd 
       AND Decode(psmovementir, 'E', B.org_cny_cd(+), 
                                'I', B.dtn_cny_cd(+), 
                                B.org_cny_cd(+)) = ZNE.cny_cd 
       AND B.svc_typ_cd(+) = psdlvrymode 
       AND B.bil_ter_typ_cd(+) = sbilltermtypecode 
       AND B.rec_eff_stt_dt(+) <= SYSDATE 
       AND B.rec_eff_end_dt(+) >= SYSDATE 
GROUP  BY RAT.cmy_cls_cd, 
          RAT.del_zn_nr, 
          RAT.wgt_cgy_min_wgt_qy, 
          RAT.wgt_cgy_max_wgt_qy, 
          ccl_mth_typ_cd 
ORDER  BY stiernumber, 
          RAT.del_zn_nr, 
          minweight, 
          RAT.wgt_cgy_max_wgt_qy, 
          ccl_mth_typ_cd; 


*BlackSwan added {code} tags. Please do so yourself in the future.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

[Updated on: Fri, 25 September 2015 12:15] by Moderator

Report message to a moderator

Re: tuning [message #643024 is a reply to message #643023] Fri, 25 September 2015 12:13 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member

ORAFAQ tuning below -
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: tuning [message #643026 is a reply to message #643024] Fri, 25 September 2015 13:00 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It appears to me that all the columns in the SELECT clause all belong to trastd RAT table.
SQL might be faster after you remove both tsvczne ZNE & tmrchzn B tables out of the FROM clause, since they contribute no data to the SELECT clause.
Re: tuning [message #643028 is a reply to message #643026] Fri, 25 September 2015 23:26 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Do you know how to generate a query execution plan? You will need one to see where the CBO is doing the wrong thing.
Re: Tuning [message #643031 is a reply to message #642954] Sat, 26 September 2015 00:06 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
I use this website to format SQL. It is one option when you don't have a tool available.

SELECT DISTINCT Decode(Rtrim(RAT.cmy_cls_cd), '9999', '99', 
                                              RAT.cmy_cls_cd) sTierNumber, 
                RAT.del_zn_nr, 
                Ceil (RAT.wgt_cgy_min_wgt_qy)                 MinWeight, 
                RAT.wgt_cgy_max_wgt_qy, 
                SUM (RAT.ac_spl_bil_ter_pr 
                     + RAT.cns_spl_bil_ter_pr)                Rate, 
                ccl_mth_typ_cd 
FROM   trastd RAT, 
       tsvczne ZNE, 
       tmrchzn B 
WHERE  RAT.svc_ra_cht_nr = schartnum 
       AND RAT.wgt_ms_unt_typ_cd = psweightunit 
       AND RAT.svc_ra_cht_sts_cd IN( '05', '06' ) 
       AND RAT.ccl_mth_typ_cd IN ( 'M ', 'F ' ) 
       AND ( stmpproductcat != 'CWT' 
             AND Substr(stmpstatecode, 1, 2) = 'US' 
             AND Substr(stmpstatecode, 3, 2) != 'PR' ) 
       AND RAT.svc_ra_cht_eff_dt <= SYSDATE 
       AND RAT.svc_ra_cht_end_dt >= SYSDATE 
       AND ZNE.pkg_cha_typ_cd = pscontainer 
       AND ZNE.svc_fea_typ_cd = psproductcat 
       AND ZNE.svm_typ_cd = psdlvrymode 
       AND ZNE.pkg_acq_mth_typ_cd = psacqsource 
       AND ZNE.asy_svc_typ_cd = psaccessmode 
       AND ZNE.mvm_drc_cd = psmovementir 
       AND ZNE.ad_pol_div_1_na IN ( pscntcd ) 
       AND ZNE.cny_cd = pscntcd 
       AND ZNE.del_zn_nr = RAT.del_zn_nr 
       AND ZNE.pkg_tns_nrs_ir = sinterintra1 
       AND ZNE.svc_zne_eff_dt <= SYSDATE 
       AND ZNE.svc_zne_end_dt >= SYSDATE 
       AND ZNE.cus_csf_typ_cd = x_zn_ncv_typ_cd 
       AND RAT.del_zn_nr = B.del_zn_nr(+) 
       AND ZNE.pkg_cha_typ_cd = B.pkg_cha_typ_cd(+) 
       AND ZNE.svc_fea_typ_cd = B.svc_fea_typ_cd(+) 
       AND ZNE.cus_csf_typ_cd = B.zn_ncv_typ_cd(+) 
       AND ZNE.del_zn_nr = B.del_zn_nr(+) 
       AND ZNE.svm_typ_cd = B.svc_typ_cd(+) 
       AND ZNE.mvm_drc_cd = B.mvm_drc_cd(+) 
       AND B.mvm_drc_cd(+) = psmovementir 
       AND B.zn_ncv_typ_cd(+) = x_zn_ncv_typ_cd 
       AND Decode(psmovementir, 'E', B.org_cny_cd(+), 
                                'I', B.dtn_cny_cd(+), 
                                B.org_cny_cd(+)) = pscntcd 
       AND Decode(psmovementir, 'E', B.org_cny_cd(+), 
                                'I', B.dtn_cny_cd(+), 
                                B.org_cny_cd(+)) = ZNE.cny_cd 
       AND B.svc_typ_cd(+) = psdlvrymode 
       AND B.bil_ter_typ_cd(+) = sbilltermtypecode 
       AND B.rec_eff_stt_dt(+) <= SYSDATE 
       AND B.rec_eff_end_dt(+) >= SYSDATE 
GROUP  BY RAT.cmy_cls_cd, 
          RAT.del_zn_nr, 
          RAT.wgt_cgy_min_wgt_qy, 
          RAT.wgt_cgy_max_wgt_qy, 
          ccl_mth_typ_cd 
ORDER  BY stiernumber, 
          RAT.del_zn_nr, 
          minweight, 
          RAT.wgt_cgy_max_wgt_qy, 
          ccl_mth_typ_cd;   


If you are interested in SQL Tuning, I refer you to my book on SQL Tuning currently on sale on Amazon. Here are the free items from the book that you can use to decide if the book is something you want, before you have to spend any money on it. The book will teach you how to SQL Tune.

Please find attached:

Chapter #1 of the book (Oracle SQL Performance Tuning and Optimization: It's all about the Cardinalities)
scripts from the book
A new organizational tool used for doing a SQL Tuning session (The SQL Tuning Worksheet)
A brief discussion of what information is useful in tuning a SQL statement

Enjoy. Kevin

Re: tuning [message #643042 is a reply to message #643024] Sat, 26 September 2015 11:44 Go to previous messageGo to next message
eml2raghu
Messages: 11
Registered: April 2007
Location: CHENNAI
Junior Member
Hi..
please find the attachment and let me know, if this information will help tuning the query.

regards,
rags
Re: tuning [message #643043 is a reply to message #643042] Sat, 26 September 2015 11:58 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
eml2raghu wrote on Sat, 26 September 2015 09:44
Hi..
please find the attachment and let me know, if this information will help tuning the query.

regards,
rags


We can only help you after you post the previously requested details.

Do all the columns in the SELECT clause originate from a single table?
Re: tuning [message #643108 is a reply to message #643043] Tue, 29 September 2015 08:35 Go to previous messageGo to next message
eml2raghu
Messages: 11
Registered: April 2007
Location: CHENNAI
Junior Member
Hi,
Please find the attachment with all details

regards,
rags
Re: tuning [message #643109 is a reply to message #643108] Tue, 29 September 2015 08:37 Go to previous messageGo to next message
cookiemonster
Messages: 13915
Registered: September 2008
Location: Rainy Manchester
Senior Member
Again there is no attachment. Are you clicking the upload file button before you post the reply?
Re: tuning [message #643111 is a reply to message #643108] Tue, 29 September 2015 08:50 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
eml2raghu wrote on Tue, 29 September 2015 06:35
Hi,
Please find the attachment with all details

regards,
rags


PLEASE just COPY the content of the file then PASTE it directly into this forum.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Previous Topic: Unable to interpret number of rows accessed during Full Table scan
Next Topic: Explain plan depends on Oracle client?
Goto Forum:
  


Current Time: Mon Mar 18 23:35:35 CDT 2024