Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Poor Performance on Oracle9i - 9.2.0.7

Re: Poor Performance on Oracle9i - 9.2.0.7

From: Joel Garry <joel-garry_at_home.com>
Date: 5 May 2006 13:24:57 -0700
Message-ID: <1146860697.295776.65120@j73g2000cwa.googlegroups.com>


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
Received on Fri May 05 2006 - 15:24:57 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US