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;