OraFAQ Forum: Performance Tuning » Query performing good in 9i but very very slow in 10g

Home » RDBMS Server » Performance Tuning » Query performing good in 9i but very very slow in 10g

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query performing good in 9i but very very slow in 10g [message #209545]

Fri, 15 December 2006 08:31

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

Report message to a moderator

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

Mahesh Rajendran
Messages: 10707
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

Report message to a moderator

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

srinivas4u2
Messages: 66
Registered: June 2005

Member

Thanks for your response. This is a bug and it's fixed now.

Thanks,
Srinivas

Report message to a moderator

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

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

Report message to a moderator

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

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

Report message to a moderator

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

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

Report message to a moderator

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

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

Report message to a moderator

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

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

Report message to a moderator

Previous Topic:	Log Switches
Next Topic:	Insert all vs Forall

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 16 06:03:37 CDT 2024