Home » RDBMS Server » Performance Tuning » URGENT:-Query taking more than 14 hrs.
URGENT:-Query taking more than 14 hrs. [message #65535] Tue, 19 October 2004 04:31 Go to next message
Milind Deshpande
Messages: 93
Registered: May 2004
Member
Hi All,

I have this query which is taking approximately 14 hrs. to execute. I have rewritten the SQL to my knowledge.

Can anybody recommend anything else that how can this query be rewritten in order to respond in minutes instead of hours.

select count(*)
  from TCOMMUNICATION_LOG l
 where (L.COMP_REP_ID_C is null or exists
        (select 'x'
           from TCOMP_REP R, TACCOUNT ACCT, TORGANIZATION ORG
          where R.COMP_REP_ID_C = L.COMP_REP_ID_C and
                R.SVC_C in
                ('000020', '000021', '000056', '000356', '000057', '000048',
                 '000046', '008203', '000148', '008201', '000228', '000223',
                 '000047', '000054', '000049', '008204', '000154', '008202',
                 '000229', '000227', '000053', '000560', '000160', '000161') and
                ACCT.ORG_ID_C = R.ORG_ID_C and
                ACCT.ORG_NME_SEQ_C = R.ORG_NME_SEQ_C and
                ORG.ORG_ID_C = ACCT.ORG_ID_C and
                ORG.ORG_NME_SEQ_C = ACCT.ORG_NME_SEQ_C and
                ACCT.ACCT_TYP_C = 'COMP' and
                ACCT.ACCT_STAT_C NOT IN ('POTENL', 'ACTWSB') AND
                ORG.JURIS_ID_C <> '70001')) and
       ((L.ORG_ID_C is null and L.ORG_NME_SEQ_C is null) or exists
        (select 'x'
           from TACCOUNT ACCT, TORGANIZATION ORG
          where ACCT.ORG_ID_C = L.ORG_ID_C and
                ACCT.ORG_NME_SEQ_C = L.ORG_NME_SEQ_C and
                ORG.ORG_ID_C = ACCT.ORG_ID_C and                     --   exe time.   52263.859          
                ORG.ORG_NME_SEQ_C = ACCT.ORG_NME_SEQ_C and           --   no of rec :- 2312033
                ACCT.ACCT_TYP_C = 'COMP' and                        
                ACCT.ACCT_STAT_C NOT IN ('POTENL', 'ACTWSB') and   
                ORG.JURIS_ID_C <> '70001' and
                (select count(R.COMP_REP_ID_C)
                   from TCOMP_REP R
                  where R.ORG_ID_C = ACCT.ORG_ID_C and
                        R.ORG_NME_SEQ_C = ACCT.ORG_NME_SEQ_C) > 0)) and
       L.JURIS_ID_C <> '70001'

Pls. help me on this.

Thanks in Advance.

Milind.

 
Re: URGENT:-Query taking more than 14 hrs. [message #65536 is a reply to message #65535] Tue, 19 October 2004 07:51 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Get rid of the select count at the end. Replace it with a where exists (select null
Further, this query has to do a Full Table Scan on TCOMMUNICATION_LOG because of the is null clauses (can't be indexed). If this table is big, tough luck.
Did this query ever perform well on the same amount of data (is it at all possible to get a result in 'minutes instead of hours') or did you just write it and hope it would perform...
Re: URGENT:-Query taking more than 14 hrs. [message #65537 is a reply to message #65536] Tue, 19 October 2004 21:30 Go to previous message
Milind Deshpande
Messages: 93
Registered: May 2004
Member
Hi Frank,

Thanks for your Reply.I really hoped it would perform.I will try the suggestions given by you.
I need your help on one more query.I came to knoe that you should rewrite the query instead of outer join with UNION's. Can you help me in rewriting this query with UNION's instead of Outer join or any other suggestions from your side.I am new to SQL statement Tuning and I dont have any idea how to write Optimised SQLs. Pls. help me on this.

select
/*+ LEADING(STM) */
stm.service_team_member_id EmployeeId,
decode((stm.full_name), '', '--',(stm.full_name)) UserName,
stm.status Status,
nvl(st.city, '--') City,
nvl(st.name, '--') Team,
nvl(st.team_name, '--') TeamType,
nvl(ug.user_group, '--') UserGroup
from
av_service_team_member stm,
av_service_team st,
av_service_team_member_keys stmk,
av_internal_user_group ug
where
(stm.full_name) like ('Alfred Esposito Jr') and
stm.service_team_id = st.service_team_id(+) and
stm.service_team_member_id = stmk.service_team_member_id(+) and
stmk.oneworld_id = ug.oneworld_id(+) and
stm.status = 'Active' and
stm.service_team_member_id not in(select employee_id from arv_security_role_grant where sec_role_id = ('6000000087')) order by stm.full_name
Previous Topic: Big performance difference between 8i and 9i
Next Topic: Scenario : SQL Tuning
Goto Forum:
  


Current Time: Fri Mar 29 01:03:11 CDT 2024