背景

     oracle10g随着提供了SGA自动管理功能，不用DBA去手工依次配置各个子组件的大小，交由ORACLE动态管理，但是，在一些特定场合下，仍需要
我们禁用SGA自动管理功能，使用SGA手工管理功能。

结论

1,sga手工管理，仅需配置sga_target为0或不配置此参数
2,sga手工管理模式下，其子组件必须手工指定值，否则会报错
3,sga手工管理模式下，共享池大小配置情况如下：
   A,必须要考虑用于管理SGA各组件且存储于共享池中的固定内存大小这块的成本开销
   B,到于A的信息可从v$sgainfo获取
   C,ORACLE11G R1前共享池真正的大小是由2部分组成,shared_pool_size + 管理SGA各组件且存储于共享池中的固定内存大小这块的成本开销
     而从oracle11g r1开始，管理SGA各组件且存储于共享池中的固定内存大小这块的成本开销已经纳入shared_pool_size中，则共享池大小=shared_pool_size
4,如果以PFILE启动ORACLE，不配置共享池，ORACLE会默认使用一个值
5,如果以PFILE启动ORACLE，如果配置共享池过小，会报ora-431错误
6,关于SGA当前还有多少可用空闲内存可以从v$sgainfo的Free SGA Memory Available看出来

测试

1，启用SGA手工管理，只要配置sga_target为0或不配置此参数即可，此时你必须手工指定SGA各个子组件的大小


 ---SGA自动管理情况下的参数配置信息
SQL> show parameter sga_


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 1104M
sga_target                           big integer 944M
SQL> 
SQL> 
SQL> show parameter _cache_size


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size                    big integer 16M
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_cache_size                        big integer 512M
db_keep_cache_size                   big integer 0
db_recycle_cache_size                big integer 0
SQL> show parameter _pool_size


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_context_pool_size             string
java_pool_size                       big integer 0
large_pool_size                      big integer 0
olap_page_pool_size                  big integer 0
shared_pool_size                     big integer 64M
streams_pool_size                    big integer 0
SQL> select name,bytes/1024/1024 as mb,RESIZEABLE as mb from  v$sgainfo;


NAME                                     MB MB
-------------------------------- ---------- ---
Fixed SGA Size                   1.99871063 No
Redo Buffers                             30 No
Buffer Cache Size                       736 Yes
Shared Pool Size                        112 Yes
Large Pool Size                          16 Yes
Java Pool Size                           16 Yes
Streams Pool Size                        32 Yes
Granule Size                             16 No
Maximum SGA Size                       1104 No
Startup overhead in Shared Pool          48 No
Free SGA Memory Available               160


11 rows selected.


SQL> 


--SGA手工管理的参数配置信息
SQL> alter system set sga_target=0;


System altered.


SQL> show parameter sga_


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 1104M
sga_target                           big integer 0


SQL> show parameter _cache_size


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size                    big integer 16M
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_cache_size                        big integer 720M
db_keep_cache_size                   big integer 0
db_recycle_cache_size                big integer 0




SQL> show parameter _pool_size


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_context_pool_size             string
java_pool_size                       big integer 16M
large_pool_size                      big integer 16M
olap_page_pool_size                  big integer 0
shared_pool_size                     big integer 112M
streams_pool_size                    big integer 32M




SQL> select name,bytes/1024/1024 as mb,RESIZEABLE as mb from  v$sgainfo;


NAME                                     MB MB
-------------------------------- ---------- ---
Fixed SGA Size                   1.99871063 No
Redo Buffers                             30 No
Buffer Cache Size                       736 Yes
Shared Pool Size                        112 Yes
Large Pool Size                          16 Yes
Java Pool Size                           16 Yes
Streams Pool Size                        32 Yes
Granule Size                             16 No
Maximum SGA Size                       1104 No
Startup overhead in Shared Pool          48 No
Free SGA Memory Available               160


11 rows selected.




SQL> alter system set shared_pool_size=220m;


System altered.


SQL> select name,bytes/1024/1024 as mb,RESIZEABLE as mb from  v$sgainfo;


NAME                                     MB MB
-------------------------------- ---------- ---
Fixed SGA Size                   1.99871063 No
Redo Buffers                             30 No
Buffer Cache Size                       736 Yes
Shared Pool Size                        224 Yes
Large Pool Size                          16 Yes
Java Pool Size                           16 Yes
Streams Pool Size                        32 Yes
Granule Size                             16 No
Maximum SGA Size                       1104 No
Startup overhead in Shared Pool          48 No
Free SGA Memory Available                48


在启用SGA手工管理情况下，SGA各子组件不能指定为0
SQL> alter system set shared_pool_size=0;
alter system set shared_pool_size=0
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04034: unable to shrink pool to specified size


在启用SGA手工管理情况下，如果指定共享池过大，也不行，因为可能会超过sga_max_size
SQL> alter system set shared_pool_size=1220m;
alter system set shared_pool_size=1220m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool






SQL> alter system set db_cache_size=0;
alter system set db_cache_size=0
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00383: DEFAULT cache for blocksize 8192 cannot be reduced to zero




SQL> alter system set db_cache_size=0 scope=spfile;
alter system set db_cache_size=0 scope=spfile
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00383: DEFAULT cache for blocksize 8192 cannot be reduced to zero


java_pool就是配置为0，实际还是有值
SQL> show parameter _pool_size


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_context_pool_size             string
java_pool_size                       big integer 16M
large_pool_size                      big integer 16M
olap_page_pool_size                  big integer 0
shared_pool_size                     big integer 128M
streams_pool_size                    big integer 32M
SQL> alter system set java_pool_size=0;


System altered.


SQL> show parameter _pool_size


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_context_pool_size             string
java_pool_size                       big integer 16M
large_pool_size                      big integer 16M
olap_page_pool_size                  big integer 0
shared_pool_size                     big integer 128M
streams_pool_size                    big integer 32M


--关于SGA还有多少可用空闲内存可以从v$sgainfo的Free SGA Memory Available看出来
SQL> alter system set shared_pool_size=120m;


System altered.


SQL> select name,bytes/1024/1024 as mb,RESIZEABLE as mb from  v$sgainfo;


NAME                                     MB MB
-------------------------------- ---------- ---
Fixed SGA Size                   1.99871063 No
Redo Buffers                             30 No
Buffer Cache Size                       736 Yes
Shared Pool Size                        128 Yes
Large Pool Size                          16 Yes
Java Pool Size                           16 Yes
Streams Pool Size                        32 Yes
Granule Size                             16 No
Maximum SGA Size                       1104 No
Startup overhead in Shared Pool          48 No
Free SGA Memory Available               144


11 rows selected.


2,在SGA手工管理模式下，如何配置共享池大小


---查看shared_pool_size及为了管理SGA各个组件且驻存于共享池中的固定成本内存，可见2者分别为128m和48m
SQL> select name,bytes/1024/1024 as mb,RESIZEABLE as mb from  v$sgainfo;


NAME                                     MB MB
-------------------------------- ---------- ---
Fixed SGA Size                   1.99871063 No
Redo Buffers                             30 No
Buffer Cache Size                       736 Yes
Shared Pool Size                        128 Yes
Large Pool Size                          16 Yes
Java Pool Size                           16 Yes
Streams Pool Size                        32 Yes
Granule Size                             16 No
Maximum SGA Size                       1104 No
Startup overhead in Shared Pool          48 No
Free SGA Memory Available               144


11 rows selected.


SQL> show parameter shared_pool_size


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 128M


查看隐含参数也可获在共享池的实际大小
NAME_1                         VALUE_1                                                                                              DESC1
------------------------------ ---------------------------------------------------------------------------------------------------- --------------------------------------------------
__shared_pool_size             134217728                                                                                            Actual size in bytes of shared pool




不能把共享池大小配置过小，否则会报错
SQL> alter system set shared_pool_size=90m;
alter system set shared_pool_size=90m
*
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=100m;


System altered.


SQL> select name,bytes/1024/1024 as mb,RESIZEABLE as mb from  v$sgainfo;


NAME                                     MB MB
-------------------------------- ---------- ---
Fixed SGA Size                   1.99871063 No
Redo Buffers                             30 No
Buffer Cache Size                       736 Yes
Shared Pool Size                        112 Yes
Large Pool Size                          16 Yes
Java Pool Size                           16 Yes
Streams Pool Size                        32 Yes
Granule Size                             16 No
Maximum SGA Size                       1104 No
Startup overhead in Shared Pool          48 No
Free SGA Memory Available               160


11 rows selected.




ORACLE 11G R1之前，共享池大小=shared_pool_size + Startup overhead in Shared Pool 


而从oracle 11g r1之后，共享池大小=shared_pool_size,因为Startup overhead in Shared Pool已经包括在shared_pool_size中。
但不管如何配置，真正可用的共享池大小一定要去除Startup overhead in Shared Pool 
而Startup overhead in Shared Pool 可以从v$sgainfo获知。






如果在SGA手工管理模式下，shared_pool_size配置过小，会报ora-371错误
SQL> show parameter spfile


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /home/ora10g/product/10.2.0/db
                                                 _1/dbs/spfileora10g.ora


SQL> create pfile='/home/ora10g/sga_manual.ora' from spfile;


File created.




[ora10g@seconary ~]$ more /home/ora10g/sga_manual.ora|grep -i --color shared_pool_size
ora10g.__shared_pool_size=117440512
*.shared_pool_size=117440512


谳整前共享池为112M
SQL> select 117440512/1024/1024 mb from dual;


        MB
----------
       112


调整共享池后，发现共享池大小配置为96M，可见不配置shared_pool_size参数，默认配置为96M
[ora10g@seconary ~]$ more /home/ora10g/sga_manual.ora|grep -i --color shared_pool_size
ora10g.__shared_pool_size=117440512
#*.shared_pool_size=117440512




SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup pfile='/home/ora10g/sga_manual.ora
ORACLE instance started.


Total System Global Area 1157627904 bytes
Fixed Size                  2095800 bytes
Variable Size             352322888 bytes
Database Buffers          771751936 bytes
Redo Buffers               31457280 bytes
Database mounted.
Database opened.
SQL> show parameter sga_


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 1104M
sga_target                           big integer 0
SQL> show parameter shared_pool


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size            big integer 5033164
shared_pool_size                     big integer 96M


SQL> select name,bytes/1024/1024 as mb,RESIZEABLE as mb from  v$sgainfo;


NAME                                     MB MB
-------------------------------- ---------- ---
Fixed SGA Size                   1.99871063 No
Redo Buffers                             30 No
Buffer Cache Size                       736 Yes
Shared Pool Size                         96 Yes
Large Pool Size                          16 Yes
Java Pool Size                           16 Yes
Streams Pool Size                        32 Yes
Granule Size                             16 No
Maximum SGA Size                       1104 No
Startup overhead in Shared Pool          48 No
Free SGA Memory Available               176


11 rows selected.




如果配置shared_pool_size过小，又会如何,会报ora-431错误,当然这种情况只会出现于SGA手工管理方式了哟
[ora10g@seconary ~]$ more /home/ora10g/sga_manual.ora|grep -i --color shared_pool_size
ora10g.__shared_pool_size=117440512
*.shared_pool_size=20m


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup pfile='/home/ora10g/sga_manual.ora
ORA-00371: not enough shared pool memory, should be atleast 148397977 bytes
SQL> 


可见共享池最小配置为141M左右
SQL> select 148397977/1024/1024 from dual;


148397977/1024/1024
-------------------
         141.523339






3,在SGA手工管理模式下，如何配置大池，首先大池是可选参数，前提是你必须使用大池的功能


SQL> show parameter large_


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
large_pool_size                      big integer 16M         

个人简介

8年oracle从业经验，具备丰富的oracle技能，目前在国内北京某专业oracle服务公司从事高级技术顾问。

服务过的客户：

中国电信

中国移动

中国联通

中国电通

国家电网

四川达州商业银行

湖南老百姓大药房

山西省公安厅

中国邮政

北京302医院     

河北廊坊新奥集团公司

 项目经验:

中国电信3G项目AAA系统数据库部署及优化
      中国联通4G数据库性能分析与优化

中国联通CRM数据库性能优化

中国移动10086电商平台数据库部署及优化

湖南老百姓大药房ERR数据库sql优化项目

四川达州商业银行TCBS核心业务系统数据库模型设计和RAC部署及优化

四川达州商业银行TCBS核心业务系统后端批处理存储过程功能模块编写及优化

北京高铁信号监控系统RAC数据库部署及优化

河南宇通客车数据库性能优化

中国电信电商平台核心采购模块表模型设计及优化

中国邮政储蓄系统数据库性能优化及sql优化

北京302医院数据库迁移实施

河北廊坊新奥data guard部署及优化

山西公安厅身份证审计数据库系统故障评估

国家电网上海灾备项目4 node rac+adg 
       贵州移动crm及客服数据库性能优化项目
       贵州移动crm及客服务数据库sql审核项目
       深圳穆迪软件有限公司数据库性能优化项目

联系方式：

手机：18201115468

qq   :   305076427

qq微博: wisdomone1

新浪微博：wisdomone9

qq群：275813900    

itpub博客名称：wisdomone1    http://blog.itpub.net/9240380/