Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Poor Performance on Oracle9i - 9.2.0.7
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 %
>
> 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 BrinsmeadReceived on Fri May 05 2006 - 15:24:57 CDT