共享池記憶體分配和錯誤ORA-00371

不論是sga自動管理還是手動管理，共享池都是由2部分記憶體組成的，一部分是供我們使用者使用，這部分就是我們熟悉的library cache和dictionary cache，另一部分就是oracle內部使用的，比如引數process的大小直接決定了使用這部分內部使用的記憶體大小。

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

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 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引數不能設定太大的原因，設定的太大會浪費記憶體不說而且甚至導致例項無法啟動的原因。