Question: I need to understand the
sga_size_factor column of the v$sga_target_advice and
dba_hist_sga_target_advice.
Answer: The sga_size_factor
column of dba_hist_sga_target_advice (an AWR
history table) shows changes to the size of the SGA, as
constrained by the sga_target (10g) or the memory_target
(11g and beyond) parameter. This is
valid only when using automatic memory management (AMM).
For example, in the query below show those time when the
SGA size did not change as a result of an automatic memory
management (AMM) resize operation:
select
snap_id,
sga_size
from
dba_hist_sga_target_advice
where
sga_size_factor = 1
order by
snap_id asc;
SNAP_ID
SGA_SIZE
---------- ----------
4
508
5
508
6
508
7
508
8
508
9
508
10
508
11
508
Within v$sga_target_advice, the
sga_size_factor, show the relative changes to SGA
performance for various sizes of the SGA, ranging from 50%
of sga_size_factor=1, up to double the value (200%).
select
sga_size,
sga_size_factor,
estd_db_time_factor
from
v$sga_target_advice
order by
sga_size asc;
| SGA_SIZE |
SGA_SIZE_FACTOR |
ESTD_DB_TIME_FACTOR |
|
| 292 |
0.5 |
2.3423 |
| 438 |
0.75 |
1.192 |
| 584 |
1 |
1 |
| 730 |
1.25 |
0.5634 |
| 876 |
1.5 |
0.5634 |
| 1022 |
1.75 |
0.5634 |
| 1168 |
2 |
0.56345 |
This data is used with AMM (a.k.a. (Automatic shared
memory management (ASMM) to determine whether you want to
increase sga_target (memory_target) to improve performance
within the SGA.
Fir large mission-critical databases, many DBA's will not
use AMM and prefer to allocate and morph the pools manually.
This allows a more intelligent approach to SGA tuning than
Oracle's AMM algorighm.
