Thanks for the question, Quanwen.
Asked: October 05, 2021 - 6:25 am UTC
Last updated: October 13, 2021 - 6:39 am UTC
Version: 19.3.0.0.0
Viewed 1000+ times
You Asked
Hello Connor & Chris :-),
I have some confusion about the
begin_time/end_time in
DBA_HIST_SNAPSHOT vs
DBA_HIST_SYSMETRIC_SUMMARY vs
AWR report.
13:52:22 SYS@emcdb> desc dba_hist_snapshot
Name Null? Type
----------------------------------------- -------- ----------------------------
SNAP_ID NOT NULL NUMBER
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
STARTUP_TIME NOT NULL TIMESTAMP(3)
<b>BEGIN_INTERVAL_TIME NOT NULL TIMESTAMP(3)
END_INTERVAL_TIME NOT NULL TIMESTAMP(3)</b>
FLUSH_ELAPSED INTERVAL DAY(5) TO SECOND(1)
SNAP_LEVEL NUMBER
ERROR_COUNT NUMBER
SNAP_FLAG NUMBER
SNAP_TIMEZONE INTERVAL DAY(0) TO SECOND(0)
BEGIN_INTERVAL_TIME_TZ TIMESTAMP(3) WITH TIME ZONE
END_INTERVAL_TIME_TZ TIMESTAMP(3) WITH TIME ZONE
CON_ID NUMBER
13:54:21 SYS@emcdb> desc dba_hist_sysmetric_summary
Name Null? Type
----------------------------------------- -------- ----------------------------
SNAP_ID NOT NULL NUMBER
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
<b>BEGIN_TIME NOT NULL DATE
END_TIME NOT NULL DATE</b>
INTSIZE NOT NULL NUMBER
GROUP_ID NOT NULL NUMBER
METRIC_ID NOT NULL NUMBER
METRIC_NAME NOT NULL VARCHAR2(64)
METRIC_UNIT NOT NULL VARCHAR2(64)
NUM_INTERVAL NOT NULL NUMBER
MINVAL NOT NULL NUMBER
MAXVAL NOT NULL NUMBER
AVERAGE NOT NULL NUMBER
STANDARD_DEVIATION NOT NULL NUMBER
SUM_SQUARES NUMBER
CON_DBID NUMBER
CON_ID NUMBER
I understand that there have begin/end snap time in an AWR report, is it like this, begin_snap_time (AWR) = begin_interval_time (DBA_HIST_SNAPSHOT) and end_snap_time (AWR) = begin_interval_time (DBA_HIST_SNAPSHOT)?
or begin_snap_time (AWR) = begin_time (DBA_HIST_SYSMETRIC_SUMMARY) and end_snap_time (AWR) = end_time (DBA_HIST_SYSMETRIC_SUMMARY)?
You know, since a snap_id has a begin/end_interval_time but an AWR report usually captures between two snap_id, so now I'm not sure where is the begin/end snap time in an AWR report?
Best Regards
Quanwen Zhao
and Connor said...
Here's my understanding of these
Each time you take a snapshot, we have the "current" moment in time (this is END_INTERVAL_TIME in dba_hist_snapshots). We are picking up any information we can find since the previous snapshot so BEGIN_INTERVAL_TIME will be the completion moment of the previous snapshot. Thus taking all snapshots should give a contiguous time coverage, eg
Snap 1: taken at 9am
Snap 2: taken at 9:30am
Snap 3 taken at 10:12am
gives
Snap2: begin/end = 9am, 9:30am
Snap3: begin/end = 9:30am, 10:12am
They are showing the period for which that snapshot collected data for. I havent explored boundary cases like (say) lets SGA is so small that ASH data doesn't span the snapshot etc. There are probably edge cases here, but you get the idea.
An AWR report does not show these *intervals*, it shows snapshot times, so an AWR for Snap 2-3 above will show:
begin snap: 9:30am
end snap: 10:12am
DBA_HIST_SYSMETRIC_SUMMARY is a different set of begin/end because its a point-in-time collection of data that sits in V$SYSMETRIC_SUMMARY which is just a set of begin/end time pairs in a memory structure.
When we take Snap 2 above, the range of values in V$SYSMETRIC_SUMMARY might be (say) 8am to 9:27am. (ie, nothing in there yet from 9:27 to 9:30)
When we take Snap 3 above, we are going to start from the high point of what we've already snapped, so the range of values in V$SYSMETRIC_SUMMARY might now be (say) 8am to 10:09am but we are going collect 9:27 to 10:09 so we don't duplicate data on a per snap basis.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment