Hi All,
I was in process of finding why streams pool size is consuming memory continuosly, which is taking from shared pool- Buffer cache.
SQL> select pool, sum(bytes)/1024/1024 Mbytes from v$sgastat group by pool;
POOL MBYTES
------------ ----------
831.999115
shared pool 898.869743
streams pool 2369.34533
large pool 16
java pool 16
SQL> select name, bytes/1024/1024 MB from v$sgastat where pool='streams pool' order by 2 desc;
Queue Name MB
------------------------------ ----------
kolcpicon kolcitr 1684.11852
kolccst obj 268.213737
free memory 102.287872
kol vstring 96.0034637
kolcalm coll 57.8486633
kol raw 51.8521118
koicnfadt adt attr 35.4646606
kolpalc 19.9390869
Logminer LCR c 12.3103943
kolcpcon kolcol 11.9338684
kolcpcon datatds 5.74586487
internal procedure lcr 5.15190125
kolcpcon nulltds 5.06633759
kolcpcon indlds 4.81216431
kolcpcon datalds 3.32568359
internal ddl lcr 1.83987427
internal row lcr 1.11590576
external row LCRs 1.11416626
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for HPUX: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> sho parameter streams_pool_size
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
streams_pool_size big integer 304M
SQL> sho parameter sga
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 2560M
sga_target big integer 2000M
select COMPONENT,OPER_TYPE,PARAMETER,INITIAL_SIZE,TARGET_SIZE,FINAL_SIZE,STATUS,START_TIME from v$sga_resize_ops order by START_TIME;
.....
.....
DEFAULT buffer cache SHRINK db_cache_size 872415232 855638016 872415232 ERROR 14-JUN-10
DEFAULT buffer cache SHRINK db_cache_size 872415232 855638016 872415232 ERROR 14-JUN-10
shared pool GROW shared_pool_size 855638016 872415232 855638016 ERROR 14-JUN-10
DEFAULT buffer cache SHRINK db_cache_size 872415232 855638016 872415232 ERROR 14-JUN-10
shared pool GROW shared_pool_size 855638016 872415232 855638016 ERROR 14-JUN-10
shared pool GROW shared_pool_size 855638016 872415232 855638016 ERROR 14-JUN-10
DEFAULT buffer cache SHRINK db_cache_size 872415232 855638016 872415232 ERROR 14-JUN-10
DEFAULT buffer cache SHRINK db_cache_size 872415232 855638016 872415232 ERROR 14-JUN-10
shared pool GROW shared_pool_size 855638016 872415232 855638016 ERROR 14-JUN-10
DEFAULT buffer cache SHRINK db_cache_size 872415232 855638016 872415232 ERROR 14-JUN-10
shared pool GROW shared_pool_size 855638016 872415232 855638016 ERROR 14-JUN-10
DEFAULT buffer cache SHRINK db_cache_size 872415232 855638016 872415232 ERROR 14-JUN-10
shared pool GROW shared_pool_size 855638016 872415232 855638016 ERROR 14-JUN-10
DEFAULT buffer cache SHRINK db_cache_size 872415232 855638016 872415232 ERROR 14-JUN-10
shared pool GROW shared_pool_size 855638016 872415232 855638016 ERROR 14-JUN-10
shared pool GROW shared_pool_size 855638016 872415232 855638016 ERROR 14-JUN-10
DEFAULT buffer cache SHRINK db_cache_size 872415232 855638016 872415232 ERROR 14-JUN-10
shared pool GROW shared_pool_size 855638016 872415232 855638016 ERROR 14-JUN-10
DEFAULT buffer cache SHRINK db_cache_size 872415232 855638016 872415232 ERROR 14-JUN-10
DEFAULT buffer cache SHRINK db_cache_size 872415232 855638016 872415232 ERROR 14-JUN-10
DEFAULT buffer cache SHRINK db_cache_size 872415232 855638016 872415232 ERROR 14-JUN-10
shared pool GROW shared_pool_size 855638016 872415232 855638016 ERROR 14-JUN-10
shared pool GROW shared_pool_size 855638016 872415232 855638016 ERROR 14-JUN-10
shared pool GROW shared_pool_size 855638016 872415232 855638016 ERROR 14-JUN-10
shared pool GROW shared_pool_size 855638016 872415232 872415232 COMPLETE 14-JUN-10
DEFAULT buffer cache SHRINK db_cache_size 872415232 855638016 855638016 COMPLETE 14-JUN-10
DEFAULT buffer cache SHRINK db_cache_size 872415232 855638016 872415232 ERROR 14-JUN-10
What does this Queue Name kolcpicon kolcitr does, why its consuming so much of memory without releasing it?
I tried to search in MOS about kolcpicon kolcitr but no luck.....could anyone please help me in getting details?
-Yasser