After years of using AWR reports I noticed that reported numbers are really strange.
Please compare following tables (this is just an example from a small test system):
Time Model Statistics
- Total time in database user-calls (DB Time): 707s
|Statistic Name||Time (s)||% of DB Time|
|sql execute elapsed time||458.65||64.87|
|parse time elapsed||45.54||6.44|
|hard parse elapsed time||37.18||5.26|
|hard parse (sharing criteria) elapsed time||8.52||1.20|
|PL/SQL execution elapsed time||5.03||0.71|
|PL/SQL compilation elapsed time||1.87||0.27|
|connection management call elapsed time||1.72||0.24|
|hard parse (bind mismatch) elapsed time||0.71||0.10|
|repeated bind elapsed time||0.69||0.10|
|sequence load elapsed time||0.00||0.00|
|background elapsed time||396.96|
|background cpu time||15.83|
Segments by Physical Reads
- Total Physical Reads: 23,942
- Captured Segments account for 71.6% of Total
|Owner||Tablespace Name||Object Name||Subobject Name||Obj. Type||Physical Reads||%Total|
SGA Target Advisory
|SGA Target Size (M)||SGA Size Factor||Est DB Time (s)||Est Physical Reads|
Using these numbers I can calculate
|SGA Size||Est DB Time||Time Saved||Est Physical Reads||Reads Saved|
- Why "Est DB Time" for the current SGA size is 457,880 sec, when total "DB time" was 707 sec only?
- Why "Est Physical Reads" for the current SGA size are 119,594,551 when "Total Physical Reads" was 23,942 only?
- Thus I hardly can save 13,691 sec and 2,798,512 physical reads by doubling SGA !
It looks like "Est DB Time" is cumulative value, because the difference with previous snapshot for "Size Factor" = 1 gives exactly real "DB time".
But this method applied to other factors as well to "Est Physical Reads" gives nonsense results.
Comparing just percent of "Est DB Time", like 444,189 / 457,880 => 97% (for increase 1200 to 2400) also does not make much sense,
as these numbers are not comparable to the real snapshot "DB time".
You can find the complete report here but I am sure you will find similar numbers in your reports too.
Can you explain these numbers?
How do you calculate possible benefit of SGA increase for large productive systems?