11g

For example, this is what I see after setting the number of shared pool subpools to 2 in my test database (and running some hard parsing workload):
SQL> select child#, gets
  2  from v$latch_children
  3  where name = 'shared pool'
  4  order by child#;


    CHILD#       GETS
---------- ----------
         1   27538623
         2   17924565
         3        131
         4        131
         5        131
         6        131
         7        131

7 rows selected.

Note that this article doesn’t aim to explain all the basics of ORA-4031 troubleshooting, I’ll talk about the subpool utilization imbalance problem only. If you haven’t read MetaLink Note 396940.1 - “Troubleshooting and Diagnosing ORA-4031 Error” yet, I recommend to do this first and then read my comments here.

As you know, ORA-4031 errors look like this:

ORA-04031: "unable to allocate n bytes of shared memory ("shared pool", "object_name", "alloc type(2,0)" ...)

“n” shows how many bytes we tried to allocate when ended up with the failure. Italic strings can show various different values but essentially they’re just some metadata describing for what did we try to allocate that memory.

Note the two bold pieces. The “shared pool” means that we tried to make the allocation from shared pool (if you have problems with other pools you can see there “large pool”, “streams pool”, “java pool” as well).

The “2″ in “(2,0)” means that the failure happened in shared pool sub pool number 2 and the “0″ shows sub-sub-pool number 0.

Sometimes the error happens just due heavily undersized shared pool (combined bad cursor management or some incorrect parameter values). In such cases you would see the shared pool free memory drop to near-zero in V$SGASTAT.

However, sometimes you can have ORA-4031’s even when you see plenty of free space available in V$SGASTAT. What’s the issue with that?

This case happens mainly for two reasons:

1) Shared pool free memory fragmentation

There is no big enough free chunk available even after flushing out unpinned chunks from LRU list. In other words, you have a lot of small free chunks scattered around in different places in shared pool but there is no single big enough chunk available for acommodating our allocation. I will talk about troubleshooting this problem in a separate post.

2) Unbalanced memory usage / free memory in different shared pool subpools

This is what I’m explaining in this post.

So, how to monitor which subpool has how much free memory available?

V$SGASTAT unfortunately just shows a sum of all subpools:

However, when we look into the source code of GV$SGASTAT we see this (output edited for readability):

SQL> @v gv$sgastat

VIEW_NAME  TEXT
---------- --------------------------------------------------------------------------------
GV$SGASTAT select inst_id,'',ksmssnam,ksmsslen from x$ksmfs where ksmsslen>1  union all

           select inst_id,'shared pool',ksmssnam, sum(ksmsslen)
           from x$ksmss
           where ksmsslen>1
           group by inst_id, 'shared pool', ksmssnam

           union all  select
           inst_id,'large pool',ksmssnam, sum(ksmsslen) from x$ksmls    where ksmsslen>1
           group by inst_id, 'large pool', ksmssnam  union all  select inst_id,'java
           pool',ksmssnam, sum(ksmsslen) from x$ksmjs    where ksmsslen>1 group by inst_id,
           'java pool', ksmssnam  union all  select inst_id,'streams pool',ksmssnam,
           sum(ksmsslen) from x$ksmstrs    where ksmsslen>1 group by inst_id, 'streams
           pool', ksmssnam

SQL> desc x$ksmss

SQL> @pd kghdsidx
NAME                                          VALUE                          DESCRIPTION
--------------------------------------------- ------------------------------ -------------------
_kghdsidx_count                               2                              max kghdsidx count

The script takes one parameter, what memory allocation reasons to report (% would report all):

I will start with “total” which just reports me the shared pool totals and doesn’t break down by allocation reason.

SQL> @sgastatx total

-- All allocations:

SUBPOOL                             BYTES         MB
------------------------------ ---------- ----------
shared pool (0 - Unused):       192937984        184
shared pool (1):                 62919000         60
shared pool (2):                 67109232         64
shared pool (Total):            322966216        308

-- Allocations matching "total":

no rows selected

By the way I can confirm these numbers by querying v$sgainfo:

SQL> select from v$sgainfo;

NAME                                  BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size                      2075656 No
Redo Buffers                        6311936 No
Buffer Cache Size                 184549376 Yes
Shared Pool Size                  322961408 Yes
Large Pool Size                     4194304 Yes
Java Pool Size                      4194304 Yes
Streams Pool Size                         0 Yes
Granule Size                        4194304 No
Maximum SGA Size                  734003200 No
Startup overhead in Shared Pool    88080384 No
Free SGA Memory Available         209715200

11 rows selected.

SQL> select current_size from v$sga_dynamic_components where component = 'shared pool';

CURRENT_SIZE
------------
   322961408

SQL> @sgastatx "free memory"

-- All allocations:

SUBPOOL                             BYTES         MB
------------------------------ ---------- ----------
shared pool (0 - Unused):       180355072        172
shared pool (1):                 67113304         64
shared pool (2):                 75497840         72
shared pool (Total):            322966216        308


-- Allocations matching "free memory":

SUBPOOL                     NAME          SUM(BYTES)         MB
--------------------------- ------------ -------------- ----------
shared pool (0 - Unused):   free memory   180355072        172

shared pool (1):            free memory    12689424       12.1  <-- 12 MB of 64 allocated to this subheap is free


shared pool (2):            free memory    14822176      14.14

And when there’s not enough free memory in some subpool then you can run sgastatx with % parameter to report all memory users in that subpool. I removed some lines from output for brevity:SQL> @sgastatx %

-- All allocations:
 
SUBPOOL                             BYTES         MB
------------------------------ ---------- ----------
shared pool (0 - Unused):       171966464        164
shared pool (1):                 71307608         68
shared pool (2):                 79692144         76
shared pool (Total):            322966216        308
 
-- Allocations matching "%":
 
SUBPOOL                        NAME                       SUM(BYTES)         MB
------------------------------ -------------------------- ---------- ----------
shared pool (0 - Unused):      free memory                 171966464        164
 
shared pool (1):               free memory                  12045488      11.49
                               ASH buffers                   6291456          6
                               sql area                      6133120       5.85
                               private strands               4359168       4.16
                               library cache                 4122344       3.93
                               KSFD SGA I/O b                3977088       3.79
                               event statistics per sess     2764800       2.64
                               KTI-UNDO                      2323992       2.22
                               KGLS heap                     2154696       2.05
                               kglsim hash table bkts        2097152          2
                              PL/SQL MPCODE                 1884456        1.8
                               sessions                      1588808       1.52
 
[...snip...]
 
shared pool (2):               free memory                  14726904      14.04
                               row cache                     7495336       7.15
                               ASH buffers                   6029312       5.75
                               sql area                      4405040        4.2
                               private strands               4359168       4.16
                               KCB Table Scan Buffer         3981120        3.8
                               FileOpenBlock                 3977984       3.79
                               event statistics per sess     2764800       2.64
                               KTI-UNDO                      2323992       2.22
                               kglsim hash table bkts        2097152          2
                               library cache                 2059376       1.96
                               KQR M PO                      1603584       1.53
                               sessions                      1593600       1.52
                               VIRTUAL CIRCUITS              1401600       1.34
                               KGLS heap                     1377192       1.31
                               PL/SQL MPCODE                 1163344       1.11
                               CCursor                       1134520       1.08
                               dbwriter coalesce buffer      1052672          1
                               KSXR receive buffers          1036000        .99
[...snip...]
 
731 rows selected.
 

Of course sometimes you’d want to know how the memory usage breakdown changes over time, for that you’d need to write a little collector script which dumps the data into some table and visualize it later on, like I have done for regular V$SGASTAT data with my PerfSheet tool