OraFAQ Forum: Performance Tuning » Performance Issues After Migration

Home » RDBMS Server » Performance Tuning » Performance Issues After Migration

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

Performance Issues After Migration [message #341695]

Wed, 20 August 2008 00:26

Asma123
Messages: 19
Registered: November 2007

Junior Member

Hi

We have just migrated our database from 9i to 10g using "Database 10g Upgrade Assistant". After migration few of our queries and procedures were taking too much time. We changed the value of parameters

optimizer_index_caching = 50 &
optimizer_index_cost_adj = 50

The performance of most of them was enhanced, but a few list of values (LOVs) have started taking too much time to get populated, with these values. These LOVs were working fine, with the default values of these parameters. What should be done, so that all of these activities are optimized.

Regards

Asma

Report message to a moderator

Re: Performance Issues After Migration [message #341697 is a reply to message #341695]

Wed, 20 August 2008 00:34

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

alter system set _silver_bullet=true;

Report message to a moderator

Re: Performance Issues After Migration [message #341756 is a reply to message #341697]

Wed, 20 August 2008 03:49

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

with SCOPE=SPFILE...

@asma,
Read this metalink note: Note:262592.1

https://metalink.oracle.com/metalink/plsql/f?p=130:14:1640858331909611675::::p14_database_id,p14_docid,p14_show_header,p14_show_help, p14_black_frame,p14_font:NOT,262592.1,1,1,1,helvetica

[Updated on: Wed, 20 August 2008 03:53]

Report message to a moderator

Re: Performance Issues After Migration [message #341777 is a reply to message #341695]

Wed, 20 August 2008 05:04

alanm
Messages: 284
Registered: March 2005

Senior Member

hi,
I see you have had some useful answers already.

Have you gathered stats recently since your upgrade?

regards

Alan

Report message to a moderator

Re: Performance Issues After Migration [message #341797 is a reply to message #341777]

Wed, 20 August 2008 06:10

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

I see you have had some useful answers already.

And you add one more.

Regards
Michel

Report message to a moderator

Re: Performance Issues After Migration [message #341895 is a reply to message #341777]

Wed, 20 August 2008 13:23

Asma123
Messages: 19
Registered: November 2007

Junior Member

I have gathered the stats, but the list is still behaving like that. I have re-set the value of optimizer_index_cost_adj to 100 again. When optimizer_index_cashing parameter is set to 0, the list is populated immediately, but it has adverse effects on other queries. By setting optimizer_index_cashing = 50, many of the other performance issues were solved, but the list is taking 8 minutes to get populated.

The query behind this list consists of three tables, joined together..

Any other options please??????

Asma

Report message to a moderator

Re: Performance Issues After Migration [message #341897 is a reply to message #341695]

Wed, 20 August 2008 13:27

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.orafaq.com/forum/t/84315/74940/
which of the suggestions in URL above have you tried & what were the results?

Why do you expect assistance?
We don't have your tables.
We don't have you data.
We do NOT even have any actual SQL.
All we have is you whining about poor performance.

http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

Report message to a moderator

Previous Topic:	solaris 10
Next Topic:	Sql stmt tuning

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 22:09:35 CST 2024