有以下几个视图可以监控PGA的统计情况：

V$PGASTAT

s V$PROCESS

s V$SQL_WORKAREA_HISTOGRAM

s V$SQL_WORKAREA_ACTIVE

s V$SQL_WORKAREA

1.V$PGASTAT

SQL> select * from v$pgastat;
 
NAME                                                    VALUE UNIT
-------------------------------------------------- ---------- ------------
aggregate PGA target parameter                       31457280 bytes
aggregate PGA auto target                            15335424 bytes
global memory bound                                   1572864 bytes
total PGA inuse                                      14416896 bytes
total PGA allocated                                  32669696 bytes
maximum PGA allocated                                34373632 bytes
total freeable PGA memory                                   0 bytes
PGA memory freed back to OS                           2424832 bytes
total PGA used for auto workareas                           0 bytes
maximum PGA used for auto workareas                   1666048 bytes
total PGA used for manual workareas                         0 bytes
 
NAME                                                    VALUE UNIT
-------------------------------------------------- ---------- ------------
maximum PGA used for manual workareas                       0 bytes
over allocation count                                       0
bytes processed                                     109182976 bytes
extra bytes read/written                            416931840 bytes
cache hit percentage                                    20.75 percent
 
16 rows selected.

2. V$SQL_WORKAREA_HISTOGRAM

SQL> SELECT LOW_OPTIMAL_SIZE/1024 low_kb,      
(HIGH_OPTIMAL_SIZE+1)/1024 high_kb,      
OPTIMAL_EXECUTIONS, ONEPASS_EXECUTIONS, MULTIPASSES_EXECUTIONS 
FROM V$SQL_WORKAREA_HISTOGRAM WHERE TOTAL_EXECUTIONS != 0;  2    3    4 
 
    LOW_KB    HIGH_KB OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS MULTIPASSES_EXECUTIONS
---------- ---------- ------------------ ------------------ ----------------------
         8         16                476                  0                      0
        32         64                  2                  0                      0
     32768      65536                  0                  0                      2

    可以看出，有2次执行了MULTIPASSES，这说明pga设置不足以满足排序的需要。

SQL> SELECT optimal_count, round(optimal_count*100/total, 2) optimal_perc,      
onepass_count, round(onepass_count*100/total, 2) onepass_perc,      
multipass_count, round(multipass_count*100/total, 2) multipass_perc
FROM (SELECT decode(sum(total_executions), 0, 1, 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 low_optimal_size > =32*1024);
 
  2    3    4    5    6    7    8    9 
OPTIMAL_COUNT OPTIMAL_PERC ONEPASS_COUNT ONEPASS_PERC MULTIPASS_COUNT MULTIPASS_PERC
------------- ------------ ------------- ------------ --------------- --------------
            2           50             0            0               2             50

      其实主要查看命中率就可以看出pga设置的是否足够了，上面的命中率只有20%多，是因为创建了一个大的索引所致。