Home » RDBMS Server » Performance Tuning » Tuning Suggestion
Tuning Suggestion [message #304182] Tue, 04 March 2008 08:01 Go to next message
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.group_mv_aggrs(grp_id,grp_typ,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
Messages: 51
Registered: February 2008
Location: Dublin, Ireland
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
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


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

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).

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

Current Time: Thu Aug 17 03:07:31 CDT 2017

Total time taken to generate the page: 0.20273 seconds