Re: Performance issues after 10gR2 Upgrade.

From: Juan Carlos Reyes Pacheco <juancarlosreyesp_at_gmail.com>
Date: Tue, 26 Dec 2006 09:20:11 -0400
Message-ID: <cd4305c10612260520rc8c2ddan21a353e6fa300c21@mail.gmail.com>

Hi bond :) 007
I can't tell you which is the best way, but I can tell you how I wold solve it.
I'll give you an example how I solved three performance complex problems after migration to 10gr2

0) statistics
 remove statistics and recalculate them
This is what I currently use, because I didn't time to find a better way, but I know of some issues can affect bigger database. But for me this is ok now, I still didn't found that issues apply to my small database ( 6GB).
- database statistics as you gather usually
EXEC DBMS_STATS.DELETE_DATABASE_STATS(); EXEC DBMS_STATS.GATHER_database_STATS(
GATHER_SYS=>TRUE,ESTIMATE_PERCENT=>20);
EXEC DBMS_STATS.GATHER_database_STATS(
GATHER_SYS=>TRUE,ESTIMATE_PERCENT=>20,METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY');

• plus *give a check to documentation before running if you don-t know this commands} eXEC DBMS_STATS.gather_dictionary_stats; EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; EXEC DBMS_STATS.GATHER_SYSTEM_STATS ('NOWORKLOAD');
  1. sizing sga components and problem sizing parameters You must know what is sga_target, etc. after running some hours or days the advisor, chekc the advisros for: pga_target, sga_target( if you are using otherwise use cache advisors, etc.),

Obvkously there are parmeter like compatible, etc. set correctly, I'm not going to mention that becaues that is obvoius, if you don't know that then better you go back to documentation and start reading concepts and administration manual.
.
Set the parameter, you must be aware of the __ parameter if you are using spfile, you'll verify this when you can't lower to zero for example the streams_pool_size if you don't use it.

alter system set "__streams_pool_size"=0 scope=both sid='isoa';
alter system set "__shared_pool_size"=0 scope=both sid='isoa';
alter system set "__large_pool_size"=0 scope=both sid='isoa';
alter system set "__java_pool_size"=0 scope=both sid='isoa';

this parameters sets the lower limit of memory

2) Then check if everything works ok. identifiying problem changing hidden parameters.
You must know about hidden paramete here, jhonatan lewsi for example has a list of optimizer related parameter changed between releases. Otherwise don't do it until you understand this.
a)I had a problem with a union all, there was no point to fix using hints, neither other way, then I started to disable/enable one by one the new parameter, until the performance fixed on that query, I found the problem fixed changing the parameter:
alter SYSTEM set "_optimizer_squ_bottomup"=false SCOPE=BOTH; This is obviously a temporary soultion, and the goal is to identify the problem, this is not intended as a permanente solution and unless this is a bug. Do it in your test database please. b) I had a problem with performance solved increasing alter system set "_optimizer_max_permutations"=2500 scope=both; I set so previously based on a Don Burleson advice and this works good. c) Unknown problem on a super copmlex view, calling another views. I solved it, analizing performance I started to create some indexes, then was necessary because load grow ( you could run sql access advisor) The problem simply dissapear.

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

Received on Tue Dec 26 2006 - 07:20:11 CST