Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!newscon02.news.prodigy.com!newscon06.news.prodigy.com!prodigy.net!border1.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!j73g2000cwa.googlegroups.com!not-for-mail
From: "Joel Garry" <joel-garry@home.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: Poor Performance on Oracle9i - 9.2.0.7
Date: 5 May 2006 13:24:57 -0700
Organization: http://groups.google.com
Lines: 65
Message-ID: <1146860697.295776.65120@j73g2000cwa.googlegroups.com>
References: <1146841778.288445.187360@v46g2000cwv.googlegroups.com>
NNTP-Posting-Host: 67.116.125.178
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1146860706 7130 127.0.0.1 (5 May 2006 20:25:06 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 5 May 2006 20:25:06 +0000 (UTC)
In-Reply-To: <1146841778.288445.187360@v46g2000cwv.googlegroups.com>
User-Agent: G2/0.2
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0),gzip(gfe),gzip(gfe)
X-HTTP-Via: 1.0 ISA2K4-OC1
Complaints-To: groups-abuse@google.com
Injection-Info: j73g2000cwa.googlegroups.com; posting-host=67.116.125.178;
   posting-account=YRNZ5wwAAAAg-yYjMFwy3FyWUbPiwNdq
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:266979

eavanzi wrote:
> Recently, we have migrated from 8.1.7.4 to 9.2.0.7 and now the
> performance is terrible!!
> Please, help us!!
> The database server is Sun E6500 (24 SPARC processors and 24GB RAM mem)
> and Solaris 2.6.
> The database server is connected to IBM Shark Storage 2105 F20 using
> two gigabit fiber channels. The size database is 400GB.
> We have 350 Oracle Applications active users.
> We have the Oracle Applications 11.5.9 running in another server - Sun
> Fire V880 (4 processors SPARC and 8GB RAM mem) and Solaris 8.
> The statspack is showing data contention in latches, specially, cache
> buffers chains and library cache, as follows:
>
> CACHE BUFFERS CHAINS
>    Gets = 561344520
>    Sleeps = 77469
>    Percentage = 48,99%
>    Cumulative = 48,99%

http://www.jlcomp.demon.co.uk/faq/buffer_chains.html
http://www.freelists.org/archives/oracle-l/03-2004/msg02734.html
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1229436447262

Now that you understand that, go look at the performance manual to
figure out what tables/indices to put in separate buffer pools (search
on V$BH in the docs).

> LIBRARY CACHE
>    Gets = 55715493
>    Sleeps = 55456
>    Percentage = 35,07%
>    Cumulative = 84,06 %

http://groups.google.com/groups?as_q=&num=10&scoring=r&hl=en&as_epq=&as_oq=&as_eq=&as_ugroup=&as_usubject=intermittent+library+cache+pin%2Flock+problems&as_uauthors=&lr=&as_drrb=q&as_qdr=&as_mind=1&as_minm=1&as_miny=1981&as_maxd=5&as_maxm=5&as_maxy=2006&safe=off
may have food for thought.

>
> Please, checking if the init.ora parameters are correct.
>
> db_name = PROD
> control_files =
> /prod11i/app/proddata/control01.ctl,/db1prod/oradata/PROD/control02.ctl,
> /db1prod/oradata/PROD/control03.ctl
> db_block_size = 8192
> compatible = 9.2.0
> _system_trig_enabled = TRUE

Expanding on Sybrand's comment, why _do_ you have underscore parameters
set?

Histograms:
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:34807121420113

jg
--
@home.com is bogus.  "Please note:  I am not a lawyer.  And I am not
your lawyer.  Heck, I have not even actually read the standard OLSA
license in several months, and I may or may not be accurately
remembering the details or correctly interpreting the language.  Even
in the highly unlikey event that I *am*, I probably have not seen the
actual contract that applies to you.  Do not base any decisions on
anything I have said here or anywhere else.  Go hire a qualified
lawyer.  Do it NOW!!!!  Save yourself!  Run!  ;-)" - Mark Brinsmead