These advisors are straightforward. There is
some debate as to the validity of the
findings they produce, both in a tabular format
showing increase or decrease in reads as memory
is increased or decreased. The
tabular/graph-like data can be queried directly
via canned scripts. An example of what the SGA
Advisor generates in a graph is shown in Figure
8.8.
Figure 8.8:
Memory
Advice
Sample queries for SGA target advice include
those which replicate what a graph shows and
what the history has been at a certain size
(does the SGA size advice vary during the day?).
In this example, it can be seen that the advice
has held steady at 508MB for the snapshots
shown.
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
The relevant data dictionary views have ADVICE
in them and it is a trivial matter to query
those based on a snapshot ID to see what the
advice was at that time.
Undo Advisor
The Automated Undo Advisor is also simple to
access and query. This advisor evaluates the
space needed to ensure a guaranteed retention
time. The retention time comes into play when
considering flashback. Since flashback goes back
in time, a sufficient amount of undo space,
which supports the amount of time, needs to be
set aside for the undo tablespace. Undo data
cannot be overwritten in order for flashback to
work.
Figure 8.9:
Undo
Tablespace Growth Line
Calculating the current optimal setting can be
done via a query like that below:
col "ACTUAL UNDO SIZE [MByte]" for 999999999
col "UNDO RETENTION [Sec]" for a20
col "OPTIMAL UNDO RETENTION [Sec]" for
999999999
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO
SIZE [MB]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value)
* TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO
SIZE [MB]"
FROM (
SELECT SUM(a.bytes)
undo_size
FROM v$datafile
a,
v$tablespace b,
dba_tablespaces
c
WHERE
c.contents = 'UNDO'
AND c.status =
'ONLINE'
AND b.name
= c.tablespace_name
AND a.ts# =
b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name =
'undo_retention'
AND f.name = 'db_block_size';
The results for a small system running on a
laptop:
ACTUAL UNDO SIZE [MB]
UNDO RETENTION [Sec] NEEDED UNDO SIZE [MB]
---------------------
-------------------- ---------------------
190 900
43.5234375
MTTR
Advisor
The MTTR Advisor (Mean Time To Recover Advisor)
is enabled when two parameters are set to
certain values:
STATISTICS_LEVEL and
FAST_START_MTTR_TARGET. The required values
for
STATISTICS_LEVEL are TYPICAL or
ALL. When the FAST_START_MTTR_TARGET is
set to a non-zero value, combined with
STATISTICS_LEVEL being
appropriately set, the advisor is started.
Several other parameters should not be set
because they will interfere with mechanisms used
to meet the value or setting of
FAST_START_MTTR_TARGET.
These
other parameters are:
-
FAST_START_IO_TARGET
-
LOG_CHECKPOINT_INTERVAL
-
LOG_CHECKPOINT_TIMEOUT
The possible settings for
FAST_START_MTTR_TARGET range
between 0 and 3600 seconds. Any setting above
3600 defaults back to 3600.
Due to what the parameter represents, the
two-phase instance recovery process of roll
forward and roll back, some amount of time is
going to be spent on startup to apply committed
transactions not yet written to datafiles and to
roll back the uncommitted ones. Therefore, a
setting of 0 and obviously anything less than
that is not realistic. Whether the time is 10
seconds, 30 seconds, or 2 minutes, examples
depend, to a degree, on how the redo logs are
sized.
So, in conjunction with the redo log size and
the time it takes for the database to start up
regardless of what the logs have to deal with, a
query from
V$INSTANCE_RECOVERY can be used to
calibrate the optimal or practical time for the
mean time to recovery.
The results of the following query show the
system is pretty much calibrated.
SELECT TARGET_MTTR,
ESTIMATED_MTTR
FROM V$INSTANCE_RECOVERY;
TARGET_MTTR
ESTIMATED_MTTR
-----------
--------------
37
35
Another ADVICE type view,
V$MTTR_TARGET_ADVICE, shows
statistics and advisories collected by this
advisor. If the advisor is not started, the
following graph will not appear under the
Instance Recovery section of the Recovery
Settings page in OEM.
