2023年7月11日发(作者：)

如何估算PGA,SGA的⼤⼩,配置数据库服务器的内存　 ORACLE

给的建议是: OLTP系统 PGA=(Total Memory)*80%*20%。DSS系统PGA=(Total Memory)*80%*50%。

　　ORACLE建议⼀个数据库

，分80%的内存给数据库，20%的内存给操作系统，那怎么给⼀个数据库

配内存呢？

　　SQL> select * from v$pgastat;

　　-----这个值等于参数PGA_AGGREGATE_TARGET的值，如果此值为0，表⽰禁⽤了PGA⾃动 管理

。

　　aggregate PGA auto target 75220992 bytes

　　-----⼯作区执⾏的最⼤值，如果这个值⼩于1M，马上增加PGA⼤⼩

　　total PGA inuse 30167040 bytes

　　-----当前分配PGA的总⼤⼩，这个值有可能⼤于PGA，如果PGA设置太⼩.这个值接近select sum(pga_used_mem) from v$process.

　　total PGA allocated 52124672 bytes

　　-----⼯作区花费的总⼤⼩

　　maximum PGA allocated 67066880 bytes

　　total freeable PGA memory 0 bytes ----没有了空闲的PGA

　　process count 23 ----当前⼀个有23个process

　　max processes count 25

　　PGA memory freed back to OS 0 bytes

　　total PGA used for auto workareas 8891392 bytes

　　maximum PGA used for auto workareas 22263808 bytes

　　total PGA used for manual workareas 0 bytes ---为0⾃动管理

　　maximum PGA used for manual workareas 0 bytes ---为0⾃动管理

　　over allocation count 0

　　如果PGA设置太⼩，导致PGA有时⼤于PGA_AGGREGATE_TARGET的值，此处为0，说明PGA没有扩展⼤于TARGET的值，如果此值出现过，那么增加PGA⼤⼩。

　　bytes processed 124434432 bytes

　　extra bytes read/written 0 bytes

　　cache hit percentage 100 percent ---命中率为100%，如果太⼩增加PGA

　　如何设置PGA呢？我们可以在压⼒测试阶段，模拟⼀下系统的运⾏，然后运⾏

　　select (select sum(pga_used_mem)/1024/1024 from v$process) /(select count(*) from v$process) from dual;得到⼀个process⼤约占⽤了多少的内存，然后估算系统⼀共会有多少连接，⽐如⼀共有500个连接，

　　那么Sessions=1.1*process +5=500，那么processes=450,再乘以⼀个process需要消耗的内存，就能⼤约估算出PGA需要设置多⼤。 　　最好将PGA设置的值⽐计算出的值⼤⼀点，PGA值设定好后，就可以根据系统的性质,如果系统为OLTOP，那么总的内存可以设置为PGA/0.16,最后也能估算出SGA的⼤⼩，建议还是多配点内存，反正便宜。

　　下⾯摘抄eygle的关于⼀个process能够分配的最⼤内存(串⾏操作)的规则:

　　10gR1之前，对于串⾏操作(⾮并⾏)⼀个process能够分配的最⼤的内存为min(5%pga_aggregate_target,100m)

　　10gR2之后，对于串⾏操作(⾮并⾏)⼀个process能够分配的最⼤内存有如下规则:

　　如果pga_aggregate_target<=500m,那么最⼤的内存为20%*pga_aggregate_target.

　　如果500m

　　如果1000m

　　如果pga_aggregate_target>2.5G,那么最⼤内存为2.5G.

　　SQL> SELECT m NAME, vl VALUE, sc describ

　　2 FROM SYS.x$ksppi x, SYS.x$ksppcv y

　　3 WHERE _id = USERENV ('Instance')

　　4 AND _id = USERENV ('Instance')

　　5 AND =

　　6 AND m LIKE '%&par%'

　　7 /

　　NAME VALUE DESCRIB

　　-------------------------------------------------- ------------------------------ -------------------------------------------------- ------------------------------ -------------------------------------------------- ------------------------------

　　_smm_max_size 20480 maximum work area size in auto mode (serial)

　　SQL> show parameter pga

　　NAME TYPE VALUE

　　------------------------------------ ----------- ------------------------------

　　pga_aggregate_target big integer 100M

　　此处我的⼀个process能够分配的最⼤内存为20M，因为我的PGA=100M，符合上⾯的规则。

　　隐含参数_smm_max_size表⽰⼀个process能够分配最⼤的memory.

　　买了piner的《oracle⾼可⽤环境》⼀书，正好趁这段时间学习⼀下。

　　把看到的东西总结⼀下发表于此，今天先发第⼀章关于SGA与PGA的内容。

　　以后会陆续将总结在此发表，与⼤家共享。 　　SGA与PGA的结构如下图：

　　SGA:

　　查看SGA：

　　Sqlp> show sga

　　或 select * from v$sga;

　　Total System Global Area 289406976 bytes

　　Fixed Size 1248600 bytes

　　Variable Size 176161448 bytes

　　Database Buffers 109051904 bytes

　　Redo Buffers 2945024 bytes

　　Fixed Size：包括了数据库与实例的控制信息、状态信息、字典信息等，启动时就被固定在SGA中，不会改变。

　　Variable Size：包括了shard pool、large pool、 java

pool、stream pool、游标区和其他结构

　　Database Buffers：数据库中数据块缓冲的地⽅，是SGA中最⼤的地⽅，决定数据库性能

　　Redo Buffers：提供REDO缓冲的地⽅，在OLAP中不需要太⼤

　　V$sgastat记录了SGA的⼀些统计信息

　　V$sga_dynamic_components保存SGA中可以⼿动调整的区域的⼀些调整记录

　　Shard pool:

　　Shard_pool_size决定其⼤⼩， 10g

以后⾃动管理

　　Shard_pool中数据字典和控制区结构⽤户⽆法直接控制，与⽤户有关的只有 sql

缓冲区（library cache）。

　　将经常访问的过程或包⽤DBMS_SHARED_存储过程将该包pin在共享池中。

　　⼿⼯清除共享池的内容：alter system flush shard_pool;

　　共享池相关的⼏个

的视图：

　　V$sqlarea 记录了所有sql的统计信息，包括执⾏次数、物理读、逻辑读、耗费时间等

　　V$sqltext_with_newline 完全显⽰sql语句，通过hash_value来标⽰语句，piece排序

　　V$sql_plan保存了sql的执⾏计划，通过⼯具查看

　　V$shared_pool_advice对共享池的预测，可以做调整SGA的参考

　　Data buffer：

　　在OLTP系统中要求data buffer 的命中率在95%以上

　　select sum(pins) "execution",sum(pinhits) "hits", 　　((sum(pinhits)/sum(pins))*100) "pinhitration",

　　sum(reloads) "misses",((sum(pins)/(sum(pins)

　　+sum(reloads)))*100) "relhitratio"

　　from V$librarycache

　　PINS NUMBER Number of times a PIN was requested for objects of this namespace

　　PINHITS NUMBER Number of times all of the metadata pieces of the library object were found in memory

　　RELOADS NUMBER Any PIN of an object that is not the first PIN performed since the object handle was created, and which requires loading the objectfrom disk

　　Oracle把从data buffer中获得的数据库叫cache hit，把从磁盘获得的脚cache miss

　　数据缓冲区中的数据块通过脏列表（dirty list）和LRU列表（LRU list）来管理。

　　Data buffer可细分为：default pool、keep pool、recycle pool对应的参数为db_cache_size、 db_keep_cache_size 、db_recycle_size分别表⽰缓冲区⼤⼩

　　从9i开始oracle⽀持不同块⼤⼩的表空间，相应的可以为不同块⼤⼩的表空间指定不同块⼤⼩的数据缓冲区，不同块⼤⼩的数据缓冲区可以⽤相应的db_nk_cache_size来指定，其中n可以是2、4、6、16或32

　　V$db_cache_advice 对数据缓冲区的预测，可以做调整data buffer的参考

　　V$bh、x$bh记录了数据块在data buffer中缓冲的情况，通过这个视图可以找系统中的热点块。通过下⾯语句找系统中top 10 热点快所在的热点对象:

　　Select /*+ rule*/ owner,object_name from dba_objects

　　Where data_object_id in

　　(select obj from

　　(select obj from x$bh order by tch desc)

　　Where rownum<11);

　　PGA：

　　⽤来保存于⽤户进程相关的内存段。

　　从9i开始使⽤PGA⾃动管理，pga_aggregate_target参数指定session⼀共使⽤的最⼤PGA内存的上限。Workarea_size_policy参数⽤于开关PGA内存⾃动管理功能，auto/manual

　　在OLTP环境中，⾃动PGA管理只要设置到⼀定的值，如2G左右就能满⾜系统的要求。

　　⾃动内存管理：

　　从9i开始，sga_max_size参数设置SGA的内存⼤⼩，不能动态修改

　　从10g开始，指定了sga_target参数后，所有的SGA组件如：shared pool、 data buffer、 large pool都不⽤⼿⼯指定了，Oracle会⾃动管理。这⼀特性就是⾃动共享内存管理ASMM。如果设置了sga_target=0,就⾃动关闭⾃动共享内存管理功能。Sga_target⼤⼩不能超过sga_max_size的⼤⼩。

　　⼿动管理SGA： 　　Alter system set sga_target=2000m;

　　Alter system set db_cache_size=1000m;

　　Alter system set shared_pool=200m;

　　Alter system set sga_target=0---------关闭⾃动共享内存管理ASMM

　　 11G

以后sga+pga整个内存可以⾃动管理AMM，相关参数memory_max_target memory_target.设置好这两个参数后就不⽤关⼼SGA和PGA了

　　11g⼿动内存管理：

　　Alter system set memory_target=3000m;

　　Alter system set sga_target=2000m;

　　Alter system set pga_aggregate_target=1000m;

　　Alter system set memory_target=0;---------关闭⾃动内存管理AMM

　　SGA+PGA最好不要超过总内存的70%