重庆分公司
重庆分公司，新征程启航
为企业提供网站建设、域名注册、服务器等服务
Oracle体系结构之内存结构（SGA、PGA）
Oracle体系结构之内存结构（SGA、PGA）
一、内存结构
SGA(System Global Area)：由所有服务进程和后台进程共享；
PGA(Program Global Area)：由每个服务进程、后台进程专有；每个进程都有一个PGA。
让客户满意是我们工作的目标，不断超越客户的期望值来自于我们对这个行业的热爱。我们立志把好的技术通过有效、简单的方式提供给客户，将通过不懈努力成为客户在信息化领域值得信任、有价值的长期合作伙伴，公司提供的服务项目有：域名注册、网页空间、营销软件、网站建设、达孜网站维护、网站推广。
二、SGA
包含实例的数据和控制信息，包含如下内存结构：
1）Database buffer cache：缓存了从磁盘上检索的数据块。
2）Redo log buffer：缓存了写到磁盘之前的重做信息。
3）Shared pool：缓存了各用户间可共享的各种结构。
4）Large pool：一个可选的区域，用来缓存大的I/O请求，以支持并行查询、共享服务器模式以及某些备份操作。
5）Java pool：保存java虚拟机中特定会话的数据与java代码。
6）Streams pool：由Oracle streams使用。
7）Keep buffer cache：保存buffer cache中存储的数据，使其尽时间可能长。
8）Recycle buffer cache：保存buffer cache中即将过期的数据。
9）nK block size buffer：为与数据库默认数据块大小不同的数据块提供缓存。用来支持表空间传输。
database buffer cache, shared pool, large pool, streams pool与Java pool根据当前数据库状态，自动调整；
keep buffer cache,recycle buffer cache,nK block size buffer可以在不关闭实例情况下，动态修改。
三、PGA
每个服务进程私有的内存区域，包含如下结构：
1）Private SQL area：包含绑定信息、运行时的内存结构。每个发出sql语句的会话，都有一个private SQL area（私有SQL区）
2）Session memory：为保存会话中的变量以及其他与会话相关的信息，而分配的内存区。
四、SGA COMPONENT
(一)、Buffer Cache
1、DB_CACHE_SIZE
通过参数DB_CACHE_SIZE可指定DB buffer cache的大小
ALTER SYSTEM SET DB_CACHE_SIZE=20M scope=both;
服务进程从数据文件读数据到buffer cache；DBWn从buffer cache写数据到数据文件。
buffer cache的四种状态：
1）pinned：当前块正在读到cache或正写到磁盘，其他会话等待访问该块。
2）clean：
3）free/unused：buffer内为空，为实例刚启动时的状态。
4）dirty：脏数据，数据块被修改，需要被DBWn刷新到磁盘，才能执行过期处理。
同一个数据库中，支持多种大小的数据块缓存。通过DB_nK_CACHE_SIZE参数指定，如
? DB_2K_CACHE_SIZE
? DB_4K_CACHE_SIZE
? DB_8K_CACHE_SIZE
? DB_16K_CACHE_SIZE
? DB_32K_CACHE_SIZE
标准块缓存区大小由DB_CACHE_SIZE指定。如标准块为nK，则不能通过DB_nK_CACHE_SIZE来指定标准块缓存区的大小，应由DB_CACHE_SIZE指定。
例，标准块为8K，则数据库可以设置的块缓存大小的参数如下：
? DB_CACHE_SIZE （指定标准块（这里为8K）的缓存区）
? DB_2K_CACHE_SIZE （指定块大小为2K的缓存区）
? DB_4K_CACHE_SIZE （指定块大小为4K的缓存区）
? DB_16K_CACHE_SIZE （指定块大小为16K的缓存区）
? DB_32K_CACHE_SIZE （指定块大小为32K的缓存区）
2、多种缓冲池（buffer pool）
1）Keep：通过db_keep_cache_size参数指定。
该buffer内的数据可能被重用，以降低I/O操作。该池的大小要大于指定到该池的段的总和。
读入到keep buffer的块不需要过期操作。
2）Recycle：通过db_recycle_cache_size参数指定。
该池中的数据被重用机会较小，该池大小要小于分配到该池的段的总和。读入该池的块需要经常执行过期处理。
3）Default：相当于一个没有Keep与Recycle池的实例的buffer cache，通过db_cache_size参数指定。
3、为对象明确指定buffer pool
buffer_pool子句，用来为对象指定默认的buffer pool，是storage子句的一部分。
对create与alter table、cluster、index语句有效。
如果现有对象没有明确指定buffer pool，则默认都指定为default buffer pool，大小为DB_CACHE_SIZE参数设置的值。
语法：
a.CREATE INDEX cust_idx ON tt(id) STORAGE (BUFFER_POOL KEEP);
b.ALTER TABLE oe.customers STORAGE (BUFFER_POOL RECYCLE);
c.ALTER INDEX oe.cust_lname_ix STORAGE (BUFFER_POOL KEEP);
(二)、Share Pool
1、SHARE_POOL_SIZE
1）Share Pool可通过SHARE_POOL_SIZE参数指定：
SQL> alter system set shared_pool_size=20M scope=both;
2）Share Pool保存的信息被多个会话共享，类型包括：
a.Library Cache
Library Cache又包含共享SQL区与PL/SQL区：
a).共享SQL区保存了分析与编译过的SQL语句。
b).PL/SQL区保存了分析与编译过的PL/SQL块（过程和函数、包、触发器与匿名PL/SQL块）。
b.Data Dictionary Cache
保存了数据字典对象的定义。
c.UGA(User Global Area)
UGA内包含了共享服务器模式下的会话信息。
共享服务器模式时，如果large pool没有配置，则UGA保存在Share Pool中。
(三)、Large Pool 
1）Large Pool大小通过LARGE_POOL_SIZE参数指定：
SQL> alter system set large_pool_size=20m scope=both;
2）作用:
a.为I/O服务进程分配内存
b.为备份与恢复操作分配内存
c.为Oracle共享服务器模式与多个数据库间的联机事务分配内存。
通过从large pool中为共享服务器模式分配会话内存，可以减少share pool因频繁为大对象分配和回收内存而产生的碎片。将大的对象从share pool中分离出来，可以提高shared pool的使用效率，使其可以为新的请求提供服务或者根据需要保留现有的数据。
(四)、Java Pool 
1、JAVA_POOL_SIZE
通过JAVA_POOL_SIZE参数指定java pool大小。
保存了jvm中特定会话的java code和数据。
2、在编译数据库中的java代码和使用数据库中的java资源对象时，都会用到share pool。
java的类加载程序对每个加载的类会使用大约8K的空间。
系统跟踪运行过程中，动态加载的java类，也会使用到share pool。
(五)、Redo Log Buffer
1、服务进程从用户空间拷贝每条DML/DDL语句的redo条目到redo log buffer中。
2、redo log buffer是一个可以循环使用的buffer，服务进程拷贝新的redo覆盖掉redo log buffer中已通过LGWR写入磁盘（online redo log）的条目。
3、导致LGWR执行写redo log buffer到online redo log的条件
a.用户执行事务提交commit
b.每3秒钟或redo log buffer内已达到1/3满或包含1MB数据
c.DBWn进程将修改的缓冲区写入磁盘时（如果相应的重做日志数据尚未写入磁盘）
（六）、ASMM(Automatic Shared Memory Management)
1、SGA_TARGET
1）SGA_TARGET默认值为0，即ASMM被禁用。需要手动设置SGA各中各组件的大小。
2）当SGA_TARGET为非0时，则启用ASMM，自动调整以下各组件大小：
DB buffer cache（default pool）
shared pool
large pool
streams pool
java pool
但ASSM中， 以下参数仍需要手动指定：
log buffer
keep、recycle、以及非标准块缓冲区
固定SGA以及其他内部分配。
2、启用ASMM需要将STATISTICS_LEVEL设置成TYPICAL或ALL
3、启用ASMM，自动调整SGA内部组件大小后。若手动指定某一组件值，则该值为该组件的最小值。如
手动设置SGA_TARGET=8G,SHARE_POOL_SIZE=1G，则ASMM在自动调整SGA内部组件大小时，保证share pool不会低于1G。
SQL> SELECT component, current_size/1024/1024 size_mb FROM v$sga_dynamic_components;
4、SGA_MAX_SIZE
SGA_MAX_SIZE指定内存中可以分配给SGA的最大值。
SGA_TARGET是一个动态参数，其最大值为SGA_MAX_SIZE指定的值。
五、PGA
（一）Private SQL Area
1、保存了当前会话的绑定信息以及运行时内存结构。这些信息
2、每个执行sql语句的会话，都有一个private sql area。
3、当多个用户执行相同的sql语句，此sql语句保存在一个称为shared sql area。此share sql area被指定给这些用户的private sql area
4、共享服务器模式：private sql area位于SGA的share pool或large pool中
专用服务器模式：private sql area位于PGA中
（二）Cursor、SQL Areas
（三）Work Area
PGA的一大部分被分配给Work Area，用来执行如下操作：
a.基于操作符的排序，group by、order by、rollup和窗口函数。
参数为sort_area_size
b.hash散列连接，
参数为hash_area_size
c.位图合并，
参数为bitmap_merge_area_size
d.位图创建，
参数为create_bitmap_area_size
e.批量装载操作使用的写缓存
（四）Session memory
保存了会话的变量，如登录信息及其他与会话相关的信息，共享服务器模式下，Session memory是共享的。
（五）自动PGA管理
设置PGA_AGGREGATE_TARGET为非0，则启用PGA自动管理，并忽略所有*_area_size的设置。如sort_area_size,hash_area_size等。
默认为启用PGA的自动管理，Oracle根据SGA的20%来动态调整PGA中专用与Work Area部分的内存大小，最小为10MB。
用于实例中各活动工作区（work area）的PGA总量，为PGA_AGGREGATE_TARGET减去其他组件分配的PGA内存。得到的结果，按照特定需求动态分配给对应的工作区。
1）设置PGA_AGGREGATE_TARGET大小的步骤
a.设置PGA_AGGREGATE_TARGET为SGA的20%，对于DSS系统，此值可能过低。
b.运行典型的负载，通过oracle收集的pga统计信息来调整PGA_AGGREGATE_TARGET的值。
c.根据oracle的pga建议调整PGA_AGGREGATE_TARGET大小。
2）禁用自动pga管理
为向后兼容，设置PGA_AGGREGATE_TARGET为0，即禁用pga的自动管理。可使用关联的*_area_size参数调整对应工作区的最大大小。
bitmap_merge_area_size
create_bitmap_area_size
hash_area_size
sort_area_size
Oracle的内存结构,除了SGA(System Global Area)之外,Oracle进程还使用下面三个全局区:
PGA: process global area
PGA: process global area
UGA: user global area
CGA: call global area
1. PGA与UGA
很多人都搞不清楚PGA和UGA两者之间的区别,实际上两者之间的区别跟一个进程和一个会话之间的区别是类似的.尽管说进程和会话之间一般都是一对一的关系,但实际上比这个更复杂.一个很明显的情况是MTS配置,会话往往会比进程多得多.在这种配置下,每一个进程会有一个PGA,每一个会话会有一个UGA. PGA所包含的信息跟会话是无任何关联的, 而UGA包含的信息是以特定的会话为基础的.
而从Oracle9i开始，Oracle引入了PGA的自动管理。不需要我们单独地设置大小了。
2. PGA
进程全局区(PGA)即可以理解为Process Global Area,也可以理解为Program Global Area.它的内存段是在进程私有区(Process Private Memory)而不是在共享区(Shared Memory). 每个Oracle的server process都有属于自己的PGA,它只包含了本进程的相关特定信息.PGA中的结构不需要由latches来保护,因为其它的进程是不能进入到这里面来访问的.
PGA包含两个主要区域:Fixed PGA和Variable PGA或称为PGA Heap.
Fixed PGA的作用跟Fixed SGA是类似的,都包含原子变量(不可分的),小的数据结构和指向Variable PGA的指针.
Variable PGA是一个堆.它的Chunks可以从Fixed Table X$KSMPP查看得到,这个表的结构跟前面有提到的X$KSMSP是相同的. PGA HEAP包含了一些有关Fixed Table的永久性内存,它跟某些参数的设置有依赖关系.这些参数包含DB_FILES,LOG_FILES,CONTROL_FILES.
Variable PGA是一个堆.它的Chunks可以从Fixed Table X$KSMPP查看得到,这个表的结构跟前面有提到的X$KSMSP是相同的. PGA HEAP包含了一些有关Fixed Table的永久性内存,它跟某些参数的设置有依赖关系.这些参数包含DB_FILES,LOG_FILES,CONTROL_FILES.
3. UGA
UGA(User Global Area)包含的是特定会话的信息。
UGA(User Global Area)包含的是特定会话的信息。
跟PGA一样,UGA也由两区组成:Fixed UGA和Variable UGA,也称为UGA HEAP. Fixed UGA包含了大约70个原子变量,小的数据结构和指向Variable UGA的指针.
UGA HEAP中的Chunks可以从它们自己的会话中通过查看表X$KSMUP获得相关信息,这个表的结构跟X$KSMSP是一样的.UGA HEAP包含了一些有关fixed tables的永久性内存段,跟一些参数的设置有依赖关系.这些参数有OPEN_CURSORS,OPEN_LINKS,和MAX_ENABLE_ROLES.
UGA在内存中的位置依赖于会话的配置方式.
如果会话连接的配置方式是专用服务器模式(DDS)即是一个会话对应一个进程,则UGA是放在PGA中的. 在PGA中, Fixed UGA是其中的一个Chunk, 而UGA HEAP是PGA的一个子堆(Subheap).
如果会话连接是配置为共享服务器模式(MTS), Fixed UGA是SHARED POOL或者Large pool中的一个Chunk, 而UGA HEAP则是SHARED POOL或者large pool中的子堆(Subheap).
4. CGA
跟其它的全局区不同,Call Global Area是短暂性存在的.它只有在函数调用期间存在,一般是在对实例的最低级别的调用时才需要CGA,比如:分析一个SQL语句，执行一个SQL语句，取出一个SELECT语句的输出
一个单独的CGA在递归调用时是需要的.在SQL语句的分析过程中,对数据字典信息的递归调用是需要的,因为要对SQL语句进行语法分析,还有在语句的优化期间要计算执行计划.执行PL/SQL块时在处理SQL语句的执行时也是需要递归调用的,在DML语句的执行时要处理触发器执行也是需要递归调用的.
不管UGA是放在PGA中还是在SGA中,CGA都是PGA的一个子堆(Subheap).这个事实的一个重要推论是在一个调用的期间会话必须是一个进程.对于在一个MTS的Oracle数据库进程应用开发时关于这一点的理解是很重要的.如果相应的调用较多,就得增加processes的数量以适应调用的增加.
没有CGA中的数据结构,CALLS是没法工作的. 而实际上跟一次CALL相关的数据结构一般都是放在UGA中,如SQL AREA,PL/SQL AREA, SORT AREA, hash area, bitmap area它们都必须在UGA中, 因为它们要在各CALLS之间要一直存在并且可用. 而CGA中所包含的数据结构是要在一次CALL结束后能够释放的. 例如CGA包含了关于递归调用的信息,直接I/O BUFFER等还有其它的一些临时性的数据结构.
5. 总结一下
PGA包括：
1）Fixed PGA
2）UGA (dedicated server)，当系统是shared server时，UGA位于shared pool或者large pool。
3）CGA
而UGA和CGA都属于Variable PGA(PGA heap)
UGA包括：
1）Fixed UGA
2）private SQL area
3）session memory
4）SQL Work Areas
private SQL area, session memory, SQL work ares都属于variable UGA(UGA heap).
6. private SQL area
可以分为两部分：
1）永久内存区域：存放相同SQL语句多次执行时都需要的一些游标信息，比如绑定变量等。这部分内存只有在游标被关闭时才释放。所以称为：永久内存区域。
2）运行时区域：处理SQL语句时的第一步要创建运行时区域，这里存放了当SQL运行时所使用的信息。对于DML语句，SQL执行完毕就释放该区域；对于select语句，当所有数据行返回给用户时释放。
7. session area
保存该session的一些参数，比如：修改的NLS参数，修改的优化器参数optimizer_mode，alter session命令所启用的跟踪信息，可以使用的roles，所打开的db links，真正使用的package等。
8. SQL work area
SQL工作区是UGA中最重要的部分，占UGA的大部分内存。主要是在排序sort(ordr by, group by)，hash-join时使用。SQL工作区的大小对性能的影响很大。
一般大数量的排序，是不太可能完全在SQL work area中完成的，因为没有那么大的内存，所以一般都会使用临时表空间，将排序的中间结果写入到temp表空间中(磁盘排序)。根据SQL work area的大小，使用临时表空间的次数可以是一次，也可能是多次。所以有三种情况：
1）optimal: 排序完全在内存中完成；
2）onepass: 排序完成，需要使用磁盘一次；
3）multipass: 排序的完成，需要使用磁盘多次；
optimal一般是不太现实的，我们一般尽量保证onepass，如果导致了multipass，则性能会下降很多。我们先来看看排序的过程：
排序算法采用的是堆排序；排序方式是分批排序，比如10000条记录排序，把它分成100次，每次对100条记录排序；当100条排序完成之后，写入到temp表空间；再对下100条排序，排序完之后在写入到temp表空间；最后对temp表空间的100个局部排序的队列进行merge操作。从而完成对10000条记录的排序。这样是比较理想的情况，因为只对磁盘读写一次，属于onepass。如果排序涉及到磁盘的多次读写，则属于multipass。一般我们尽量调整sort_area_size的大小，来避免multipass排序。
SQL work area可以分为下面几个部分：
排序区(sort_area_size)，hash区(hash_area_size)，bitmap区(create_bitmap_area_size,bitmap_merge_area_size)等，他们深刻的影响着排序、hash-join、bitmap等的性能。
9. PGA的自动管理
在Oracle9i之前，我们是通过设置sort_area_size, hash_area_size等参数来管理PGA。
SQL> show parameter area_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
bitmap_merge_area_size integer 1048576
create_bitmap_area_size integer 8388608
hash_area_size integer 131072
sort_area_size integer 65536
workarea_size_policy string AUTO
SQL> show parameter sort_area;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sort_area_retained_size integer 0
sort_area_size integer 65536
而从Oracle9i开始，Oracle引入了PGA的自动管理。不需要我们单独地设置大小了。
设置参数workarea_size_policy参数，可以在PGA自动(auto默认是auto)和PGA手动管理(manual)之间进行选择。
然后通过初始化参数pga_aggregate_size来设置PGA的内存总和(相当于SGA的sga_max_size)。注意在Oracle9i时，PGA自动管理只对dedicate server有效，对shared server无效，但是在Oracle10g时，PGA自动管理都有效。
10. PGA大小设置：
对于OLTP系统，典型的PGA内存为：
PGA_AGGREGATE_SIZE = (total_memory * 80%) * 20%
对于OLAP系统，由于会运行一些很大的查询：
PGA_AGGREATE_SIZE = (total_memoery * 80%) * 50%
80%是指，将机器总内存的80%分给Oracle使用。然后在将80%中的20%给PGA。
我们也可以使用PGA相关的一些视图来调整pga_aggregate_size的大小：
v$pgastat, v$pga_target_advice, v$pga_target_advice_histogram
使用v$pgastat查看当前PGA的统计信息(类似于v$sgastat和SGA)：
SQL> select * from v$pgastat;
NAME VALUE UNIT
---------------------------------------- ---------- ------------
aggregate PGA target parameter 71303168 bytes
aggregate PGA auto target 27297792 bytes
global memory bound 14260224 bytes
total PGA inuse 40966144 bytes
total PGA allocated 88008704 bytes
maximum PGA allocated 125034496 bytes
total freeable PGA memory 4259840 bytes
process count 23
max processes count 40
PGA memory freed back to OS 492634112 bytes
total PGA used for auto workareas 0 bytes
maximum PGA used for auto workareas 6105088 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 0 bytes
over allocation count 0
bytes processed 6275959808 bytes
extra bytes read/written 0 bytes
cache hit percentage 100 percent
recompute count (total) 33814
19 rows selected.
maximum PGA allocated：PGA曾今达到的最大值
over allocation count: Over allocating PGA memory can happen if the value ofPGA_AGGREGATE_TARGET is too small. When this happens, the Oracle Database cannot honor the value of PGA_AGGREGATE_TARGET and extra PGA memory needs to be allocated. If over allocation occurs, then increase the value of PGA_AGGREGATE_TARGET using the information provided by the V$PGA_TARGET_ADVICE view.
extra bytes read/written: Number of bytes processed during extra passes of the input data, cumulated since instance startup. When a work area cannot run optimal, one or more of these extra passes is performed. (即磁盘排序的读写磁盘的字节数)
使用v$pga_target_advice来估测PGA的大小，即PGA的建议值(类似于v$sga_target_advice和SGA)：
V$PGA_TARGET_ADVICE predicts how the cache hit percentage and over allocation count statistics displayed by the V$PGASTAT performance view would be impacted if the value of the PGA_AGGREGATE_TARGET parameter is changed. The prediction is performed for various values of the PGA_AGGREGATE_TARGET parameter, selected around its current value. The advice statistic is generated by simulating the past workload run by the instance.
SQL> select round(pga_target_for_estimate/(1024*1024)) target_size_M,
使用v$pga_target_advice来估测PGA的大小，即PGA的建议值(类似于v$sga_target_advice和SGA)：
V$PGA_TARGET_ADVICE predicts how the cache hit percentage and over allocation count statistics displayed by the V$PGASTAT performance view would be impacted if the value of the PGA_AGGREGATE_TARGET parameter is changed. The prediction is performed for various values of the PGA_AGGREGATE_TARGET parameter, selected around its current value. The advice statistic is generated by simulating the past workload run by the instance.
SQL> select round(pga_target_for_estimate/(1024*1024)) target_size_M,
2 estd_pga_cache_hit_percentage est_cache_hit_percentage,
3 round(estd_extra_bytes_rw/(1024*1024)) est_extra_read_write_M,
4 estd_overalloc_count est_over_alloc from v$pga_target_advice;
TARGET_SIZE_M EST_CACHE_HIT_PERCENTAGE EST_EXTRA_READ_WRITE_M EST_OVER_ALLOC
------------------ --------------------------- ---------------------- --------------
17 97 186 31
34 97 186 31
51 100 0 2
68 100 0 0
82 100 0 0
95 100 0 0
109 100 0 0
122 100 0 0
136 100 0 0
204 100 0 0
272 100 0 0
408 100 0 0
544 100 0 0
13 rows selected.
使用v$pga_target_advice_histogram来估测PGA的大小(类似于v$sga_target_advice和SGA)：
V$PGA_TARGET_ADVICE_HISTOGRAM predicts how statistics displayed by the V$SQL_WORKAREA_HISTOGRAM dynamic view would be impacted if the value of thePGA_AGGREGATE_TARGET parameter is changed. This prediction is performed for various values of the PGA_AGGREGATE_TARGET parameter, selected around its current value. The advice statistic is generated by simulating the past workload run by the instance.
该视图可以通过对不同工作区大小的采样评估提供统计信息共分析使用SQL在工作区中以3种方式执行：
Optimal（优化方式）：指所有处理可以在内存中完成；Onepass：大部分操作可以在内存中完成，但是需要使用到磁盘排序；Multipass：大量操作需要产生磁盘交互，性能极差。About Me
...............................................................................................................................
● 本文整理自网络
● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/
● 本文博客园地址：http://www.cnblogs.com/lhrbest
● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/
● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/
● QQ群：230161599 微信群：私聊
● 联系我请加QQ好友(646634621)，注明添加缘由
● 于 2017-07-01 09:00 ~ 2017-07-31 22:00 在魔都完成
● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
...............................................................................................................................
拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。
新闻名称：Oracle体系结构之内存结构（SGA、PGA）
本文地址：http://cqcxhl.cn/article/posghp.html