PGA Monitoring Scripts using v$sql_workarea and v$sql_workarea_active

1. Finding top ten work areas requiring the most cache memory:

select * 
from
 (select workarea_address, operation_type, policy, estimated_optimal_size
    from v$sql_workarea
  order by estimated_optimal_size DESC)
where ROWNUM <=10;

2. Finding the percentage of work areas using maximum memory:

select operation_type, total_executions * 100  / optimal_executions "%cache"
From v$sql_workarea
Where policy='AUTO'
And optimal_executions > 0
Order By operation_type;

3. Finding the top ten biggest work areas currently allocated in the system:

select c.sql_text, w.operation_type, top_ten.wasize
From (Select *
      From (Select workarea_address, actual_mem_used wasize
            from v$sql_workarea_active
            Order by actual_mem_used)
      Where ROWNUM <=10) top_ten,
      v$sql_workarea w,
      v$sql c
Where    w.workarea_address=top_ten.workarea_address
        And c.address=w.address
        And c.child_number = w.child_number
        And c.hash_value=w.hash_value;          

4. Finding the percentage of memory that is over and under allocated:

select  total_used,
        under*100/(total_used+1) percent_under_use,
        over*100/(total_used+1)   percent_over_used
From
        ( Select
                sum(case when expected_size > actual_mem_used 
                                       then actual_mem_used else 0 end) under,
                sum(case when expected_size<> actual_mem_used 
                                       then actual_mem_used else 0 end) over,
                sum(actual_mem_used) total_used
        From v$sql_workarea_active
        Where policy='AUTO') usage;