Home » RDBMS Server » Performance Tuning » Tuning (linux)
Tuning [message #642942] |
Wed, 23 September 2015 06:12 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #643031 is a reply to message #642954] |
Sat, 26 September 2015 00:06 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/56289.jpg) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #643109 is a reply to message #643108] |
Tue, 29 September 2015 08:37 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 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?
|
|
|
|
Goto Forum:
Current Time: Sat Aug 31 19:32:19 CDT 2024
|