Home » RDBMS Server » Performance Tuning » Query Takes Lots Of time to execute.
 Query Takes Lots Of time to execute. [message #158573] |
Mon, 13 February 2006 04:47  |
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  |
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 #158682 is a reply to message #158577] |
Mon, 13 February 2006 20:54 |
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
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 14:25:14 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
