OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Tuning (linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Tuning [message #642942]

Wed, 23 September 2015 06:12

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

Report message to a moderator

Re: Tuning [message #642954 is a reply to message #642942]

Wed, 23 September 2015 07:27

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.

Report message to a moderator

tuning [message #643023 is a reply to message #642942]

Fri, 25 September 2015 12:04

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

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

Report message to a moderator

Re: tuning [message #643026 is a reply to message #643024]

Fri, 25 September 2015 13:00

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.

Report message to a moderator

Re: tuning [message #643028 is a reply to message #643026]

Fri, 25 September 2015 23:26

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.

Report message to a moderator

Re: Tuning [message #643031 is a reply to message #642954]

Sat, 26 September 2015 00:06

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

Attachment: Oracle SQL Performance Tuning and Optimization v26 chapter 1.pdf
(Size: 783.27KB, Downloaded 3052 times)
Attachment: scripts.rar
(Size: 21.87KB, Downloaded 2176 times)
Attachment: SQL Tuning Worksheet 3.docx
(Size: 125.21KB, Downloaded 3390 times)
Attachment: Information needed in Tuning a SQL Query.docx
(Size: 24.87KB, Downloaded 3205 times)

Report message to a moderator

Re: tuning [message #643042 is a reply to message #643024]

Sat, 26 September 2015 11:44

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

Report message to a moderator

Re: tuning [message #643043 is a reply to message #643042]

Sat, 26 September 2015 11:58

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?

Report message to a moderator

Re: tuning [message #643108 is a reply to message #643043]

Tue, 29 September 2015 08:35

eml2raghu
Messages: 11
Registered: April 2007
Location: CHENNAI

Junior Member

Hi,
Please find the attachment with all details

regards,
rags

Report message to a moderator

Re: tuning [message #643109 is a reply to message #643108]

Tue, 29 September 2015 08:37

cookiemonster
Messages: 13963
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?

Report message to a moderator

Re: tuning [message #643111 is a reply to message #643108]

Tue, 29 September 2015 08:50

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

Report message to a moderator

Previous Topic:	Unable to interpret number of rows accessed during Full Table scan
Next Topic:	Explain plan depends on Oracle client?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 30 13:21:09 CST 2025