optimizing sort operations

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