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
