-- ################################################
-- # Creator: Vincent Fenoll
-- # Created: 2010/01/01
-- # Name: pga_target_advice_histogram.sql
--  ################################################
-- #
-- # Compatible: Oracle 10g 11g
-- #
-- ################################################
-- #
-- # First statement: Display pga target advice histogram
-- # Second statement: Display pga target advice (%cache hit)
-- #
-- ################################################
 
SELECT
   low_optimal_size/1024 "Low(K)",
   (high_optimal_size+1)/1024 "High(K)",
   estd_optimal_executions "Optimal",
   estd_onepass_executions "One Pass",
   estd_multipasses_executions "Multi-Pass"
FROM
   v$pga_target_advice_histogram
WHERE
   pga_target_factor = 2
AND
   estd_total_executions != 0
ORDER BY
   1;

column c1     heading 'Target(M)'
column c2     heading 'Estimated|Cache Hit %'
column c3     heading 'Estimated|Over-Alloc.'
 
SELECT
   ROUND(pga_target_for_estimate /(1024*1024)) c1,
   estd_pga_cache_hit_percentage         c2,
   estd_overalloc_count                  c3
FROM
   v$pga_target_advice;