RE: Performance issues after 10gR2 Upgrade.

From: Kerber, Andrew <Andrew.Kerber_at_umb.com>
Date: Tue, 26 Dec 2006 07:19:35 -0600
Message-ID: <D40740337A3B524FA81DB598D2D7EBB305776F86@x6009a.umb.corp.umb.com>

On our upgrade to 10g, we had to set

optimizer_features_enable=8.1.7
and
optimizer_index_cost_adj=30

in order to get acceptable performance.
We use gather_database_stats
Andrew W. Kerber
Oracle DBA
UMB "If at first you dont succeed, dont take up skydiving"

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of MVR Sent: Saturday, December 23, 2006 8:37 AM To: oracle-l
Subject: Performance issues after 10gR2 Upgrade.

Fellas,

here is my situation: We have upgraded 11i database from 9206 and 10.2.0.2. There are various complaints on performance. When I digged into it.. I found some init parameters whicha are supposed to be removed in 10gR2..were not removed.

optimizer_features_enable =9.2.0
optimizer_mode = choose.

We were using "Gather Schema Statistics" concurrent program to gather statistics on application Schemas and dbms_stats on SYS and SYSTEM. How do you guys gather stats ?

Im planning to remove these two parameters from Init file. I have removed this in test instance... its now showing default parameters as

optimizer_features_enable = 10.2.0.2
optimizer_mode = ALL_ROWS

All the functionality is working... but dont know about performance... as load is not there test or no one really uses test instance. I dont know its implications...Can you someone suggest ?

This Optimizer mode change is a very big change..So Im afraid..what I should hear during holidays.

I have created so many SQL_PROFILES so far in reaction to the complaints.. Not sure if those SQL_PROFILES are still valid and useful.. As optimizer is going to be chnaged.. would it be good idea to remove and see?

Thanks
--

http://www.freelists.org/webpage/oracle-l

NOTICE: This electronic mail message and any attached files are confidential. The information is exclusively for the use of the individual or entity intended as the recipient. If you are not the intended recipient, any use, copying, printing, reviewing, retention, disclosure, distribution or forwarding of the message or any attached file is not authorized and is strictly prohibited. If you have received this electronic mail message in error, please advise the sender by reply electronic mail immediately and permanently delete the original transmission, any attachments and any copies of this message from your computer system. Thank you.

--

http://www.freelists.org/webpage/oracle-l Received on Tue Dec 26 2006 - 07:19:35 CST