Home » RDBMS Server » Performance Tuning » Tuning Suggestion
Tuning Suggestion [message #304182] Tue, 04 March 2008 08:01 Go to next message
GokulTendulkar
Messages: 1
Registered: March 2008
Junior Member
Hi ,
We have a query which ran in 8 mins in oracle 9i,
But takes 1 hr in 10g.

SELECT ( acc.acct_group_dim_key
||'|'|| to_char(sdate.cycle_date,'DD-MON-YYYY')
||'|'||dmart_acct_group_pkg.get_acct_nav(acc.grp_id,acc.grp_typ)
||'|'||dmart_acct_group_pkg.group_mv_aggrs(grp_id,grp_typ,base_curcy_cd) ||'|'||
dmart_acct_group_pkg.get_acct_exposure(acc.grp_id,acc.grp_typ,sdate.cycle_date,acc.base_curcy_cd))
FROM acct_group_dim acc,
(select cycle_dt cycle_date from system_date) sdate
WHERE NVL(close_dt, sdate.cycle_date+1) >sdate.cycle_date
AND acc.exp_dt=to_date('12/31/9999','mm/dd/yyyy')
AND acc.grp_typ IN ('S','A','G')

I have checked the functions called, they are fine.
Can anyone help me with the hints r query rewrite?
Re: Tuning Suggestion [message #304235 is a reply to message #304182] Tue, 04 March 2008 10:47 Go to previous messageGo to next message
moshea
Messages: 51
Registered: February 2008
Location: Dublin, Ireland
Member
Have you tried comparing the explain plans under 9i versus 10g?
Re: Tuning Suggestion [message #304300 is a reply to message #304182] Tue, 04 March 2008 16:34 Go to previous messageGo to next message
alanm
Messages: 282
Registered: March 2005
Senior Member

Are statistics up to date in the 10g database.

Are you comparing like with like.

What paltform are you running your databases on?

are you spfile settings the same for SGA, PGA etc etc

regards

Alan
Re: Tuning Suggestion [message #304719 is a reply to message #304182] Thu, 06 March 2008 06:37 Go to previous message
hd999
Messages: 2
Registered: March 2008
Location: Austria
Junior Member
Hi,

check for histograms on the table, which probably came new in 10g and lead to a slower execution plan. If so, get rid of them, rerun the query and see what happens (flush the shared pool, or slightly modify the query before you retry).

-H
Previous Topic: Performance Issue
Next Topic: Reclaiming free space questions
Goto Forum:
  


Current Time: Fri Dec 09 17:14:58 CST 2016

Total time taken to generate the page: 0.17800 seconds