Hi All,
Oracle v 11.2.0.2.
The database I have, has 50GB SGA. I was trying to find out if that is being used properly or not. Shared pool is occupying almost 14GB, so I am trying to find out what is eating up those 14GB.
Please see the following queries.
I can see that about 4GB is teken up by SQLs. How can I find out about remaining 9GB? Also, let me know if I am doing something totally wrong :-)
SQL> select name, bytes/(1024 * 1024) size_mb, resizeable from v$sgainfo ;
NAME SIZE_MB RES
-------------------------------- ---------------- ---
Fixed SGA Size 2.135 No
Redo Buffers 153.281 No
Buffer Cache Size 36,864.000 Yes
Shared Pool Size 13,696.000 Yes
Large Pool Size 128.000 Yes
Java Pool Size 128.000 Yes
Streams Pool Size .000 Yes
Shared IO Pool Size .000 Yes
Granule Size 128.000 No
Maximum SGA Size 50,971.418 No
Startup overhead in Shared Pool 896.000 No
Free SGA Memory Available .000
12 rows selected.
SQL> select * from V$LIBRARY_CACHE_MEMORY ;
LC_NAMESPACE LC_INUSE_MEMORY_OBJECTS LC_INUSE_MEMORY_SIZE LC_FREEABLE_MEMORY_OBJECTS LC_FREEABLE_MEMORY_SIZE
--------------- ----------------------- -------------------- -------------------------- -----------------------
BODY 0 0 64 5
CLUSTER 0 0 0 0
INDEX 0 0 0 0
JAVA DATA 0 0 0 0
JAVA RESOURCE 0 0 0 0
JAVA SOURCE 0 0 0 0
OBJECT 0 0 0 0
OTHER/SYSTEM 0 0 0 0
PIPE 0 0 0 0
SQL AREA 70608 543 197839 3775
TABLE/PROCEDURE 0 0 1409 7
TRIGGER 0 0 0 0
12 rows selected.
We have a third party software, which I doubt, fires lot of SQLs with hardcoded literals (and not bind veriables). But those SQLs account for the 4GBs in SQL area. How can I find out about rest of 9GB?
Thanks in advance.