Oracle 9i Performance on SPARCE 450 Solaris 5.7

From: danths <d_sameer_at_yahoo.com>
Date: 4 Nov 2002 16:29:39 -0800
Message-ID: <78e940e1.0211041629.5890223f@posting.google.com>

Hi All,
After having upgraded from 8i to 9i I have been running into a lot of perf issues. For one, the cpu ( 1 processor / 1G mem) has been spiking up a lot.
Here are few details. In 8i I always has buffer_cache_hit_ration > 90% , but not anymore. Nothing significant has changed including disk layout. I am already working with Oracle support for the past one week and they haven't been able to suggest anything . I have 1G mem and Oracle is already consuming 1/2 of it and so I cannot increase any SGA and hence db_buffer_cache. I also ran statspack and nothing major had shown up. There's been no swapping either. How concerned should I be regarding the buffer ratio. This is for an OLTP application. Pls suggest.

SQL> show sga

Total System Global Area 537691892 bytes

Fixed Size                   280308 bytes
Variable Size             419430400 bytes
Database Buffers          117440512 bytes
Redo Buffers                 540672 bytes




~Database Version            : Oracle9i Enterprise Edition Release

9.0.1.4.0


~Database Up Since           : 02:27:01 AM, October   31 2002

!Buffer Cache Hit Ratio      : 58.2289

~Library Cache Miss Ratio : 0.1588
~Dictionary Cache Miss Ratio : 0.0524

[Shared Pool Usage] Exec Time 0 seconds
~ Total Mb Unused : 30.75
~ Total Mb Used : 273.25
~ Total Mb : 304
~ Shared Pool Percent Used : 89.88

[Archive Log Mode Info] Exec Time 0 seconds ! Archiver : STOPPED
! Log Mode : NOARCHIVELOG

[Archive Log Info] Exec Time 0 seconds
~ Average Log Switches Per Day : 3.77
~ Hard Drive Storage (in Mb) for this many archive logs : 377.05

[Rollback Segments with wait ratios > 1 %] Exec Time 1 seconds
~ None
Received on Mon Nov 04 2002 - 18:29:39 CST