Skip to Main Content
  • Questions
  • begin_time/end_time in DBA_HIST_SNAPSHOT vs DBA_HIST_SYSMETRIC_SUMMARY vs AWR report

Breadcrumb

Question and Answer

Connor McDonald

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 100+ 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)

Comments

Very detailed interpretation about dba_hist_snapshot vs dba_hist_sysmetric_summary

Quanwen Zhao, October 13, 2021 - 12:36 pm UTC

Thanks a lot, Connor! After reading your understanding about the snap time of AWR report/dba_hist_snapshot/dba_hist_sysmetric_summary I seen like to have deeper comprehension for those time than before.

Yes, snapshot (AWR) and sysmetric is a different and independent thing but their “dba_hist_” view have both column “snap_id”, which is very easy to make me confused.

Of course, all of snapshots have a contiguous
begin_interval_time and end_interval_time, hence each snapshot recorded the delta value during the period of sample.

Oracle official doc says some metric value is also persisted by AWR snapshots. In other words these metrics such as “Transactions Per Sec” (TPS), “Logons Per Sec” (LPS) are found out in the section “Load Profile” of AWR report - whether I can understand like this, the begin/end snap time of those 2 sysmetrics aforementioned should be between the begin and end snap time of AWR report.

Best Regards
Quanwen Zhao

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.