Home » RDBMS Server » Performance Tuning » to tune an SQL query (oracle 9i)
to tune an SQL query [message #438307] Fri, 08 January 2010 00:13 Go to next message
mohannksr
Messages: 28
Registered: January 2009
Junior Member
Hi,

this is my query


select count(*) from
(SELECT
*
FROM TABLE_P PIC,
TABLE_bs STG
LEFT OUTER JOIN
(SELECT
T.T_NAME AS T_NAME,
CY.CY_NAME AS CY,
EX.EX_NAME AS EX_NAME,
TH.TH_NAME AS CI_NAME,
TH.M_CODE AS T_CODE,
SC.SC_NAME AS SC_NAME,
SM.SM_NR AS SCM_NO,
CEL.EQ_NR AS EQ_NO,
T.T_NR AS T_NO
FROM
TABLE_EX EX,TABLE_TH TH,TABLE_SC SC,
TABLE_CY CY,TABLE_T T,TABLE_SM SM,
TABLE_EL CEL
WHERE RTRIM(TH.EX_NR)=RTRIM(EX.EX_NR)
AND RTRIM(TH.T_NR)=RTRIM(T.T_NR)
AND RTRIM(TH.CY_NR)=RTRIM(CY.CY_NR)
AND RTRIM(SC.TH_NR)=RTRIM(TH.TH_NR)
AND RTRIM(CY.T_NR)=RTRIM(T.T_NR)
AND RTRIM(SM.T_NR)=RTRIM(T.T_NR)
AND RTRIM(SM.TH_NR)=RTRIM(TH.TH_NR)
AND RTRIM(SM.EX_NR)=RTRIM(EX.EX_NR)
AND RTRIM(SM.SC_NR)=RTRIM(SC.SC_NR)
AND RTRIM(SM.CY_NR)=RTRIM(CY.CY_NR)
AND RTRIM(CEL.SM_NR) =RTRIM(SM.SM_NR)
AND SM.RD='N'
) TB
ON RTRIM(TB.T_NAME)=RTRIM(STG.T_NAME)
AND RTRIM(TB.EX_NAME) = RTRIM(STG.EX_NAME)
AND RTRIM(TB.CI_NAME) = RTRIM(STG.CI_NAME)
AND RTRIM(TB.T_CODE)=RTRIM(STG.MCC)
AND RTRIM(TB.SC_NAME) = RTRIM(STG.SC_NAME)
AND RTRIM(TB.CY) = RTRIM(STG.CY)
WHERE RTRIM(PIC.M_P)=RTRIM(STG.M_N)
AND UPPER(RTRIM(STG.DS))='NEW'
AND TB.T_NAME IS NOT NULL);

The query is taking 21 sec.
After removing the TABLE_P table the performance has to be increased but actually it decreased.the query is taking 2 minutes.




select count(*) from
(SELECT
*
FROM --TABLE_P PIC,
TABLE_bs STG
LEFT OUTER JOIN
(SELECT
T.T_NAME AS T_NAME,
CY.CY_NAME AS CY,
EX.EX_NAME AS EX_NAME,
TH.TH_NAME AS CI_NAME,
TH.M_CODE AS T_CODE,
SC.SC_NAME AS SC_NAME,
SM.SM_NR AS SCM_NO,
CEL.EQ_NR AS EQ_NO,
T.T_NR AS T_NO
FROM
TABLE_EX EX,TABLE_TH TH,TABLE_SC SC,
TABLE_CY CY,TABLE_T T,TABLE_SM SM,
TABLE_EL CEL
WHERE RTRIM(TH.EX_NR)=RTRIM(EX.EX_NR)
AND RTRIM(TH.T_NR)=RTRIM(T.T_NR)
AND RTRIM(TH.CY_NR)=RTRIM(CY.CY_NR)
AND RTRIM(SC.TH_NR)=RTRIM(TH.TH_NR)
AND RTRIM(CY.T_NR)=RTRIM(T.T_NR)
AND RTRIM(SM.T_NR)=RTRIM(T.T_NR)
AND RTRIM(SM.TH_NR)=RTRIM(TH.TH_NR)
AND RTRIM(SM.EX_NR)=RTRIM(EX.EX_NR)
AND RTRIM(SM.SC_NR)=RTRIM(SC.SC_NR)
AND RTRIM(SM.CY_NR)=RTRIM(CY.CY_NR)
AND RTRIM(CEL.SM_NR) =RTRIM(SM.SM_NR)
AND SM.RD='N'
) TB
ON RTRIM(TB.T_NAME)=RTRIM(STG.T_NAME)
AND RTRIM(TB.EX_NAME) = RTRIM(STG.EX_NAME)
AND RTRIM(TB.CI_NAME) = RTRIM(STG.CI_NAME)
AND RTRIM(TB.T_CODE)=RTRIM(STG.MCC)
AND RTRIM(TB.SC_NAME) = RTRIM(STG.SC_NAME)
AND RTRIM(TB.CY) = RTRIM(STG.CY)
WHERE /*RTRIM(PIC.M_P)=RTRIM(STG.M_N)
AND */
UPPER(RTRIM(STG.DS))='NEW'
AND TB.T_NAME IS NOT NULL);

i have attached the execution plan for both the queries..

could any one help me to know the reason behing this..?
  • Attachment: BOOKING.LOG
    (Size: 12.86KB, Downloaded 138 times)
Re: to tune an SQL query [message #438311 is a reply to message #438307] Fri, 08 January 2010 00:59 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
mohannksr wrote on Fri, 08 January 2010 00:13

....
....

WHERE RTRIM(TH.EX_NR)=RTRIM(EX.EX_NR)
AND RTRIM(TH.T_NR)=RTRIM(T.T_NR)
AND RTRIM(TH.CY_NR)=RTRIM(CY.CY_NR)
AND RTRIM(SC.TH_NR)=RTRIM(TH.TH_NR)
AND RTRIM(CY.T_NR)=RTRIM(T.T_NR)
AND RTRIM(SM.T_NR)=RTRIM(T.T_NR)
AND RTRIM(SM.TH_NR)=RTRIM(TH.TH_NR)
AND RTRIM(SM.EX_NR)=RTRIM(EX.EX_NR)
AND RTRIM(SM.SC_NR)=RTRIM(SC.SC_NR)
AND RTRIM(SM.CY_NR)=RTRIM(CY.CY_NR)
AND RTRIM(CEL.SM_NR) =RTRIM(SM.SM_NR)
AND SM.RD='N'
....
....

WHERE RTRIM(TH.EX_NR)=RTRIM(EX.EX_NR)
AND RTRIM(TH.T_NR)=RTRIM(T.T_NR)
AND RTRIM(TH.CY_NR)=RTRIM(CY.CY_NR)
AND RTRIM(SC.TH_NR)=RTRIM(TH.TH_NR)
AND RTRIM(CY.T_NR)=RTRIM(T.T_NR)
AND RTRIM(SM.T_NR)=RTRIM(T.T_NR)
AND RTRIM(SM.TH_NR)=RTRIM(TH.TH_NR)
AND RTRIM(SM.EX_NR)=RTRIM(EX.EX_NR)
AND RTRIM(SM.SC_NR)=RTRIM(SC.SC_NR)
AND RTRIM(SM.CY_NR)=RTRIM(CY.CY_NR)
AND RTRIM(CEL.SM_NR) =RTRIM(SM.SM_NR)
AND SM.RD='N'
) TB
ON RTRIM(TB.T_NAME)=RTRIM(STG.T_NAME)
AND RTRIM(TB.EX_NAME) = RTRIM(STG.EX_NAME)
AND RTRIM(TB.CI_NAME) = RTRIM(STG.CI_NAME)
AND RTRIM(TB.T_CODE)=RTRIM(STG.MCC)
AND RTRIM(TB.SC_NAME) = RTRIM(STG.SC_NAME)
AND RTRIM(TB.CY) = RTRIM(STG.CY)
WHERE /*RTRIM(PIC.M_P)=RTRIM(STG.M_N)
AND */
UPPER(RTRIM(STG.DS))='NEW'
AND TB.T_NAME IS NOT NULL);



To Start with ( First Step ).

why to use so many RTRIM function ? Remove ALL , Remove UPPER from the query !!! you might want to save data in STG.DS in UPPER case. amd while inserting data in Table please trim the incoming data so you do NOT need to use the RTRIM function, this will save more process time.

-Rahul


Re: to tune an SQL query [message #438313 is a reply to message #438311] Fri, 08 January 2010 01:09 Go to previous messageGo to next message
mohannksr
Messages: 28
Registered: January 2009
Junior Member
hi rahul,

thanks for your suggestion...

but the question is why the performance decreased after
removing the table TABLE_P from the query..?

could any one help me by reviewing the execution plan..?
Re: to tune an SQL query [message #438319 is a reply to message #438313] Fri, 08 January 2010 01:36 Go to previous message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
mohannksr wrote on Fri, 08 January 2010 01:09

....
....
could any one help me by reviewing the execution plan..?


Post execution plan of this query.( not attached one )

from Attached execution Plan

Quote:

TABLE ACCESS (FULL) OF 'TABLE_P' (Cost=68 Card=18715 Bytes=56145)


its full table access means index is not being used.


-Rahul

[Updated on: Fri, 08 January 2010 01:39]

Report message to a moderator

Previous Topic: Re-structuring the SQL query
Next Topic: SQL Tuning- Need help
Goto Forum:
  


Current Time: Sun Dec 04 08:26:44 CST 2016

Total time taken to generate the page: 0.24200 seconds