PGA介绍

PGA参数：
1、查看workarea_size_policy参数设置
show parameter workarea_size_policy
workarea_size_policy为auto表明PGA为自动管理

2、v$pgastat视图：
--查看pga命中率
select * from v$pgastat where name='cache hit percentage';
--v$pgastat各记录含义
select name,value from v$pgastat;
aggregate PGA target parameter --这个值等于参数PGA_AGGREGATE_TARGET的值，如果此值为0，表示禁用了PGA自动管理。
aggregate PGA auto target  --表示PGA还能提供多少内存给自动运行模式，通常这个值接近pga_aggregate_target-total pga inuse.
global memory bound  --工作区执行的最大内存，如果这个值小于1M，马上增加PGA大小
total PGA inuse --正被耗用的pga，可以从v$process的SUM(pga_used_mem)字段中获取
total PGA allocated --当前实例已分配的PGA内存总量，可能超过PGA_AGGREGATE_TARGET的限定值
maximum PGA allocated  --pga曾经扩张到的最大值
total freeable PGA memory --可释放的pga
process count --当前有几个process
max processes count  --允许的最大进程数
PGA memory freed back to OS
total PGA used for auto workareas  --auto模式下占用的workara size 大小
maximum PGA used for auto workareas --auto模式下占用的workara size最大 大小
total PGA used for manual workareas --manual模式下占用的workara size 大小
maximum PGA used for manual workareas --manual模式下占用的workara size最大 大小
over allocation count  --使用量超过pga大小的次数
bytes processed --pga使用的字节
extra bytes read/written  --向临时段写的字节
cache hit percentage --命中率为100%，如果太小增加PGA
recompute count (total)

3、v$pga_target_advice视图
SQL> show parameter pga_aggregate_target;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 191M

--假如我们需要调整pga_aggregate_target时，到底我们应该设置多大呢？oracle为了帮助我们确定这个参数的值，引入了一个新的视图：
--v$pga_target_advice。 为了使用该视图，需要将初始化参数statistics_level设置为typical（缺省值）或all。
SELECT a.pga_target_for_estimate / 1024 / 1024, --调整后的pga大小
       a.pga_target_factor,--当前pga大小
       a.estd_pga_cache_hit_percentage,--pga中cache命中的百分比
       a.estd_overalloc_count --PGA的过载量，最好为0
  FROM v$pga_target_advice a;
A.PGA_TARGET_FOR_ESTIMATE/1024 PGA_TARGET_FACTOR ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
------------------------------ ----------------- ----------------------------- --------------------
                        23.875             0.125                            62                 1000
                         47.75              0.25                            83                  141
                          95.5               0.5                            89                   55
                        143.25              0.75                            89                   44
                           191                 1                            92                    1
                  229.19921875               1.2                            92                    0
                267.3994140625               1.4                            92                    0
                 305.599609375               1.6                            92                    0
                343.7998046875               1.8                            92                    0
                           382                 2                            93                    0
                           573                 3                            93                    0
                           764                 4                            93                    0
                          1146                 6                            93                    0
                          1528                 8                            93                    0
pga_target_factor为1表示当前的pga大小，1其他都是以这个进行相比的比值。

/*
同时，我们知道v$tempstat里记录了读写临时表空间的数据块数量以及所花费的时间。这样，我们就可以结合v$pga_target_advice和v$tempstat
这两个视图。可以得到每一种估计PGA值下的响应时间大致是多少，从而可以换一个角度来显示PGA的建议值：
*/
SELECT 'PGA Aggregate Target' component,
       ROUND(pga_target_for_estimate / 1024 / 1024) target_size,
       estd_pga_cache_hit_percentage cache_hit_ratio,
       ROUND(((estd_extra_bytes_rw /
             DECODE((b.blocksize * i.avg_blocks_per_io),
                      0,
                      1,
                      (b.blocksize * i.avg_blocks_per_io))) * i.iotime) / 100) "response_time(sec)"
  FROM v$pga_target_advice,
       (SELECT AVG((readtim + writetim) /
                   DECODE((phyrds + phywrts), 0, 1, (phyrds + phywrts))) iotime,
               AVG((phyblkrd + phyblkwrt) /
                   DECODE((phyrds + phywrts), 0, 1, (phyrds + phywrts))) avg_blocks_per_io
          FROM v$tempstat) i,
       (SELECT value blocksize FROM v$parameter WHERE NAME = 'db_block_size') b; --b表示查询数据库当前块大小

4、v$sql_workarea_histogram视图：
完全在内存中排序的为最佳排序（optimal_executions）；若内存不够，直接将部分数据写出到磁盘的称为一遍排序（onepass_executions）

SELECT a.low_optimal_size,--最佳执行的pga最小大小
       a.high_optimal_size,--最佳执行的pga最大大小
       a.optimal_executions,--最佳执行的次数
       a.onepass_executions,--遍历一次的次数
       a.multipasses_executions,--遍历多次的次数
       a.total_executions --总的执行次数
  FROM v$sql_workarea_histogram a
 WHERE total_executions <> 0
 ORDER BY low_optimal_size;

/*optimal、onepass、multipass执行次数的百分比，optimal所占的百分比越高越好，如果onepass和 multipass占的百分比很高，
就不需要增加pga_aggregate_target的值了，或者调整SQL语句以使用更少的PGA区*/
SELECT optimal_count,
       ROUND(optimal_count / total_count * 100, 2) "optimal_count%",
       onepass_count,
       ROUND(onepass_count / total_count * 100, 2) "onepass_count%",
       multipass_count,
       ROUND(multipass_count / total_count * 100, 2) "multipass_count%"
  FROM (SELECT SUM(a.total_executions) total_count,
               SUM(a.optimal_executions) optimal_count,
               SUM(a.onepass_executions) onepass_count,
               SUM(a.multipasses_executions) multipass_count
          FROM v$sql_workarea_histogram a
         WHERE a.total_executions <> 0)

5、v$process_memory和v$process视图：         
--查看每个进程每种类型（SQL、PL/SQL、OLAP和JAVA）分配的pga大小
SELECT * FROM v$process_memory;
--查看每个进程pga的具体分配情况（已使用的，分配值，空闲值，最大值）
SELECT p.program,--进程
       p.pga_used_mem,--使用的内存
       p.pga_alloc_mem,--分配的内存
       p.pga_freeable_mem,--空闲的内存
       p.pga_max_mem   --最大可有的内存
  FROM v$process p;

SELECT SUM(pga_used_mem),SUM(pga_alloc_mem),SUM(pga_max_mem) FROM v$process;

--查看工作区执行排序情况
SELECT name,
       cnt,
       DECODE(total, 0, 0, ROUND(cnt / total * 100, 2)) percentage
  FROM (SELECT name, value cnt, SUM(value) OVER() total
          FROM v$sysstat
         WHERE name like 'workarea exec%');

6、v$sql_workarea_active视图：显示当前工作区的活动情况
SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
       operation_type operation,
       trunc(expected_size/1024) esize,--PGA管理器估计的本次操作需要的总内存数量
       trunc(actual_mem_used/1024) mem,--目前正在使用的内存数量
       trunc(max_mem_used/1024) "max mem",--该SESSION曾经使用的最大PGA内存数量
       number_passes pass,--0代表内存排序，1代表1-pass排序，M代表M-PASS排序
       trunc(tempseg_size/1024) tsize --转储到临时表空间的大小
  FROM v$sql_workarea_active
 ORDER BY 1,2;

7、查看分配给各个会话的内存量
SELECT s.value,s.sid,a.username FROM v$sesstat s, v$statname n, v$session a
 WHERE s.statistic# = n.statistic#
   AND n.name = 'session pga memory'
   AND s.sid = a.sid

8、pga大致分配规则
For OLTP 系统
 PGA_AGGREGATE_TARGET = (<Total Physical Memory > * 80%) * 20%
For DSS 系统
 PGA_AGGREGATE_TARGET = (<Total Physical Memory > * 80%) * 50%
 
--查看OLAP
cmd 用sqlplus "/ as sysdba"登陆
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options