Home » RDBMS Server » Performance Tuning » Query taking too much time than usual to execute
Query taking too much time than usual to execute [message #287744] Thu, 13 December 2007 06:13 Go to next message
irremediable
Messages: 38
Registered: December 2007
Member

Hello!
I had a query that used to execute for 2-3 minutes.
It started to take about 40-45 minutes to complete.
The end of the query is GROUP BY.
I can say that tables and indexes seem to be ok,
all indexes of the tables are VALID.
Server RAM is 2 GB and 900 MB is allocated for buffer cache,
200 for shared pool.
Without the GROUP BY clause the query takes 2-3 minutes to finish, but as soon as I add GROUP BY it does work that fast,
thou it used to finish for 2-3 minutes with GROUP BY clause.
What be the possible reasons for sush a degradation in performance?
Thank you.

Re: Query taking too much time than usual to execute [message #287745 is a reply to message #287744] Thu, 13 December 2007 06:15 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

1. Post your query.
2. autotrace output
3. execution plan

please post your query code tags with format.

[Updated on: Thu, 13 December 2007 06:16]

Report message to a moderator

Re: Query taking too much time than usual to execute [message #287767 is a reply to message #287744] Thu, 13 December 2007 07:06 Go to previous messageGo to next message
irremediable
Messages: 38
Registered: December 2007
Member
Here is the query

select DT, 
       CODE, 
       SUM(Saldo)      as Saldo, 
       SUM(SaldoEkv)   as SaldoEkv,
       Sum(Tr_deb)     as Tr_deb, 
       Sum(Tr_DebEkv)  as Tr_DebEkv,
       Sum(Tr_Cr)    as Tr_Cred,
       Sum(Tr_CrEkv) as Tr_CredEkv,
       ID_FINSTR, 
       ID_BALANCE, 
       ID_GROUPBAL,
       ID_CLASBAL, 
       ID_TREST, 
       ID_R013, 
       ID_Term,
       ID_R011,
       ID_R011D,
       Id_Sub_Bal_Etl,
       ID_BRANCH,
       ID_DEP,
       ID_SECTOR_EC,
       id_ved,
       id_pers,
       id_rez
 from 
(
select  
        Acc_Saltr.DT                                  as DT, 
       'SLD'                                          as CODE, 
        ABS(Acc_Saltr.Saldo )                         as Saldo, 
        ABS(Acc_Saltr.SaldoEkvMy )                    as SaldoEkv,
        Acc_Saltr.tr_deb                              as Tr_deb,
        Acc_Saltr.tr_debekv                           as Tr_debEkv,
        Acc_Saltr.tr_cr                               as Tr_cr,
        Acc_Saltr.tr_crEkv                            as Tr_crEkv,
        Acc_Saltr.Id_Finstr                           as ID_FINSTR, 
        ACC_BAL.Id_Balance                            as ID_BALANCE, 
        ACC_BAL.ID_GROUPBAL                           as ID_GROUPBAL, 
        ACC_BAL.ID_CLASBAL                            as ID_CLASBAL, 
        decode(sign(Acc_Saltr.saldo),-1,
                         (select id_trest 
                          from det_trest 
                          where code_trest='А'),1,
                          (select id_trest 
                          from det_trest 
                          where code_trest='П'),-1) as ID_TREST,  
        ACC_BAL.Id_R013                               as ID_R013,       
        nvl((select distinct trm.id_term 
         from con_ass_contr_acc asac,
              det_contracts cn,
              det_term trm
         where asac.id_account=acc_bal.id_account 
         and asac.id_contracts=cn.id_contracts
         and cn.id_source=src.id_source
         and (Date_END-cn.Date_Open) between trm.DAY_FROM
         and trm.Day_To),-1)                              as ID_Term,
        
        ACC_BAL.ID_R011                               as ID_R011,
        ACC_BAL.ID_R011D                              as ID_R011D,
        ACC_BAL.id_subject_dep_etl                    as Id_Sub_Bal_Etl,
        ACC_BAL.ID_BRANCH                             as ID_BRANCH,
        ACC_BAL.id_Sub_bal_etl                        as ID_DEP,
        ACC_BAL.ID_SECTOR_EC                          as ID_SECTOR_EC,
        ACC_BAL.ID_VED                                as ID_VED,    
        ACC_BAL.ID_PERS                               as ID_PERS,
        ACC_BAL.ID_REZ                                as ID_REZ

from (
    
     select to_date('08122007','DDMMYYYY') as dt, 
     b.id_finstr,b.id_account,b.id_subsys,
     b.Saldo as Saldo,a.saldo as SaldoEkvMy,
     decode(to_date('08122007','DDMMYYYY'),b.dt_open,b.tr_deb,0)    as tr_deb,
     decode(to_date('08122007','DDMMYYYY'),a.dt_open,a.tr_debekv,0) as tr_debekv,
     decode(to_date('08122007','DDMMYYYY'),b.dt_open,b.tr_cr,0)     as tr_cr,
     decode(to_date('08122007','DDMMYYYY'),a.dt_open,a.tr_crekv,0)  as tr_crekv
     from fct_acount_saltran a,con_ass_acc asacc,fct_acount_saltran b
     where to_date('08122007','DDMMYYYY')
     between a.dt_open and a.dt_close 
     and to_date('08122007','DDMMYYYY')
     between b.dt_open and b.dt_close
     and b.id_finstr not in (select id_finstr from det_finstr where code_finstr='944')
     and asacc.id_role_assacc in (select id_role_assacc 
                                  from con_role_assacc rac 
                                  where rac.code_role_assacc ='1' )
      and asacc.id_account_child=a.id_account and asacc.id_account_parent=b.id_account
      and to_date('08122007','DDMMYYYY') between asacc.dt_open and asacc.dt_close
      union all     
      select to_date('08122007','DDMMYYYY') as dt, 
      d.id_finstr,d.id_account,d.id_subsys,
      d.saldo as Saldo,d.saldo as SaldoEkvMy,
      decode(to_date('08122007','DDMMYYYY'),d.dt_open,d.tr_deb,0)    as tr_deb,
      decode(to_date('08122007','DDMMYYYY'),d.dt_open,d.tr_debekv,0) as tr_debekv,
     decode(to_date('08122007','DDMMYYYY'),d.dt_open,d.tr_cr,0)      as tr_cr,
     decode(to_date('08122007','DDMMYYYY'),d.dt_open,d.tr_crekv,0)   as tr_crekv
      from fct_acount_saltran d
        where to_date('08122007','DDMMYYYY')
     between d.dt_open and d.dt_close 
     and d.id_finstr in (select id_finstr from det_finstr where code_finstr='944')

    ) Acc_Saltr,    

     (select * from det_source where code_source='GK') src,
(select ACC.*,
         -1                            as ID_GROUPBAL, 
        Bal.Id_R050                                   as ID_R050, 
          -1                           as ID_CLASBAL 
         from 
         (select a.*, b.id_branch,b.id_sector_ec, b.id_ved, b.id_pers,b.id_rez
          from  det_account a,det_subject_etl b 
               where is_cover='0' and a.is_gk='1'
                and a.id_chapter in(select b.id_chapter from det_chapter b 
                where b.code_chapter in ('1','3')) 
                and a.id_subject_cli_etl=b.id_subject_etl)
                Acc,
              det_balance Bal                              
        )ACC_BAL
       where Acc_BAL.Id_Account=Acc_Saltr.Id_Account and acc_bal.id_account<>-1
       and dt=to_date('08122007','DDMMYYYY') 
       )
       group by
       DT, 
       CODE, 
       ID_FINSTR, 
       ID_BALANCE, 
       ID_GROUPBAL, 
       ID_CLASBAL, 
       ID_TREST, 
       ID_R013,       
       ID_Term,
       ID_R011,
       ID_R011D,
       Id_Sub_Bal_Etl,
       ID_Branch,
       ID_DEP,
       id_sector_ec,
       id_ved,
       id_pers,
       id_rez



Execution plan
SELECT STATEMENT 
34  SORT [GROUP BY] 
33  MERGE JOIN [CARTESIAN] 
30  MERGE JOIN [CARTESIAN] 
26  NESTED LOOPS 
23  NESTED LOOPS 
20  NESTED LOOPS 
17  . VIEW 
16  UNION-ALL 
11  FILTER 
8  HASH JOIN 
6  HASH JOIN 
4  HASH JOIN 
1  DWH.FCT_ACOUNT_SALTRAN TABLE ACCESS [FULL] 
3  DWH.CON_ASS_ACC TABLE ACCESS [BY INDEX ROWID] 
2  DWH.IND_ASS_ACC_DOPDCL INDEX [RANGE SCAN] 
5  DWH.CON_ROLE_ASSACC TABLE ACCESS [FULL] 
7  DWH.FCT_ACOUNT_SALTRAN TABLE ACCESS [FULL] 
10  DWH.DET_FINSTR TABLE ACCESS [BY INDEX ROWID] 
9  DWH.AK_FINSTR INDEX [RANGE SCAN] 
15  HASH JOIN 
13  DWH.DET_FINSTR TABLE ACCESS [BY INDEX ROWID] 
12  DWH.AK_FINSTR INDEX [RANGE SCAN] 
14  DWH.FCT_ACOUNT_SALTRAN TABLE ACCESS [FULL] 
19  DWH.DET_ACCOUNT TABLE ACCESS [BY INDEX ROWID] 
18  DWH.PK_DET_ACCOUNT INDEX [UNIQUE SCAN] 
22  DWH.DET_CHAPTER TABLE ACCESS [BY INDEX ROWID] 
21  DWH.PK_DET_CHAPTER INDEX [UNIQUE SCAN] 
25  DWH.DET_SUBJECT_ETL TABLE ACCESS [BY INDEX ROWID] 
24  DWH.PK_DET_SUBJECT_ETL INDEX [UNIQUE SCAN] 
29  BUFFER [SORT] 
28  DWH.DET_SOURCE TABLE ACCESS [BY INDEX ROWID] 
27  DWH.AK_SOURCE INDEX [RANGE SCAN] 
32  BUFFER [SORT] 
31  DWH.PK_DET_BALANCE INDEX [FAST FULL SCAN] 




Couldnt understand 2. Please clarify. Thank you.
Re: Query taking too much time than usual to execute [message #287774 is a reply to message #287767] Thu, 13 December 2007 07:19 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

About autotrace output.
http://asktom.oracle.com/tkyte/article1/autotrace.html
Re: Query taking too much time than usual to execute [message #287785 is a reply to message #287774] Thu, 13 December 2007 08:08 Go to previous messageGo to next message
irremediable
Messages: 38
Registered: December 2007
Member



Than you for docs.
The problem is that I cannt get (I've waited for 65 minutes)
the outup by this query if GROUP BY is unlcuded.
This why I cant get the autotrace output.
Re: Query taking too much time than usual to execute [message #287801 is a reply to message #287744] Thu, 13 December 2007 09:34 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
OS name & version?
Oracle version to 4 decimal places?
Are statistics current for all tables & indexes involved?
Re: Query taking too much time than usual to execute [message #287892 is a reply to message #287801] Fri, 14 December 2007 00:03 Go to previous messageGo to next message
irremediable
Messages: 38
Registered: December 2007
Member



Windows Server 2003.
Oracle Enterprise Edition Release 9.2.0.6.0
Yes, statistics for tables and indexes involved in the query are currenct.
Without the GROUP BY clause the query works pretty good.
But with it it does not.
Please suggest.
Thank you.
Re: Query taking too much time than usual to execute [message #288003 is a reply to message #287892] Fri, 14 December 2007 05:00 Go to previous messageGo to next message
irremediable
Messages: 38
Registered: December 2007
Member



I'm terribly sorry.
I gave incorrect information.
I gathered statistics and it worked fine.
It was the test db that I gathered stats.
Thank you very much.
Re: Query taking too much time than usual to execute [message #289084 is a reply to message #288003] Wed, 19 December 2007 09:00 Go to previous messageGo to next message
irremediable
Messages: 38
Registered: December 2007
Member



How to close a topic?
Re: Query taking too much time than usual to execute [message #289085 is a reply to message #289084] Wed, 19 December 2007 09:11 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Done.

Regards
Michel
Previous Topic: SQL Query Performance
Next Topic: shared pool size tunning
Goto Forum:
  


Current Time: Sat Dec 10 09:19:01 CST 2016

Total time taken to generate the page: 0.23358 seconds