| Tuning Suggestion [message #304182] |
Tue, 04 March 2008 08:01  |
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 #304719 is a reply to message #304182] |
Thu, 06 March 2008 06:37  |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
