共享池内存分配和错误ORA-00371

不论是sga自动管理还是手动管理，共享池都是由2部分内存组成的，一部分是供我们用户使用，这部分就是我们熟悉的library cache和dictionary cache，另一部分就是oracle内部使用的，比如参数process的大小直接决定了使用这部分内部使用的内存大小。

[@more@]

Specifying the Shared Pool Size

The SHARED_POOL_SIZE initialization parameter is a dynamic parameter that lets you specify or adjust the size of the shared pool component of the SGA. Oracle Database selects an appropriate default value.

In releases before Oracle Database 10g Release 1, the amount of shared pool memory that was allocated was equal to the value of the SHARED_POOL_SIZE initialization parameter plus the amount of internal SGA overhead computed during instance startup. The internal SGA overhead refers to memory that is allocated by Oracle during startup, based on the values of several other initialization parameters. This memory is used to maintain state for different server components in the SGA. For example, if the SHARED_POOL_SIZE parameter is set to 64MB and the internal SGA overhead is computed to be 12MB, the real size of shared pool in the SGA is 64+12=76MB, although the value of the SHARED_POOL_SIZE parameter is still displayed as 64MB.Starting with Oracle Database 10g Release 1, the size of internal SGA overhead is included in the user-specified value of SHARED_POOL_SIZE. In other words, if you are not using the automatic shared memory management feature, then the amount of shared pool memory that is allocated at startup is exactly equal to the value of SHARED_POOL_SIZE initialization parameter. In manual SGA mode, this parameter must be set so that it includes the internal SGA overhead in addition to the desired value of shared pool size. In the previous example, if the SHARED_POOL_SIZE parameter is set to 64MB at startup, then the available shared pool after startup is 64-12=52MB, assuming the value of internal SGA overhead remains unchanged. In order to maintain an effective value of 64MB for shared pool memory after startup, you must set the SHARED_POOL_SIZE parameter to 64+12=76MB.

The Oracle Database 10g migration utilities recommend a new value for this parameter based on the value of internal SGA overhead in the pre-upgrade environment and based on the old value of this parameter. In Oracle Database 10g, the exact value of internal SGA overhead, also known as startup overhead in the shared pool, can be queried from the V$SGAINFO view. Also, in manual SGA mode, if the user-specified value of SHARED_POOL_SIZE is too small to accommodate even the requirements of internal SGA overhead, then Oracle generates an ORA-371 error during startup, along with a suggested value to use for the SHARED_POOL_SIZE parameter.When you use automatic shared memory management in Oracle Database 10g, the shared pool is automatically tuned, and an ORA-371 error would not be generated by Oracle.

--===================================

SQL> show parameter shared

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
max_shared_servers integer
shared_memory_address integer 0
shared_pool_reserved_size big integer 4823449
shared_pool_size big integer 92M
shared_server_sessions integer
shared_servers integer 0
SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 160M
sga_target big integer 160M
SQL> col name format a40
SQL> set linesize 200
SQL> select * from V$SGAINFO ;

NAME BYTES RES
---------------------------------------- ---------- ---
Fixed SGA Size 1247876 No
Redo Buffers 7139328 No
Buffer Cache Size 41943040 Yes
Shared Pool Size 96468992 Yes
Large Pool Size 16777216 Yes
Java Pool Size 4194304 Yes
Streams Pool Size 0 Yes
Granule Size 4194304 No
Maximum SGA Size 167772160 No
Startup overhead in Shared Pool 41943040 No
Free SGA Memory Available 0

11 rows selected.

SQL> show parameter sga_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 160M
SQL> show parameter shared_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 92M
SQL> alter system set sga_target=0;

System altered.

SQL> alter system set shared_pool_size=24m;
alter system set shared_pool_size=24m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04034: unable to shrink pool to specified size

SQL> alter system set shared_pool_size=24m scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00371: not enough shared pool memory, should be atleast 77298483 bytes
SQL>

--====================================

SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1247876 bytes
Variable Size 117441916 bytes
Database Buffers 41943040 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.
SQL> col name format a40
SQL> set linesize 200
SQL> select * from V$SGAINFO ;

NAME BYTES RES
---------------------------------------- ---------- ---
Fixed SGA Size 1247876 No
Redo Buffers 7139328 No
Buffer Cache Size 41943040 Yes
Shared Pool Size 96468992 Yes
Large Pool Size 16777216 Yes
Java Pool Size 4194304 Yes
Streams Pool Size 0 Yes
Granule Size 4194304 No
Maximum SGA Size 167772160 No
Startup overhead in Shared Pool 41943040 No
Free SGA Memory Available 0

11 rows selected.

SQL> show parameter process

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0
db_writer_processes integer 4
gcs_server_processes integer 0
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 150
SQL> alter system set processes=300 scope=spfile;
alter system set processes=300 scope=spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup

--由于没有使用spfile启动，所以上面出现了错误，手动编辑initSID.ora文件，设置processes=300...
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1247876 bytes
Variable Size 117441916 bytes
Database Buffers 41943040 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.
SQL> show parameter process

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0
db_writer_processes integer 4
gcs_server_processes integer 0
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 300
SQL> col name format a40
SQL> set linesize 200
SQL> select * from V$SGAINFO ;

NAME BYTES RES
---------------------------------------- ---------- ---
Fixed SGA Size 1247876 No
Redo Buffers 7139328 No
Buffer Cache Size 41943040 Yes
Shared Pool Size 96468992 Yes
Large Pool Size 16777216 Yes
Java Pool Size 4194304 Yes
Streams Pool Size 0 Yes
Granule Size 4194304 No
Maximum SGA Size 167772160 No
Startup overhead in Shared Pool 50331648 No
Free SGA Memory Available 0

11 rows selected.

SQL>

--把参数processes从150修改为300之后，很显然oracle内部使用的共享池内存Startup overhead in Shared Pool又原来的41943040增加为50331648：

SQL> select (50331648 - 41943040)/(300 - 150) from dual;

(50331648-41943040)/(300-150)
-----------------------------
55924.0533

SQL>

oracle为每一个进程大约预留了56k的内存，这也是我们常说的processes参数不能设置太大的原因，设置的太大会浪费内存不说而且甚至导致实例无法启动的原因。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19602/viewspace-1048062/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/19602/viewspace-1048062/