As Oracle allocates shared pool memory for shared server session memory, the See Also: Oracle Database Data Warehousing Guide for more
information on sizing the large pool with parallel query
See Also: Oracle Database Backup and Recovery Reference for more information on sizing the large pool when using Recovery Manager
Configuring and Using the Shared Pool and Large Pool
decreases. If you allocate this session memory from a different pool, then Oracle can use the shared pool primarily for caching shared SQL and not incur the performance overhead from shrinking the shared SQL cache.
Oracle recommends using the large pool to allocate the shared server-related User Global Area (UGA), rather that using the shared pool. This is because Oracle uses the shared pool to allocate System Global Area (SGA) memory for other purposes, such as shared SQL and PL/SQL procedures. Using the large pool instead of the shared pool decreases fragmentation of the shared pool.
To store shared server-related UGA in the large pool, specify a value for the
initialization parameter LARGE_POOL_SIZE. To see which pool (shared pool or large pool) the memory for an object resides in, check the column POOL in V$SGASTAT. The large pool is not configured by default; its minimum value is 300K. If you do not configure the large pool, then Oracle uses the shared pool for shared server user session memory.
Configure the size of the large pool based on the number of simultaneously active sessions. Each application requires a different amount of memory for session information, and your configuration of the large pool or SGA should reflect the memory requirement. For example, assuming that the shared server requires 200K to 300K to store session information for each active session. If you anticipate 100 active sessions simultaneously, then configure the large pool to be 30M, or increase the shared pool accordingly if the large pool is not configured.
Determining an Effective Setting for Shared Server UGA Storage The exact amount of UGA Oracle uses depends on each application. To determine an effective setting for the large or shared pools, observe UGA use for a typical user and multiply this amount by the estimated number of user sessions.
Even though use of shared memory increases with shared servers, the total amount of memory use decreases. This is because there are fewer processes; therefore, Oracle uses less PGA memory with shared servers when compared to dedicated server environments.
Note: If a shared server architecture is used, then Oracle allocates some fixed amount of memory (about 10K) for each configured session from the shared pool, even if you have configured the large pool. The CIRCUITS initialization parameter specifies the
maximum number of concurrent shared server connections that the database allows.
See Also:
■ Oracle Database Concepts for more information about the large pool
■ Oracle Database Reference for complete information about initialization parameters
Note: For best performance with sorts using shared servers, set SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE to the same value. This keeps the sort result in the large pool instead of having it written to disk.
Configuring and Using the Shared Pool and Large Pool
Checking System Statistics in the V$SESSTAT View Oracle collects statistics on total memory used by a session and stores them in the dynamic performance view V$SESSTAT. Table 7–3 lists these statistics.
To find the value, query V$STATNAME. If you are using a shared server, you can use the following query to decide how much larger to make the shared pool. Issue the following queries while your application is running:
SELECT SUM(VALUE) || ' BYTES' "TOTAL MEMORY FOR ALL SESSIONS"
FROM V$SESSTAT, V$STATNAME WHERE NAME = 'session uga memory'
AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC#;
SELECT SUM(VALUE) || ' BYTES' "TOTAL MAX MEM FOR ALL SESSIONS"
FROM V$SESSTAT, V$STATNAME
WHERE NAME = 'session uga memory max'
AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC#;
These queries also select from the dynamic performance view V$STATNAME to obtain internal identifiers for session memory and max session memory. The results of these queries could look like the following:
TOTAL MEMORY FOR ALL SESSIONS ---157125 BYTES
TOTAL MAX MEM FOR ALL SESSIONS ---417381 BYTES
The result of the first query indicates that the memory currently allocated to all sessions is 157,125 bytes. This value is the total memory with a location that depends on how the sessions are connected to Oracle. If the sessions are connected to dedicated server processes, then this memory is part of the memories of the user processes. If the sessions are connected to shared server processes, then this memory is part of the shared pool.
The result of the second query indicates that the sum of the maximum size of the memory for all sessions is 417,381 bytes. The second result is greater than the first because some sessions have deallocated memory since allocating their maximum amounts.
If you use a shared server architecture, you can use the result of either of these queries to determine how much larger to make the shared pool. The first value is likely to be a better estimate than the second unless nearly all sessions are likely to reach their maximum allocations at the same time.
Limiting Memory Use for Each User Session by Setting PRIVATE_SGA You can set the PRIVATE_SGA resource limit to restrict the memory used by each client session from the SGA. PRIVATE_SGA defines the number of bytes of memory used from the SGA Table 7–3 V$SESSTAT Statistics Reflecting Memory
Statistic Description
session UGA memory The value of this statistic is the amount of memory in bytes allocated to the session.
Session UGA memory max The value of this statistic is the maximum amount of memory in bytes ever allocated to the session.
Configuring and Using the Shared Pool and Large Pool
by a session. However, this parameter is used rarely, because most DBAs do not limit SGA consumption on a user-by-user basis.
Reducing Memory Use with Three-Tier Connections If you have a high number of connected users, then you can reduce memory usage by implementing three-tier connections.
This by-product of using a transaction process (TP) monitor is feasible only with pure transactional models, because locks and uncommitted DMLs cannot be held between calls. A shared server environment offers the following advantages:
■ It is much less restrictive of the application design than a TP monitor.
■ It dramatically reduces operating system process count and context switches by enabling users to share a pool of servers.
■ It substantially reduces overall memory usage, even though more SGA is used in shared server mode.