oracle 更改 sga+pga 大小

前接天需要改一下Oracle的内存参数，本来oracle 装机时候已经配置好了sga 和pga的大小了，但是有好多问题随之出现。linux 用的是16G内存，sga 内存12G，pga 4G,所以linux 系统很慢。改打开连接数：SQL>show parameter open_cursors; ---显示参数值SQL>alter system set open_cursors = 1000; ---修改参数值为1000SQL>commit; ----提交修改成功。（转）Oracle 调整SGA、PGA大小博客分类： OracleSQL> show parameter sga; SQL> alter system set sga_max_size=4096M scope=spfile;System altered.SQL> alter system set sga_target=4096M scope=spfile;System altered. 如果是RAC环境，需要这样增加sid='*'： alter system set sga_target=20G scope=spfile sid='*'; 重启使更改生效： SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area 4294967296 bytesFixed Size                  2026296 bytesVariable Size             805307592 bytesDatabase Buffers         3472883712 bytesRedo Buffers               14749696 bytesDatabase mounted.Database opened.SQL> show parameter sga;NAME                                 TYPE        VALUE ----------- ------------------------------lock_sga                             boolean     FALSEpre_page_sga                         boolean     FALSEsga_max_size                         big integer 4Gsga_target                           big integer 4G SQL> show parameter pga； 不用重启DB，直接在线修改。  SQL> alter system set workarea_size_policy=auto scope=both;  System altered. SQL> alter system set pga_aggregate_target=512m scope=both;  System altered.  SQL> show parameter workarea  NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ workarea_size_policy                 string      AUTO --这个设置成AUTO SQL> show parameter pga  NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_target                 big integer 536870912 SELECT * FROM V$PGASTAT; aggregate PGA auto target                                         477379584 bytes    --当前可用于自动分配了的PGA大小，应该比PGA_AGGREGATE_TARGET 小 over allocation count                                                  1118    --实例启动后，发生的分配次数，如果这个值大于0，就要考虑增加pga的值   1 查看 SGA大小  show parameter sga_max_size  db_cache_size的尺寸受 SGA的影响为能大于SGA 2 查看 show parameter shared_pool_size 尺寸 一般来说 shared_pool_size+db_cache_size=SGA_MAX_SIZE*70%左右 经过计算再修改db_cache_size   sql>alter system set db_cache_size= 大小M  scope=spfile sid='数据库SID'; sql>shutdown immediate sql>startup sql>show parameter db_cache_size 就可以看到修改后的效果了[oracle@oracle_db ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.4.0 - Production on Wed Aug 5 21:56:47 2015Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> SQL> SQL> SQL> show parameter sga;NAME                                 TYPE       VALUE------------------------------------ ----------- ------------------------------lock_sga                             boolean    FALSEpre_page_sga                         boolean    FALSEsga_max_size                         big integer 12Gsga_target                           big integer 12GSQL> alter system set sga_max_size=10240M scope=spfile;System altered.SQL> alter system set sga_target=10240M scope=spfile;System altered.SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area 1.0737E+10 bytesFixed Size             2101808 bytesVariable Size               1560284624 bytesDatabase Buffers    9160359936 bytesRedo Buffers                  14671872 bytesDatabase mounted.Database opened.SQL>  show parameter sga;NAME                                 TYPE       VALUE------------------------------------ ----------- ------------------------------lock_sga                             boolean    FALSEpre_page_sga                         boolean    FALSEsga_max_size                         big integer 10Gsga_target                           big integer 10GSQL> show parameter pga；SQL> alter system set workarea_size_policy=auto scope=both; System altered.SQL> alter system set pga_aggregate_target=2048m scope=both;System altered.SQL>  show parameter workarea NAME                                 TYPE       VALUE------------------------------------ ----------- ------------------------------workarea_size_policy                 string     AUTOSQL> show parameter workarea NAME                                 TYPE       VALUE------------------------------------ ----------- ------------------------------workarea_size_policy                 string     AUTOSQL> show parameter pga；SQL> show parameter sga;NAME                                 TYPE       VALUE------------------------------------ ----------- ------------------------------lock_sga                             boolean    FALSEpre_page_sga                         boolean    FALSEsga_max_size                         big integer 10Gsga_target                           big integer 10GSQL> SQL> SQL> SQL> 最后的结果是改成sga 10G + pga 2G ,系统内存 4G，swap 分区 内存32G。改后效果并不是很明显，需要后续观察