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.
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 ...