Home » RDBMS Server » Performance Tuning » Query Takes Lots Of time to execute.
icon9.gif  Query Takes Lots Of time to execute. [message #158573] Mon, 13 February 2006 04:47 Go to next message
loveoracle
Messages: 41
Registered: February 2006
Location: Mumbai
Member


Dear All,

I have one query, that takes 7-8 minutes to execute.
The query based on 4 tables.
The table IBS_WORK_BANKDATA contains 120000 records,
IBS_ORG_BANKDATA contains 110000 records,
IBS_CURRENCYMASTER contains 178 records,
IBS_CURRENCYEXCHANGEMASTER contains 58 records.

Query:
select distinct trim(wrk.bd_alcd) as ALCD, wrk.bd_typecd as TypeCD, wrk.bd_forcd as FORCD, wrk.bd_curcd as CURCD,
       wrk.bd_councd as COUNCD, wrk.bd_sectcd as SECCD,
       wrk.bd_matcd as MATCD, wrk.bd_c_u_cd as C_U_CD, wrk.bd_s_u_cd as S_U_CD,
       0 as Org_FCBal,0 as ORG_Bal,case when wrk.bd_type='O' then wrk.bd_fc_bal else 0 end as Main_FCBal,
       case when wrk.bd_type='O' then (wrk.bd_fc_bal * nvl(exchg.cer_exchangerate, 1)) else 0 end as main_Bal,
       wrk.bd_rs_int,wrk.bd_rs_bal,wrk.bd_fc_int,wrk.bd_fc_bal, 
       ' '  as TrackChangs
 from ibs_work_bankdata wrk inner join ibs_org_bankdata org ON org.bd_yrqtr = wrk.bd_yrqtr and org.bd_bkcode=wrk.bd_bkcode and org.bd_forcd = wrk.bd_forcd 
      and wrk.BD_YRQTR=20044 and wrk.BD_BKCODE ='000'
      and wrk.BD_ALCD = '51' and wrk.BD_FORCD ='IN' and wrk.BD_TYPECD = '11'
 left join ibs_currencymaster curmst on curmst.cur_code = wrk.bd_curcd
 left join ibs_currencyexchangerate exchg on exchg.cer_currencyid = curmst.cur_id
      and exchg.cer_yearqtr = 20051 and exchg.CER_ACTIVE=1  union select distinct trim(wrk.bd_alcd) as ALCD, wrk.bd_typecd as TypeCD, wrk.bd_forcd as FORCD, wrk.bd_curcd as CURCD,
       wrk.bd_councd as COUNCD, wrk.bd_sectcd as SECCD,
       wrk.bd_matcd as MATCD, ' ' as C_U_CD, ' ' as S_U_CD,
       0 as Org_FCBal,0 as ORG_Bal,case when wrk.bd_type='O' then wrk.bd_fc_bal else 0 end as Main_FCBal,
       case when wrk.bd_type='O' then (wrk.bd_fc_bal * nvl(exchg.cer_exchangerate, 1)) else 0 end as main_Bal,
       wrk.bd_rs_int,wrk.bd_rs_bal,wrk.bd_fc_int,wrk.bd_fc_bal, 
      ' '  as TrackChangs
 from ibs_work_bankdata wrk inner join ibs_org_bankdata org ON org.bd_yrqtr = wrk.bd_yrqtr and org.bd_bkcode=wrk.bd_bkcode and org.bd_forcd = wrk.bd_forcd 
      and wrk.BD_YRQTR=20044 and wrk.BD_BKCODE ='000'
      and wrk.BD_ALCD = '51' and wrk.BD_FORCD ='IN' and wrk.BD_TYPECD = '11' and wrk.bd_rs_bal>0 
 left join ibs_currencymaster curmst on curmst.cur_code = wrk.bd_curcd 
 left join ibs_currencyexchangerate exchg on exchg.cer_currencyid = curmst.cur_id
      and exchg.cer_yearqtr = 20051 and exchg.CER_ACTIVE=1  order by main_FCBal

Explain_plan:
SELECT STATEMENT, GOAL = CHOOSE			Cost=429	Cardinality=2	Bytes=314
 SORT UNIQUE			Cost=402	Cardinality=2	Bytes=314
  UNION-ALL					
   TABLE ACCESS BY INDEX ROWID	Object owner=RBI	Object name=IBS_ORG_BANKDATA	Cost=54	Cardinality=204	Bytes=2856
    NESTED LOOPS			Cost=174	Cardinality=41	Bytes=6437
     NESTED LOOPS OUTER			Cost=120	Cardinality=1	Bytes=143
      NESTED LOOPS OUTER			Cost=119	Cardinality=1	Bytes=93
       TABLE ACCESS BY INDEX ROWID	Object owner=RBI	Object name=IBS_WORK_BANKDATA	Cost=118	Cardinality=1	Bytes=52
        INDEX SKIP SCAN	Object owner=RBI	Object name=IBS_WORK_BANKDATA_IDX	Cost=59	Cardinality=1	
       TABLE ACCESS BY INDEX ROWID	Object owner=RBI	Object name=IBS_CURRENCYMASTER	Cost=1	Cardinality=178	Bytes=7298
        INDEX RANGE SCAN	Object owner=RBI	Object name=IBS_CURRENCYMASTER_CODE		Cardinality=178	
      TABLE ACCESS BY INDEX ROWID	Object owner=RBI	Object name=IBS_CURRENCYEXCHANGERATE	Cost=1	Cardinality=19	Bytes=950
       INDEX RANGE SCAN	Object owner=RBI	Object name=IBS_CURRENCYEXCH_CURRENCYID		Cardinality=19	
     INDEX RANGE SCAN	Object owner=RBI	Object name=IBS_ORG_BANKDATA_IDX	Cost=19	Cardinality=204	
   TABLE ACCESS BY INDEX ROWID	Object owner=RBI	Object name=IBS_ORG_BANKDATA	Cost=54	Cardinality=204	Bytes=2856
    NESTED LOOPS			Cost=174	Cardinality=41	Bytes=6437
     NESTED LOOPS OUTER			Cost=120	Cardinality=1	Bytes=143
      NESTED LOOPS OUTER			Cost=119	Cardinality=1	Bytes=93
       TABLE ACCESS BY INDEX ROWID	Object owner=RBI	Object name=IBS_WORK_BANKDATA	Cost=118	Cardinality=1	Bytes=52
        INDEX SKIP SCAN	Object owner=RBI	Object name=IBS_WORK_BANKDATA_IDX	Cost=59	Cardinality=1	
       TABLE ACCESS BY INDEX ROWID	Object owner=RBI	Object name=IBS_CURRENCYMASTER	Cost=1	Cardinality=178	Bytes=7298
        INDEX RANGE SCAN	Object owner=RBI	Object name=IBS_CURRENCYMASTER_CODE		Cardinality=178	
      TABLE ACCESS BY INDEX ROWID	Object owner=RBI	Object name=IBS_CURRENCYEXCHANGERATE	Cost=1	Cardinality=19	Bytes=950
       INDEX RANGE SCAN	Object owner=RBI	Object name=IBS_CURRENCYEXCH_CURRENCYID		Cardinality=19	
     INDEX RANGE SCAN	Object owner=RBI	Object name=IBS_ORG_BANKDATA_IDX	Cost=19	Cardinality=204	


Please give me the solution.
thanks in advance,
Prathamesh.
Re: Query Takes Lots Of time to execute. [message #158577 is a reply to message #158573] Mon, 13 February 2006 05:10 Go to previous messageGo to next message
princess
Messages: 4
Registered: January 2006
Location: Alexandria
Junior Member


hai
to increase yr query perfommance u have to build a materialized view on it,so every time u execute the query the optimizer will access the materialized view table and gets the data from it since once u create materialized view optimzer access the four table once and makes the nessceray join instead accessing the four tables &hash joins between every time u issue the query
so here is
the action plan
----------------
-set QUERY_REWRITE_ENABLED to TRUE.
-CREATE MATERIALIZED VIEW anyname
ENABLE QUERY REWRITE
AS
select distinct trim(wrk.bd_alcd) as ALCD, wrk.bd_typecd as TypeCD, wrk.bd_forcd as FORCD, wrk.bd_curcd as CURCD,
wrk.bd_councd as COUNCD, wrk.bd_sectcd as SECCD,
wrk.bd_matcd as MATCD, wrk.bd_c_u_cd as C_U_CD, wrk.bd_s_u_cd as S_U_CD,
0 as Org_FCBal,0 as ORG_Bal,case when wrk.bd_type='O' then wrk.bd_fc_bal else 0 end as Main_FCBal,
case when wrk.bd_type='O' then (wrk.bd_fc_bal * nvl(exchg.cer_exchangerate, 1)) else 0 end as main_Bal,
wrk.bd_rs_int,wrk.bd_rs_bal,wrk.bd_fc_int,wrk.bd_fc_bal,
' ' as TrackChangs
from ibs_work_bankdata wrk inner join ibs_org_bankdata org ON org.bd_yrqtr = wrk.bd_yrqtr and org.bd_bkcode=wrk.bd_bkcode and org.bd_forcd = wrk.bd_forcd
and wrk.BD_YRQTR=20044 and wrk.BD_BKCODE ='000'
and wrk.BD_ALCD = '51' and wrk.BD_FORCD ='IN' and wrk.BD_TYPECD = '11'
left join ibs_currencymaster curmst on curmst.cur_code = wrk.bd_curcd
left join ibs_currencyexchangerate exchg on exchg.cer_currencyid = curmst.cur_id
and exchg.cer_yearqtr = 20051 and exchg.CER_ACTIVE=1 union select distinct trim(wrk.bd_alcd) as ALCD, wrk.bd_typecd as TypeCD, wrk.bd_forcd as FORCD, wrk.bd_curcd as CURCD,
wrk.bd_councd as COUNCD, wrk.bd_sectcd as SECCD,
wrk.bd_matcd as MATCD, ' ' as C_U_CD, ' ' as S_U_CD,
0 as Org_FCBal,0 as ORG_Bal,case when wrk.bd_type='O' then wrk.bd_fc_bal else 0 end as Main_FCBal,
case when wrk.bd_type='O' then (wrk.bd_fc_bal * nvl(exchg.cer_exchangerate, 1)) else 0 end as main_Bal,
wrk.bd_rs_int,wrk.bd_rs_bal,wrk.bd_fc_int,wrk.bd_fc_bal,
' ' as TrackChangs
from ibs_work_bankdata wrk inner join ibs_org_bankdata org ON org.bd_yrqtr = wrk.bd_yrqtr and org.bd_bkcode=wrk.bd_bkcode and org.bd_forcd = wrk.bd_forcd
and wrk.BD_YRQTR=20044 and wrk.BD_BKCODE ='000'
and wrk.BD_ALCD = '51' and wrk.BD_FORCD ='IN' and wrk.BD_TYPECD = '11' and wrk.bd_rs_bal>0
left join ibs_currencymaster curmst on curmst.cur_code = wrk.bd_curcd
left join ibs_currencyexchangerate exchg on exchg.cer_currencyid = curmst.cur_id
and exchg.cer_yearqtr = 20051 and exchg.CER_ACTIVE=1 order by main_FCBal;

regards

Re: Query Takes Lots Of time to execute. [message #158582 is a reply to message #158573] Mon, 13 February 2006 05:54 Go to previous messageGo to next message
loveoracle
Messages: 41
Registered: February 2006
Location: Mumbai
Member

Dear Princess,

Thanks for your useful feedback.

Regards,
Prathsmesh.
Re: Query Takes Lots Of time to execute. [message #158682 is a reply to message #158577] Mon, 13 February 2006 20:54 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
princess wrote on Mon, 13 February 2006 22:10


hai
to increase yr query perfommance u have to build a materialized view on it


Yes, it's official: the art of tuning is dead.

Or, you could have a go at tuning the SQL.

IF you get it tuned to its absolute fastest,
AND it's still too slow,
AND it's a mission critical query
AND it's a query that is run many times a day
THEN you may consider using an MV.

To tune the SQL, we need to know how many rows are returned. I see there are a number of filter conditions on the largest table, but none of them look like they will limit the result set down to a tiny number.

The current plan would be fairly good if the SQL returned fewer than 100 rows. Any more than that, and I would want to see some HASH joins.

Some suggestions:
1. Try gathering statistics on all tables
2. Why are the joins to the smaller tables RANGE SCANs? Are you joining on a non-unique column? Can one row in ibs_work_bankdata match multiple rows in the other tables? If it should be unique, then the keys on the other tables should have unique indexes.
3. The SKIP SCANs may not be ideal. What are the columns indexed in IBS_WORK_BANKDATA_IDX?
4. The two parts of the UNION look pretty similar. The second one contains and wrk.bd_rs_bal>0. Is there any way you can eliminate the UNION? Perhaps with an OR?

_____________
Ross Leishman
Previous Topic: Delete Rows
Next Topic: parallel hint
Goto Forum:
  


Current Time: Thu Mar 28 08:17:05 CDT 2024