RelationalDBDesign RelationalDBDesign


Shared Pool   «Prev 

Identifying high-use packages for pinning

Pinned packages output

Shared Pool Objects consisting of 1) OWNER 2) TYPE 3)NAME 4) EXECUTIONS 5) MEM_USED

  1. In this listing, we see those packages that have the highest number of executions and their memory size. In general, you will want to pin those packages that are frequently referenced. Be aware, however, that pinned packages will leave less re-usable memory in the shared pool, and you may want to increase the shared_pool size to accommodate the pinned packages.

Configuring the Reserved Pool

Although Oracle Database breaks down very large requests for memory into smaller chunks, on some systems there might still be a requirement to find a contiguous chunk (for example, over 5 KB) of memory. (The default minimum reserved pool allocation is 4,400 bytes.) If there is not enough free space in the shared pool, then Oracle Database must search for and free enough memory to satisfy this request. This operation could conceivably hold the latch resource for detectable periods of time, causing minor disruption to other concurrent attempts at memory allocation.
Thus, Oracle Database internally reserves a small memory area in the shared pool that the database can use if the shared pool does not have enough space. This reserved pool makes allocation of large chunks more efficient. By default, Oracle Database configures a small reserved pool. The database can use this memory for operations such as PL/SQL and trigger compilation or for temporary space while loading Java objects.
After the memory allocated from the reserved pool is freed, it returns to the reserved pool. You probably will not need to change the default amount of space Oracle Database reserves. However, if necessary, the reserved pool size can be changed by setting the SHARED_POOL_RESERVED_SIZE initialization parameter. This parameter sets aside space in the shared pool for unusually large allocations. For large allocations, Oracle Database attempts to allocate space in the shared pool in the following order:
  1. From the unreserved part of the shared pool.
  2. From the reserved pool. If there is not enough space in the unreserved part of the shared pool, then Oracle Database checks whether the reserved pool has enough space.
  3. From memory. If there is not enough space in the unreserved and reserved parts of the shared pool, then Oracle Database attempts to free enough memory for the allocation. It then retries the unreserved and reserved parts of the shared pool.