DB Advisories

DB Cache Advisor (9i and above)
=========================
Query below will provide advisory data depending on usage history :

SELECT a.size_for_estimate From_Size , a.buffers_for_estimate Buffers
, a.estd_physical_read_factor Phy_Read_Factor , a.estd_physical_reads Phy_Reads
FROM V$DB_CACHE_ADVICE a
WHERE a.name = 'DEFAULT'
AND a.block_size = (SELECT value FROM V$PARAMETER
WHERE name = 'db_block_size')
AND a.advice_status = 'ON'


Shared Pool Advisor (9i and above)
=========================
1)
SELECT a.shared_pool_size_for_estimate "Size of Shared Pool in MB",
a.shared_pool_size_factor "Size Factor",
a.estd_lc_time_saved "Time Saved in sec"
FROM v$shared_pool_advice a where
a.shared_pool_size_for_estimate = b.shared_pool_size_for_estimate - 128 ;
2) Above query using rank :

SELECT a.shared_pool_size_for_estimate "Size of Shared Pool in MB",
a.shared_pool_size_factor "Size Factor",
a.estd_lc_time_saved "Time Saved in sec" ,
abs(a.estd_lc_time_saved-b.estd_lc_time_saved) SECS_DIFF
FROM v$shared_pool_advice a,v$shared_pool_advice b where
a.shared_pool_size_for_estimate = b.shared_pool_size_for_estimate -
(
( select shared_pool_size_for_estimate from
(select shared_pool_size_for_estimate,rank()
over (order by shared_pool_size_for_estimate ) rnk
from v$shared_pool_advice ) where rnk = 2 )
-
(select shared_pool_size_for_estimate from
(select shared_pool_size_for_estimate,rank()
over (order by shared_pool_size_for_estimate ) rnk
from v$shared_pool_advice ) where rnk = 1 )
) ;

PGA advisory
===========
SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
PGA_TARGET_FACTOR FACTOR,
ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
ESTD_OVERALLOC_COUNT
FROM v$pga_target_advice;

• V$PGA_TARGET_ADVICE view predicts how the statistics cache hit percentage and
  over allocation count in V$PGASTAT will be impacted if you change the value of
  the initialization parameter PGA_AGGREGATE_TARGET.
• set the PGA_AGGREGATE_TARGET parameter to a
  value where we avoid any over allocation .