How to monitor shared

From: NetComrade <netcomradeNSPAM_at_bookexchange.net>
Date: Thu, 17 Feb 2005 19:01:22 GMT
Message-ID: <4214e71c.1288567512@localhost>

All,

To avoid getting ORA errors, we've always monitored share_pool to have at least 5% free space, with 9i install Oracle seems more aggresive with utilizing all that space, and I keep getting paged for server getting under the threshold, statspack shows there is no point in increasing shared pool (it's 200M):

Library Cache Activity for DB: BASE Instance: BASE Snaps: 71 -72
->"Pct Misses" should be very low

                         Get  Pct        Pin        Pct
Invali-
Namespace           Requests  Miss     Requests     Miss     Reloads

dations
--------------- ------------ ------ -------------- ------ ----------

BODY                   3,623    0.0          3,626    0.2          6
0
CLUSTER                   50    0.0             42    0.0          0
0
INDEX                     12    0.0             12    0.0          0
0
SQL AREA             228,777    0.4      2,204,984    1.3     26,643
74
TABLE/PROCEDURE      259,926    0.0        262,575    0.4        867
0
TRIGGER                1,690    0.0          1,689    0.1          0

^LShared Pool Advisory for DB: BASE Instance: BASE End Snap: 72 -> Note there is often a 1:Many correlation between a single logical object

in the Library Cache, and the physical number of memory objects associated

with it. Therefore comparing the number of Lib Cache objects (e.g. in

v$librarycache), with the number of Lib Cache Memory Objects is invalid

                                                          Estd
Shared Pool    SP       Estd         Estd     Estd Lib LC Time

Size for Size Lib Cache Lib Cache Cache Time Saved Estd Lib Cache
Estim (M) Factr Size (M) Mem Obj Saved (s) Factr Mem Obj Hits
----------- ----- ---------- ------------ ------------ -------

96 .5 96 22,330 74,641 1.0 5,541,419

128 .7 127 31,438 74,697 1.0 5,554,678

160 .8 159 40,639 74,728 1.0 5,562,001

192 1.0 190 47,985 74,731 1.0 5,565,175

224 1.2 221 55,329 74,733 1.0 5,565,645

256 1.3 252 62,597 74,735 1.0 5,565,915

288 1.5 283 69,965 74,736 1.0 5,566,159

320 1.7 314 78,555 74,738 1.0 5,566,456

352 1.8 345 85,013 74,738 1.0 5,566,470

384 2.0 377 90,721 74,738 1.0 5,566,505

system_at_BASE> select * from v$sgastat;

POOL        NAME                                BYTES
----------- ------------------------------ ----------
            fixed_sga                          456356
            buffer_cache                    134217728
            log_buffer                        1180672
shared pool errors                             102600
shared pool enqueue                           1010664
shared pool KGK heap                             1836
shared pool KQR M PO                          3162480
shared pool KQR S PO                           629348
shared pool KQR S SO                             8224
shared pool KQR X PO                             9720
shared pool sessions                          2669680
shared pool sql area                        112362924
shared pool 1M buffer                         2098176
shared pool KGLS heap                         3547492
shared pool processes                          944000
shared pool PX subheap                          19684
shared pool parameters                         325052
shared pool free memory                       7445512
shared pool transaction                       1241708
shared pool PL/SQL DIANA                      1731636
shared pool FileOpenBlock                     1755880
shared pool PL/SQL MPCODE                     4196664
shared pool PL/SQL PPCODE                       65872
shared pool library cache                    58544200
shared pool miscellaneous                    32758456
shared pool PLS non-lib hp                       2068
shared pool joxs heap init                       4220
shared pool sim memory hea                     158428
shared pool table definiti                      26156
shared pool temporary tabl                      18024
shared pool trigger defini                       8244
shared pool trigger inform                        320
shared pool trigger source                        128
shared pool VIRTUAL CIRCUITS                  1722600
shared pool dictionary cache                  1614976
shared pool KSXR receive buffers              1033000
shared pool event statistics per sess        12437880
shared pool fixed allocation callback             388
large pool  free memory                     134217728
java pool   free memory                      16777216

.......
We use Oracle 8.1.7.4 and 9.2.0.5 on Solaris 2.7 boxes remove NSPAM to email Received on Thu Feb 17 2005 - 13:01:22 CST

How to monitor shared_pool?