PGA Queries

show parameter pga

You can issue the following simple query to find out the PGA cache hit percentage as well as number of PGA performance - related values :

select * from v$pgastat;


You can also use the  v$sql_workarea_histogram view to find out how much of its work the database is performing in an optimal fashion

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;

select low_optimal_size/1024 low,
     (high_optimal_size+1)/1024 high,
     optimal_executions, onepass_executions, multipasses_executions
from v$sql_workarea_histogram
where total_executions !=0; 

select name profile,cnt,decode(total,0,0,round(cnt*100/total)) percentage from (select name,value cnt,(sum(value) over()) total from v$SYSSTAT where name like 'workarea exec%');