Oracle内存结构之PGA优化原理渗透解析
程序全局区(Program Global Area)简称PGA,是oracle实例的一部分,主要用来存储oracle服务器进程和后台进程的数据和控制信息。与SGA不同,它不是被几个进程共享,而是专属于某一个进程。PGA在进程创建时被分配,进程终止时释放,只有进程本身才能访问自己的PGA。
1.PGA的组成
根据服务器的工作模式,PGA有着不同的组织结构。一般情况下,PGA由私有SQL区和会话区两个部分组成。
通常,在SQL语句中还有一部分信息,如会话参数、绑定变量等,这些信息属于执行该SQL语句的用户所私有,这些私有信息一般不能被其他用户所共享,因此,oracle为这些信息设置了各自的内存区域,这些存储区称为私有SQL区。私有SQL区可以用来保存SQL语句运行中的绑定变量和数据结构等信息。
在专用服务器模式下,私有SQL区放在PGA中。在专用服务器模式下,每个用户都通过一个属于字的的服务器进程来访问数据库,该用户提交的SQL语句中的变量和数据结构等信息保存在自己的服务器进程中,这块内存区域就叫私有SQL区,它存在于服务器进程中。每一个用户都有自己的服务器进程,也都有自己的私有SQL区。对于不同用户执行同一条SQL语句,其绑定变量等信息放在私有SQL区中,共享信息放在共享SQL区中,两者之和就构成了一条SQL完整的执行信息。
在共享服务器模式下,私有SQL区放在共享池的库高速缓存中。此模式下一个服务器进程对应多个用户,因此SQL私有信息就不能放在服务器进程中,因此,oracle将私有sql区放在了共享池的库高速缓存中。
根据私有SQL区中信息的生命周期,每个私有SQL区又可分为两部分:
持久区:保存会话过程中使用的绑定变量等信息,其内容在会话过程中一直保存在其中,只有会话结束,该区才被释放
动态区:用于SQL语句的执行和中间结果的暂存,其内容是不断变化的,一条SQL语句执行完后,该区就会被释放
2.SQL工作区
SQL工作区设置在动态区中,是Oracle专门为执行复杂SQL语句而设置的大容量的 内存区域。对于一些复杂的SQL查询操作(如包含有排序或连接等字句的查询等),在执行期间需要较大的存储区域保存中间结果,这些中间结果只在SQL语句执行期间有用,SQL语句执行完毕后就可以释放。对于这类操作,Oracle专门在私有SQL区的动态区域中设置了这样的工作区,这块工作区叫SQL工作区。
在SQL工作区中可以执行这些需要使用大量存储空间的查询操作:
排序(包含有order by、group by等子句的查询)
连接(连接查询等)
BITMAP MERGE(位图合并)
BITMAP CREATE集合操作(使用UNION,INTERSECT、MINUS等运算符的查询)
由于SQL工作区设置在内存中,在内存中执行排序、连接等操作具有较快运算速度,使用SQL工作区可以有效的提高oracle的性能。但是,如果SQL工作区放不下中间结果,oracle将把这些数据分成许多小块,一部分放在SQL工作区中,一部分放在磁盘中,通过内存和磁盘之间的数据交换完成SQL语句的执行。这种情况,大量的I/O操作将会影响SQL语句的执行速度。
在ORACEL应用系统中,复杂的SQL查询是非常多的,如何调整SQL工作区的大小对于DBA来说非常重要。在oracle 9i以前,SQL工作区的管理非常复杂,必须使用4个初始化参数进行配置,这四个参数对应4类查询操作所使用的4个工作区。
SORT_AREA_SIZE:排序区的初始化参数
HASH_AREA_SIZE:与并行执行操作和DML或DDL语句相关,指定要用于散列连接的最大内存量
BITMAP_MERGE_AREA_SIZE:对索引进行扫描而检索得到的位图要使用的内存量
CREATE_BITMAP_AREA_SIZE:为创建位图索引而分配的内存量
在配置SQL工作区时,DBA必须对于4类查询所使用的内存大小有一个准确的估算,但这对于DBA来说是比较困难的。为此,Oracle 9i改进了管理方式,增加了两个新参数,简化了SQL工作区的管理:
PGA_AGGREGATE_TARGET:该值指定所有PGA总和。如该值设置为64M时,Oracle自动分配上述4个工作区的大小,使它们的和等于64M。该值的取值范围为10M到4095G,单位可以为KB、MB或GB。如果设置了该值,
WORKAREA_SIZE_POLICY:该参数用于指定SQL工作区管理方式。取值范围为AUTO或MANUAL。当该值为AUTO时,SQL工作区为自动管理方式,由Oracle根据PGA_AGGREGATE_TARGET的大小,自动调整上述四个工作区的大小。如果该值设置为MANUAL,则需要DBA手工设置上述4个参数的大小。
查询PGA大小的方法:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL> show parameter pga;
NAME
TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big
integer
90M
SQL> show parameter workarea_size_policy;
NAME
TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AUTO
|
如果想了解排序区的使用情况,可以查询动态性能视图V$SYSSTAT
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
select
name
,value
from
v$sysstat
where
name
like
'%sort%'
;
NAME
VALUE
---------------------------------------------------------------- ----------
sorts (memory) 2928678
sorts (disk) 0
sorts (
rows
) 20856857
|
sorts (memory):表示内存中排序量
sorts (disk):表示磁盘中的排序量