Re: Performance issues after 10gR2 Upgrade.
- From: "Juan Carlos Reyes Pacheco" <juancarlosreyesp@xxxxxxxxx>
- To: yoursraju007@xxxxxxxxx
- Date: Tue, 26 Dec 2006 09:20:11 -0400
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.
Other related posts:
