oracle 调优PGA内存

调优PGA内存
每个服务于客户机的服务器进程都分配有一个私有内存区--PGA,大多数PGA专门用于内存密集型的任务，如group by, order by, rollup及散列联结等。PGA区是Oracle在启动一个服务器进程时创建的非共享内存区，在相应会话结束时自动释放。

诸如内存排序和建立散列表这样的操作需要专门的工作区。分配给PGA的内存区决定了这些用于专门任务(如排序)的工作区的尺寸，从而决定了系统 完成这些任务的速度。

1.自动PGA内存管理
从DBA的角度来看，PGA内存分配的管理很容易。可设置几个基本的参数，让Oracle自动管理各个工作区的内存分配。在Oracle能自动管理PGA前要做儿件事情。需要用PGA_AGGREGATE_TARGET参数设置内存限额，用V$PGA_TARGET_ADVICE视图调优目标值。

•使用 PGA_AGGREGATE_TARGET 参数
init.ora文件中的PGA_AGGREGATE_TARGET参数设置分配给PGA的内存分配的最大限额。Oracle给出了以下设置PGA_AGGREGATE_TARGET参数的准则。
对于OLTP数据库，目标应该是分配给Oracle的总内存的16%-20%。
对于DSS数据库，目标应该是分配给Oracle的总内存的40%-70%。

上述准则仅仅是准则。确定PGA_AGGREGATE_TARGET参数的理想尺寸的最好办法是使用V$PGA_TARGET_ADVICE或V$PGASTAT视图。

•使用 V$PGA_TARGET_ADVICE 视图
设置了PGA内存区的初始分配后，可使用V$PGA_TARGET_ADVICE 视图来调优目标值。Oracle用模拟各种PGA目标水平的不同负荷的结果来填充此位图。可按如下方式査询此视图：
select round(pga_target_for_estimate / 1024 / 1024) target_mb,
estd_pga_cache_hit_percentage cache_hit_perc,
estd_overalloc_count
from v$pga_target_advice;

使用来自V$PGA_TARGET_ADVICE视图的估算，可设置最佳水平的PGA内存。

2.设置PGA_AGGREGATE_TARGET参数值
请记住，通过设置PGA_AGGREGATE_TARGET参数提供的内存将决定数据库中排序和散列操作的效果。如果有大量的用户执行重载的排序或散列操作，则必须将PGA_AGGREGATE_TARGET参数设置为较高的值。例如，在设置SGA_TARGET为2GB时，实例启动后，将从总的OS内存中取出2GB。但是，PGA_AGGREGATE,TARGET设置的值仅仅是一个指标。在实例启动时，Oracle并未获得为PGA_AGGREGATE_TARGET指定的所有内存。PGA_AGGREGATE_TARGET参数的值仅作为实例可分配给所有组合会话的总的私有内存或工作区内存的上限。

执行排序的理想方法是在内存中完成整个作业。Oracle完全在内存中执行的排序作业称为最佳排 序(optimal sort)。如果设置PGA_AGGREGATE_TARGET过低，某些排序数据将直接写到磁盘(临时表空间), 因为此排序作业太大，内存放不下。

如果仅有部分排序作业溢出到磁盘，称为1遍排序(1-pass sort).如果实例在磁盘上而不是内存中执行大多数排序工作，则响应时间将很长。幸好，只要有足够的内存可用，可以监控和避免由于设置PGA内存(PGA_AGGREGATE_TARGET)过低而引起的问题。

可用以下的査询检査数据库中pga的使用情况。value列以字节显示当前分配给各个用户的内存
select s.value, s.sid, a.username
from v$sesstat s, v$statname n, v$session a
where n.statistic# = s.statistic#
and n.name = 'session pga memory'
and s.sid = a.sid
order by s.value;

衡量PGA_TARGET参数是否高效的一个重要指标是PGA的“命中率”，它显示在下面査询的最后一行, 此査询使用了V$PGASTAT视图：
SELECT * FROM V$PGASTAT;

在这个例子中，高速缓存命中率(PGA)大于85%,对于olTP或数据仓库应用，这己经足够好了。事实上，对于一个大型数据仓库型数据库，有时不得不满足于更低的PGA高速缓存命中率。

査看PGA效能的另一方法是使用下面的査询，它使用V$SQL_WORKAREA_HISTOGRAM视图。此视图包含以最佳、1遍、多遍内存尺寸实现的工作区数目的信息。工作区被分成组，其最优需求从0KB-1KB,从1KB-2KB,从2KB-4KB,等等。代码清单20-7显示使用V$SQL_WORKAREA_HISTOGRAM视图的査询结果.

使用V$SQL_WORKAREA_HISTOGRAM视图
SELECT low_optimal_size / 1024 "Low(K)",
(high_optimal_size + 1) / 1024 "High(K)",
optimal_executions "Optimal",
onepass_executions "1-Pass",
multipasses_executions ">1 Pass"
FROM v$sql_workarea_histogram
WHERE total_executions <> 0;

此实例中大多数排序是都使用最佳方法来执行，只有几个排序使用1遍方法。这就是前面例子中会有85%的PGA命中率的原因。下面给出一个存在问题的实例，正如所示，它有大量1遍和多遍(>1Pass)排序操作。立刻就会有大量顾客抱怨数据库运行速度太慢。

可以看到，这个例子中有大量的多遍排序，可以肯定，高速缓存命中率一定很低，大约在70%左右。幸好，加速实例所需做的只是以下面的方式增加PGA_AGGREGATE_TARGET参数的值：
ALTER SYSTEM SET pga_aggregate_target=500000000;

新V$PROCESS_MEMORY视图允许査看每个Oracle进程的动态PGA内存使用情况，并按每种类别(如Java、PL/SQL、OLAP和SQL等)的进程显示PGA的使用情况。下面是对该视图的一个简单查询：
select pid, category, allocated, used from v$process_memory;

还可以使用V$PROCESS视图来监控各个进程的PGA的使用情况。如果服务器上的内存用完，看看是否能释放其他用户的某些PGA内存。下面给出一个查询，它显示当前连接到实例的每个进程分配的、使用的、可用的PGA内存：
select program, pga_used_mem, pga_alloc_mem, pga_freeable_mem,pga_max_mem from v$process;

可使用下面的SQL语句来快速估计自Oracle实例启动以来，使用最佳、1遍、多遍PGA内存尺寸的工作区比例：
set line 122
select name profile,
cnt count,
decode(total, 0, 0, round(cnt * 100 / total)) percentage
from (select name, value cnt, (sum(value) over()) total
from v$sysstat
where name like 'workarea exec%');

在这个例了中，最佳执行的PGA高速缓存命中率为100%,当然这是很出色的。这里介绍的PGA内存调优相对来说比较新颖，以往，Oracle DBA的主要精力是放在SGA内存组件的调优上的。建议负责要求重载散列和排序的应用程序的DBA密切注意PGA的性能。调优PGA很容易，很好地调优PGA能极大地提高性能。