pga详解

一、pga内存结构：
1.fixed pga:这部分包含一些小的固定尺寸的变量，以及指向变化PGA部分的指针。
2.variable pga:这部分是按照堆（Heap）来进行组织的，所以这部分也叫做PGA堆。
               可以从X$KSMPP视图中看到有关PGA堆的分布信息。
  2.1 固定表永久内存
  2.2 uga
      2.2.1 固定uga部分:这部分包含一些小的固定尺寸的变量，以及指向变化UGA部分的指针。
      2.2.2 变化uga部分:这部分也是按照堆来进行组织的，可以从X$KSMUP视图中看到有关UGA堆的分布情况。
        UGA堆的分布与OPEN_CURSORS、 OPEN_LINKS等参数有关系。所谓的游标（cursor）就是放在这里的。
            2.2.2.1 私有sql区域:这部分区域包含绑定变量信息以及运行时的内存结构等数据。
                                每一个发出SQL语句的session都有自己的私有SQL区域
                    2.2.1.1 永久内存区域:这里存放了相同SQL语句多次执行时都需要的一些游标信息，
                             比如绑定变量信息、数据类型转换信息等。
                     这部分内存只有在游标被关闭时才会被释放。
                2.2.1.2 运行时内存区域(sql work area):
                这里存放了当SQL语句运行时所使用的一些信息。这部分区域的大小尺寸依赖于
                所要执行的SQL语句的类型（sort或hash-join等）和复杂度以及所要处理的数
                据行的行数以及行的大小。在处理SQL语句时的第一步就是要创建运行时区域，
                对于DML（INSERT、UPDATE、DELETE）语句来说，SQL语句执行完毕就释放该区域；
                而对于查询语句（SELECT）来说，则是在所有数据行都被获取并传递给用户以后被释放，
                或者该查询被取消以后也会被释放。
            2.2.2.2 session相关的信息
            正在使用的包（package）的状态信息。使用alter session这样的命令所启用的跟踪信息、
            或者所修改的session级别的优化器参数（optimizer_mode）、排序参数（sort_area_size等）
            修改的NLS参数等。所打开的dblinks。可使用的角色（roles）等。
  3.3 cga
      CGA也是一块内存区域，但它是动态的，随着调用（call）的开始而创建，在调用过程中一直存在，直到调用结束时被释放。
      它存放的是在调用过程中所需要的数据。我们知道，调用主要包括解析（parse）调用、执行（executive）调用、
      获取（fetch）调用以及递归SQL调用和 PL/SQL调用。从调用的种类可以看出，实际上在调用过程中所需要的数据，
      比如SQL AREA，PL/SQL AREA和SORT AREA基本都是放在UGA中的，因为这些数据在各个调用之间必须一直存在并可用。
      而在CGA中只存放了在调用过程中临时需要的数据，比如直接I/O缓存（Direct I/O Buffer）以及堆栈空间等数据结构。
      因此，没有CGA中的数据结构，调用是无法完成的。注意，CGA不象UGA可以位于SGA中（以共享服务器模式连接），
      CGA一定是位于PGA中的。如果当前进程正在运行，则每个PGA中只有一个CGA。如果当前进程没有运行，则该进程的PGA中就没有CGA。

二、pga设置
    我们一旦设置了pga_aggregate_target以后，所有的*_area_size就将被忽略。那么，我们该如何来设置该参数的值呢？
    这依赖于数据库的用途，如果数据库为OLTP（联机事务处理）应用的，则其应用一般都是小的短的进程，所需要的PGA也相应较少，
    所以该值该值通常为总共分配给 oracle实例的20%，另外的80%则给了SGA；如果数据库为OLAP（DSS）（数据仓库或决策分析）应用的，
    则其应用一般都是很大的，运行时间很长的进程，因此需要的PGA就多。所以通常为PGA分配50%的内存。而如果数据库为混合类型的，
    则情况比较复杂，一般会先分配40%的初始值，而后随着数据库的应用，而不断对PGA进行监控，并进行相应的调整。
    比如，对于8GB物理内存的数据库服务器来说，按照oracle推荐的，分配给 oracle实例的内存为物理内存的80%。
    那么对于OLTP应用来说，pga_aggregate_target的值大约就是1310MB ((8192 MB× 80%)×20%)。而对于OLAP来说，
    则该值大约就是3276MB (8192MB×80%)×50%)。当然，这里所说的都是对于一个新的数据库来说，初始设置的值。
    这些值并不一定正确，可能设置过大，也可能设置过小。必须随着系统的不断运行，DBA需要不断监控，从而对其进行调整。

三、pga监控
--监控SQL语句所使用的SQL工作区
SELECT
b.sql_text,
a.operation_type,
a.policy,
a.last_memory_used/(1024*1024) as "Used MB" ,
a.estimated_optimal_size/(1024*1024) as "Est Opt MB",
a.estimated_onepass_size/(1024*1024) as "Est OnePass MB",
a.last_execution,
a.last_tempseg_size
FROM v$sql_workarea a,v$sql b
WHERE a.hash_value = b.hash_value
  and a.hash_value = &hashvalue;

--监控session所使用的PGA和UGA的大小
select a.name, b.value
from v$statname a, v$sesstat b
where a.statistic# = b.statistic#
and b.sid = &sid
and a.name like '%ga %'
order by a.name;

--监控进程所使用的PGA的大小
SELECT
   a.pga_used_mem "PGA Used",
   a.pga_alloc_mem "PGA Alloc",
   a.pga_max_mem "PGA Max"
  FROM v$process a,v$session b
where a.addr = b.paddr
and b.sid= &sid;

--其它监控
我们监控PGA的视图除了上面介绍到的v$sql_workarea_active、v$sesstat、v$sql_workarea以及 v$process以外，
还有v$sql_workarea_histogram、v$pgastat以及v$sysstat。
v$sql_workarea_histogram记录了每个范围的SQL工作区内所执行的optimal、onepass、multipass的次

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;

select
optimal_count "Optimal",
round(optimal_count * 100 / total,2) "Optimal %",
onepass_count "OnePass",
round(onepass_count * 100 / total,2) "Onepass %",
multipass_count "MultiPass",
round(multipass_count * 100 / total,2) "Multipass %"
from (
        select
           sum(total_executions) total,
           sum(optimal_executions) optimal_count,
           sum (onepass_executions) onepass_count,
           sum (multipasses_executions) multipass_count
         from v$sql_workarea_histogram
         where total_executions <> 0)




--v$pgastat说明
select * from v$pgastat;
NAME                                          VALUE UNIT
---------------------------------------- ---------- ------------
aggregate PGA target parameter             62914560 bytes
aggregate PGA auto target                  51360768 bytes
global memory bound                       104857600 bytes
total PGA inuse                             5846016 bytes
total PGA allocated                         8386560 bytes
maximum PGA allocated                      66910208 bytes
total freeable PGA memory                         0 bytes
PGA memory freed back to OS                       0 bytes
total PGA used for auto workareas                 0 bytes
maximum PGA used for auto workareas        51167232 bytes
total PGA used for manual workareas               0 bytes
maximum PGA used for manual workareas             0 bytes
over allocation count                             0
bytes processed                           142055424 bytes
extra bytes read/written                  138369024 bytes
cache hit percentage                          50.65 percent
从结果可以看出，第一行表示 pga_aggregate_target设置为60M。PGA的一部分被用于无法动态调整的部分，
比如UGA中的“session相关的信息”等。而 PGA内存的剩下部分则是可以动态调整的，
由“aggregate PGA auto target”说明。我们来看第二行的值，就表示可以动态调整的内存数量，
该值不能与pga_aggregate_target设置的值差太多。如果该值太小，
则oracle没有足够的内存空间来动态调整session的内存工作区。
其中的global memory bound表示一个工作区的最大尺寸，
并且oracle推荐只要该统计值低于1M时，就应该增加pga_aggregate_target的值。
另外，9i还提供了两个有用的指标：over allocation count和cache hit percentage。
如果在使用SQL工作区过程中，oracle认为pga_aggregate_target过小，则它自己会去多分配需要的内存。
则多分配的次数就累加在over allocation count指标里。该值越小越好，
最好为0。cache hit percentage则表示完全在内存里完成的操作的字节数与所有完成的操作(包括optimal、onepass、multipass)的字节数的比率。
如果所有的操作都是optimal类，则该值为100%。
最后，我们可以查询v$sysstat视图，获得optimal、onepass、 multipass执行的总次数：
SQL>  select * from v$sysstat where name like 'workarea executions%';
STATISTIC# NAME                                          CLASS      VALUE
---------- ---------------------------------------- ---------- ----------
       230 workarea executions - optimal                    64        360
       231 workarea executions - onepass                    64          2
       232 workarea executions - multipass                  64          0
我们可以计算optimal次数占总次数的比率，比如上例中，360/(360+2+0)=99.45%，
该比率越大越好，如果发现onepass和 multipass较多，
则需要增加pga_aggregate_target，或者调整SQL语句以使用更少的PGA区。

--查找sql消耗pga大的sql
那么我们如何找到需要调整以使用更少的PGA的SQL语句呢？我们可以将v$sql_workarea中的记录按照 estimated_optimal_size字段由大到小的排序，
选出排在前几位的hash值，同时还可以选出last_execution值为“n PASSES”（这里的n大于或等于2）的hash值，
将这些hash值与v$sql关联后找出相应的SQL语句，进行调整，以便使其使用更少的PGA。

--根据建议设置pga值
select * from v$pga_target_advice;