AWR Queries - Advisor Related
Submitted by dave on Wed, 04/27/2011 - 15:59
SGA Advisory - Will doubling SGA size help/
According to the Oracle SGA Advisor that is!
The following query will give the current DB Time and I/O info and projected DB Time and I/O info if the SGA is doubled.
-- SGA Advisory Summary for current and SGA X 2 sizing WITH snaps as ( select snap_id, begin_interval_time from dba_hist_snapshot), SGAAdviceCur as (select snap_id, sga_size, estd_db_time, estd_physical_reads from dba_hist_sga_target_advice where SGA_SIZE_FACTOR=1), SGAAdviceNew as (select snap_id, sga_size as sga_size_new, estd_db_time as estd_db_time_new, estd_physical_reads as estd_physical_reads_new from dba_hist_sga_target_advice where SGA_SIZE_FACTOR=2) SELECT snaps.SNAP_ID, TO_CHAR(snaps.BEGIN_INTERVAL_TIME,'MM/DD/YYYY HH24:MI') as RecordDate, SGAAdviceCur.SGA_SIZE, SGAAdviceNew.SGA_SIZE_NEW, SGAAdviceCur.ESTD_DB_TIME, SGAAdviceNew.ESTD_DB_TIME_NEW, ROUND(SGAAdviceNew.ESTD_DB_TIME_NEW/SGAAdviceCur.ESTD_DB_TIME*100,2) as NewTimePct, SGAAdviceCur.ESTD_PHYSICAL_READS, SGAAdviceNew.ESTD_PHYSICAL_READS_NEW, ROUND(SGAAdviceNew.ESTD_PHYSICAL_READS_NEW/SGAAdviceCur.ESTD_PHYSICAL_READS*100,2) as NewReadsPct from snaps left outer join SGAAdviceCur on snaps.snap_id=SGAAdviceCur.snap_id left outer join SGAAdviceNew on snaps.snap_id = SGAAdviceNew.snap_id ORDER BY begin_interval_time;