Home » RDBMS Server » Performance Tuning » Performance Issues After Migration
Performance Issues After Migration [message #341695] Wed, 20 August 2008 00:26 Go to next message
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
Re: Performance Issues After Migration [message #341697 is a reply to message #341695] Wed, 20 August 2008 00:34 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
alter system set _silver_bullet=true;
Re: Performance Issues After Migration [message #341756 is a reply to message #341697] Wed, 20 August 2008 03:49 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Smile 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 Go to previous messageGo to next message
alanm
Messages: 282
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
Re: Performance Issues After Migration [message #341797 is a reply to message #341777] Wed, 20 August 2008 06:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I see you have had some useful answers already.

And you add one more.

Regards
Michel
Re: Performance Issues After Migration [message #341895 is a reply to message #341777] Wed, 20 August 2008 13:23 Go to previous messageGo to next message
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



Re: Performance Issues After Migration [message #341897 is a reply to message #341695] Wed, 20 August 2008 13:27 Go to previous message
BlackSwan
Messages: 25040
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
Previous Topic: solaris 10
Next Topic: Sql stmt tuning
Goto Forum:
  


Current Time: Tue Dec 06 08:30:47 CST 2016

Total time taken to generate the page: 0.13043 seconds