Hi All,
I am on Oracle 12.1 , enterprise edition.
The problem is, too much memory from my SGA is being given to shared pool. SGA target is set to 19GB. If I look at v$sgastat this is what I can see
SQL > select * from
2 (select pool, name, round(bytes/(1024 * 1024)) size_mb from v$sgastat order by bytes desc)
3 where rownum < 15 ;
POOL NAME SIZE_MB
------------ -------------------------- ----------
buffer_cache 6336
shared pool SQLA 3963
shared pool free memory 2454
shared pool KGLH0 1911
shared pool PRTMV 1175
shared pool KQR L PO 643
shared_io_pool 512
streams pool free memory 319
shared pool KGLHD 297
shared pool kglsim object batch 208
java pool free memory 163
large pool free memory 141
large pool PX msg pool 117
shared pool kglsim heap 111
Question - The free memory in shared pool , does the shared pool keep that free memory for ever ? or does it give it back to be used for other purpose ?
I want more memory for buffer cache. But my shared pool is taking more memory. I do have some application connecting to the database and running 10s of thousands of SQLs which don't use bind variable. So , I can see why the shared pool has grown..... but is that space taken up permanantly? How can I reduce the size of shared pool ?
Any ideas ??