Shared pool free space
                                                 Last update (2011-05-07 15:53:42)
                                                                                                                   Date added (2008-04-15 14:21:00)

Summary
Here is some useful information about Shared Pool size and free space. First of all you can find the parameters related to shared pool with the query:
SELECT NAME, VALUE/(1024*1024) SHARED_POOL_SIZE_MB 
FROM V$PARAMETER WHERE NAME LIKE '%shared_pool%';

NAME                                                    SHARED_POOL_SIZE_MB
---------------------------------------------------- -------------------
shared_pool_size                                          11488
shared_pool_reserved_size                                 574.4
You can find the free space in Shared pool with the query:
SELECT POOL,NAME, ROUND(BYTES/(1024*1024),2) FREE_MB 
FROM V$SGASTAT WHERE POOL='shared pool'
AND NAME='free memory'
ORDER BY BYTES DESC;

POOL         NAME                          FREE_MB
------------ -------------------------- ----------
shared pool  free memory                   8130.13

After oracle 10g the shared pool is divided in subpools. To find how many subpools your instance has you must follow the Metalink note 455179.1 because it depends on cpu number and init parameters.
How To Determine The Default Number Of Subpools Allocated During Startup [ID 455179.1]

I think the maximum number of subpools that you can have is 7.

Each subpool is protected by a separate shared pool latch and each subpool has its own freelists and LRU list. To monitor how much space each subpool is using execute the query:
SELECT KSMCHIDX "SubPool", ROUND(SUM(KSMCHSIZ)/1024/1024) USED_MBYTES
FROM SYS.X$KSMSP 
GROUP BY KSMCHIDX 
ORDER BY KSMCHIDX ASC;

   SubPool  USED_MBYTES
---------- ----------
         1        944
         2       1424
         3       1216
         4        864

if you want to change the number of subpools from 7 to 4 for example then use the following hidden parameter:_kghdsidx_count = 4

Tip: Actually the subpools are not only as many as the oracle algorithm creates or what ever value the parameter _kghdsidx_count has. There is always one more! The subpool with KSMCHIDX=0. You can check this out with
select distinct ksmdsidx from x$ksmss order by ksmdsidx asc;

  KSMDSIDX
----------
         0
         1
         2
         3
         4
This is why when you startup the database not all memory allocated for shared pool is immediately given to subpool heaps. Some memory stays free allocated to subpool 0 and will be used to feature increases for the other pools. This mechanism allows some subpools to grab more memory than the others. So it is no strange if you monitor subpool imbalances!

From what i know a subpool heap cannot give memory back to some other subpool! I assume that is due to the fact that each subpool is protected by a separate shared pool latch and each subpool has its own freelists and LRU list.

using this query you can monitor all the subpools and 0 subpool free space
SELECT TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MI:SS') DT, KSMSSNAM NAME, 
       KSMDSIDX SUBPOOL, ROUND(KSMSSLEN/1024/1024) MBYTES 
	   FROM X$KSMSS
WHERE KSMSSLEN > (10*1024*1024) OR KSMSSNAM='free memory' --more than 10MBytes
ORDER BY NAME, SUBPOOL;

DT                  NAME                          SUBPOOL     MBYTES
------------------- -------------------------- ---------- ----------
04-05-2011 13:36:43 ASH buffers                         1         68
04-05-2011 13:36:43 CCursor                             1         11
04-05-2011 13:36:43 Checkpoint queue                    1        195
04-05-2011 13:36:43 FileIdentificatonBlock              1         11
04-05-2011 13:36:43 FileOpenBlock                       1         15
04-05-2011 13:36:43 KQR M PO                            1         12
04-05-2011 13:36:43 KTI-UNDO                            1         16
04-05-2011 13:36:43 db_block_hash_buckets               1         45
04-05-2011 13:36:43 event statistics per sess           1         20
04-05-2011 13:36:43 free memory                         0      24112
04-05-2011 13:36:43 free memory                         1         67
04-05-2011 13:36:43 library cache                       1         20
04-05-2011 13:36:43 private strands                     1         31
04-05-2011 13:36:43 sessions                            1         12
04-05-2011 13:36:43 sql area                            1         49

To eliminate imbalances of the subpools you can set the _kghdsidx_count = 1. Then you will only have the 0 subpool and one subpool. If you set _kghdsidx_count = 1 then it is obvious that is working "like an Oracle9i" style.

A very common error related to shared pool misconfiguration or problems is:
ORA-04031: unable to allocate x bytes of shared memory

To monitor if you have ORA-04031 errors you can use the query:
SELECT KGHLUSHRPOOL "SUBPOOL", KGHLURCR "PINS AND|RELEASES",
KGHLUTRN, KGHLUFSH, KGHLUOPS, KGHLUNFU "ORA-4031|ERRORS",
KGHLUNFS "LAST ERROR|SIZE" 
FROM SYS.X$KGHLU
WHERE INST_ID = USERENV('Instance') ;

                                                     PINS AND
   SUBPOOL PINS AND|RELEASES   KGHLUTRN   KGHLUFSH   RELEASES ORA-4031|ERRORS LAST ERROR|SIZE
---------- ----------------- ---------- ---------- ---------- --------------- ---------------
         0                 0        559          0       4057               0               0
         1            460039    1147916          0    4562952               0               0
If you want to see what Oracle advises for Shared pool size check this out:
SELECT SHARED_POOL_SIZE_FOR_ESTIMATE || ' MB' SHARED_POOL_SIZE_MB, 
SHARED_POOL_SIZE_FACTOR*100 || '%' RESIZE_SHARED_POOL_PERC, ESTD_LC_SIZE, 
ESTD_LC_MEMORY_OBJECTS, ESTD_LC_TIME_SAVED, ESTD_LC_TIME_SAVED_FACTOR, ESTD_LC_MEMORY_OBJECT_HITS 
FROM V$SHARED_POOL_ADVICE;
To check how much space is free at the shared_pool_reserved use this query:
SELECT ROUND(FREE_SPACE/(1024*1014), 2) SHARED_POOL_RESERVED_FREE_MB, 
ROUND(USED_SPACE/(1024*1024),2) SHARED_POOL_RESERVED_USED_MB 
FROM V$SHARED_POOL_RESERVED;
Other links
Shared pool loaded objects
Large pool size and contents
Reviews
Filters
Search