Home » RDBMS Server » Performance Tuning » Query performing good in 9i but very very slow in 10g
Query performing good in 9i but very very slow in 10g [message #209545] Fri, 15 December 2006 08:31 Go to next message
srinivas4u2
Messages: 66
Registered: June 2005
Member
Hi,

I have a large query that completes in about an hour in 9i but it takes about 6 hours in 10g. I have contacted support, just want to know if anyone of you have faced such an issue before.

Thanks,
Srinivas
Re: Query performing good in 9i but very very slow in 10g [message #209547 is a reply to message #209545] Fri, 15 December 2006 08:40 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
THere are many differences in 10g including how CBO works.
Please post some metrics. Statspack report/trace/plan/query/your statistics collection methods etc.
Regards

Re: Query performing good in 9i but very very slow in 10g [message #209565 is a reply to message #209545] Fri, 15 December 2006 10:10 Go to previous messageGo to next message
srinivas4u2
Messages: 66
Registered: June 2005
Member
Thanks for your response. This is a bug and it's fixed now.

Thanks,
Srinivas
Re: Query performing good in 9i but very very slow in 10g [message #209592 is a reply to message #209565] Fri, 15 December 2006 13:55 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi,

srinivas4u2   > wrote
 This is a bug and it's fixed now. 


If it is bug and solved now , will you please share with all what was the real issue and what is that bug so that other's don't face the same .


Thanks
Re: Query performing good in 9i but very very slow in 10g [message #212050 is a reply to message #209592] Wed, 03 January 2007 11:37 Go to previous messageGo to next message
srinivas4u2
Messages: 66
Registered: June 2005
Member
Hi,

It was bug 3499674. Due to this bug CBO overestimates cost of NL join with index. In our case the workaround was deleting statistics on the concerned table.

Thanks,
Srinivas
Re: Query performing good in 9i but very very slow in 10g [message #212054 is a reply to message #209545] Wed, 03 January 2007 11:49 Go to previous messageGo to next message
srinivas4u2
Messages: 66
Registered: June 2005
Member
I apologize for not having posted more details, I just got back after the vacation.

We were hitting at bug 3499674. In our case deleting the statistics of the concerned table did the trick for us.

Thanks,
Srinivas
Re: Query performing good in 9i but very very slow in 10g [message #213751 is a reply to message #212054] Fri, 12 January 2007 03:35 Go to previous messageGo to next message
ramesh4u
Messages: 1
Registered: January 2007
Junior Member
Hi
we are also facing a similar problem . But i did not understand what u mean by deleting the statistics of the concerned table. Could u please explain
Re: Query performing good in 9i but very very slow in 10g [message #213753 is a reply to message #213751] Fri, 12 January 2007 03:45 Go to previous message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Read Metalink Docid: 3499674.8(Bug 3499674 - CBO may overestimate cost of NL join with index)
for more information i guess
Previous Topic: Log Switches
Next Topic: Insert all vs Forall
Goto Forum:
  


Current Time: Fri Dec 09 03:45:34 CST 2016

Total time taken to generate the page: 0.09989 seconds