1.设置总的work area的大小
alter system set pga_aggregate_target =100M
2.自动化管理
workarea_size_policy = auto
3.相关视图
PGA work area
v$sql_workarea_histogram ---查看以前pga的历史工作状态(optimal次数,one次数,multi次数)
v$pgastat
v$sql_workarea_active ----当前工作的排序情况
v$sql_workarea ------v$sql_workarea_active的合计
v$tempseg_usage
v$pga_target_active
v$pga_target_advice_histogram
v$pgastat ----over allocation count-----当排序要求内存超过PGA设置的内存一次,此项就加1
-----total bytes processed --- 总的排序数
------extra bytes read/written ----读出或写入磁盘的数
select round(pga_target_for_estimate/1024/1024) as target_mb,
estd_pga_cache_hit_percentage as hit_ratio,
estd_overalloc_count
from v$pga_target_advice
order by target_mb;
v$sysstat
v$sort_usage
v$sort_segment