2.337 SHARED_POOL_SIZE
SHARED_POOL_SIZE
specifies (in bytes) the size of the shared pool.
Property | Description |
---|---|
Parameter type |
Big integer |
Syntax |
|
Default value |
If If If For considerations when dealing with database instances using Oracle ASM, see "SHARED_POOL_SIZE and Automatic Storage Management". |
Modifiable |
|
Modifiable in a PDB |
Yes |
Range of values |
Minimum: the granule size Maximum: operating system-dependent |
Basic |
No |
The shared pool contains shared cursors, stored procedures, control structures, and other structures. If SGA_TARGET
is not set, then Oracle also allocates parallel execution message buffers from the shared pool. Larger values improve performance in multiuser systems. Smaller values use less memory.
You can monitor utilization of the shared pool by querying the view V$SGASTAT
.
Note:
This parameter is optional for pluggable databases (PDBs). When this parameter is set for a PDB, it indicates a possible minimum value for the PDB usage of the memory pool.
To be able to use Resource Manager in a CDB to control the amount of memory each PDB can use:
-
The
NONCDB_COMPATIBLE
initialization parameter must be set toFALSE
at the CDB level (in the root of the CDB). -
The
MEMORY_TARGET
initialization parameter must not be set at the CDB level. -
If the
SGA_TARGET
initialization parameter is set at the CDB level, then the following requirement must be met:-
The value of
SHARED_POOL_SIZE
set in a PDB must be less than or equal to 50% of theSGA_TARGET
value at the CDB level.
-
-
If the
SGA_TARGET
initialization parameter is set at the PDB level, then the following requirement must be met:-
The value of
SHARED_POOL_SIZE
set in a PDB must be less than or equal to 50% of theSGA_TARGET
value at the PDB level.
-
-
If the
SGA_TARGET
initialization parameter is not set, but theSHARED_POOL_SIZE
initialization parameter is set at the CDB level, then the following requirement must be met:-
The value of
SHARED_POOL_SIZE
set in a PDB must be less than or equal to 50% of theSHARED_POOL_SIZE
value at the CDB level.
-
When you set SHARED_POOL_SIZE
in a PDB to a value that does not meet these requirements, you receive an error.
See Also:
-
Oracle Multitenant Administrator's Guide for more information about the initialization parameters that control the memory usage of PDBs
-
Oracle Database Performance Tuning Guide for more information on setting this parameter
SHARED_POOL_SIZE and Automatic Storage Management
On a database instance using Oracle Automatic Storage Management (Oracle ASM), additional memory is required to store extent maps. As a general guideline, you can aggregate the values from the following queries to obtain current database storage size that is either already on Oracle ASM or will be stored in Oracle ASM. Then determine the redundancy type that is used (or will be used), and calculate the value for SHARED_POOL_SIZE
, using the aggregated value as input.
SELECT SUM(BYTES)/(1024*1024*1024) FROM V$DATAFILE; SELECT SUM(BYTES)/(1024*1024*1024) FROM V$LOGFILE a, V$LOG b WHERE a.group#=b.group#; SELECT SUM(BYTES)/(1024*1024*1024) FROM V$TEMPFILE WHERE status='ONLINE';
Additionally, keep the following guidelines in mind:
-
For disk groups using external redundancy:
(Every 100G of space needs 1M of extra shared pool) + 2M
-
For disk groups using normal redundancy:
(Every 50G of space needs 1M of extra shared pool) + 4M
-
For disk groups using high redundancy:
(Every 33G of space needs 1M of extra shared pool) + 6M