Pool Utilization ...

[vr76413]

Hello all

I allocated 110M, 125M and 116M respectively to Java_pool_size, Large_pool_size and Shared_pool_size...

How do we know whether what is the utilization of space we allocated to these parameters ....are tehse parameters utilizing all the space or only 20% or what ?

Is there any sql command or something like that from which we will know ?
Thanks
Ron

[pando]

v$sgastat

but it seems to me you are allocating space for pools which you dont need them, obviously I am assumming but do you use many java stored procedures and MTS? (Considering MTS here only since RMAN/PQ/process slaves dont use that much space in large pool)

[vr76413]

How do we read this v$SGASTAT View ?....This is what it looked like for me...Is there any documents i can be refered to ?

******************************************************************
SQL> select * from v$sgastat;

POOL NAME BYTES
----------- -------------------------- ----------
fixed_sga 730200
buffer_cache 33554432
log_buffer 787456
shared pool errors 39320
shared pool subheap 86232
shared pool KGK heap 7000
shared pool KQR L PO 947648
shared pool KQR M PO 733536
shared pool KQR S SO 6144
shared pool sessions 459680
shared pool sql area 94572488

POOL NAME BYTES
----------- -------------------------- ----------
shared pool 1M buffer 2098176
shared pool KGLS heap 922792
shared pool free memory 13998168
shared pool PL/SQL DIANA 680920
shared pool FileOpenBlock 1191104
shared pool PL/SQL MPCODE 242448
shared pool library cache 12453936
shared pool miscellaneous 8449376
shared pool MTTR advisory 16936
shared pool PLS non-lib hp 2088
shared pool joxs heap init 4240

POOL NAME BYTES
----------- -------------------------- ----------
shared pool kgl simulator 4950944
shared pool sim memory hea 136424
shared pool trigger defini 712
shared pool trigger inform 1880
shared pool trigger source 848
shared pool Checkpoint queue 513280
shared pool VIRTUAL CIRCUITS 349120
shared pool dictionary cache 3221760
shared pool KSXR receive buffers 1034000
shared pool FileIdentificatonBlock 349824
shared pool message pool freequeue 940944

POOL NAME BYTES
----------- -------------------------- ----------
shared pool KSXR pending messages que 853952
shared pool event statistics per sess 1728560
shared pool fixed allocation callback 464
large pool free memory 16777216
java pool free memory 117440512

38 rows selected.
*****************************************************************

Thanks
Ron