Oracle教程

Oracle 参数 SHARED_POOL_SIZE 官方解释，作用，如何配置最优化建议

本站中文解释

SHARED_POOL_SIZE 参数，即共享内存池大小参数，是Oracle数据库中重要的系统参数，控制着SQL语句缓存区所占可用内存的大小。该缓存在Oracle实例启动时，由SGA（共享全局区 Shared Global Area）动态分配，缓存所有引用过的包括存储过程和函数在内的SQL和PL/SQL代码。

如何正确设置SHARED_POOL_SIZE：
1.在Oracle数据库系统启动时自动设置。
2.不要设置为过低。系统在启动时SHARED_POOL_SIZE参数的设置要尽可能的宽松，减少因Invalid对象数量造成的内存不足问题，否则系统运行时会受到影响。
3.不要设置过大。过大的SHARED_POOL_SIZE参数可能会占用大量的内存，使数据库系统剩余的内存不足，即使数据库在活跃期也会出现问题。

官方英文解释

SHARED_POOL_SIZE specifies (in bytes) the size of the shared pool.

Property Description

Parameter type

Big integer

Syntax

SHARED_POOL_SIZE = integer [K | M | G]

Default value

If SGA_TARGET is set: If the parameter is not specified, then the default is 0 (internally determined by the Oracle Database). If the parameter is specified, then the user-specified value indicates a minimum value for the memory pool.

If SGA_TARGET is not set (32-bit platforms): 64 MB, rounded up to the nearest granule size.

If SGA_TARGET is not set (64-bit platforms): 128 MB, rounded up to the nearest granule size.

For considerations when dealing with database instances using Oracle ASM, see “SHARED_POOL_SIZE and Automatic Storage Management”.

Modifiable

ALTER SYSTEM

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 to FALSE 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 the SGA_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 the SGA_TARGET value at the PDB level.

  • If the SGA_TARGET initialization parameter is not set, but the SHARED_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 the SHARED_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:

  • “V$SGASTAT”

  • 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


数据运维技术 » Oracle 参数 SHARED_POOL_SIZE 官方解释，作用，如何配置最优化建议
分享到：

相关推荐