Oracle Internals: An Introduction

The Shared Pool

The part of the SGA that is most commonly oversized is the shared pool. Many DBAs have little understanding of what the shared pool is used for, and how to determine whether it is correctly sized. So they just make it “BIG!” Sometimes that is not big enough, but more often it is wasteful and can also impair performance.

Chunks

To understand the shared pool better, you need to do little more than take a careful look at X$KSMSP . Each row in this table represents a chunk of shared pool memory. Example 6.2 shows some sample rows.

Example 6-2. Sample Chunks in the Shared Pool

SQL> select ksmchcom, ksmchcls, ksmchsiz from x$ksmsp;
KSMCHCOM         KSMCHCLS   KSMCHSIZ
---------------- -------- ----------
KGL handles      recr            496
PL/SQL MPCODE    recr           1624
dictionary cach  freeabl        4256
free memory      free           1088
library cache    freeabl         568
library cache    recr            584
multiblock rea   freeabl        2072
permanent memor  perm        1677104
row cache lru    recr             48
session param v  freeabl        2936
sql area         freeabl        2104
sql area         recr           1208
...

When each shared pool chunk is allocated, the code passes a comment to the function that is called to perform the allocation. These comments are visible in the KSMCHCOM column of X$KSMSP, and describe the purpose for which the memory has been allocated.

Each chunk is a little larger than the object it contains because there is a 16-byte header to identify the type, class, and size of the chunk and to contain linked-list pointers used for shared pool management.

There are four main classes of ...