This topic provides information about public views containing rolling statistics.
For convenience in creating data marts, some special statistics for a certain sub-set of metrics are pre-calculated on a regular basis and made available through these "rolling statistics" views.
For information on the public views needed for creating data marts, see Accessing Capacity Optimization data using Public Views. The views described here are additional public views that pre-calculate some commonly needed statistics: percentile statistics and linear regression parameters. These views are called ROLL views because their calculations are based on a rolling window of data.
These two ROLL views are identical except that the first contains data for Systems while the second contains data for Business drivers. We describe below the
PV_SYS_DATA_ROLL view. The description for the BDRV view simply replaces SYS with BDRV.
PV_SYS_DATA_ROLL contains percentile statistics and linear regression parameters, for systems for a selected sub-set of metric instances. Each row contains the latest data only; it is refreshed each day by a back-end service.
The set of metric instances is selected based on certain meaningful combinations of Entity Types (
SYSTYPEID) and Metrics (METRIC). For these selected metric instances, hourly data samples for the previous so many consecutive days (e.g., 30 days) are used to compute these statistics. For each metric, the meaning of the metric is used to decide whether to use the AVERAGE or MAXIMUM sample as the basis for computations. For CONF type metrics, the VALUE is used and converted to a number. For the meanings of these terms, see Metric values and summarization.
The key columns for this view are:
SYSMETRICIDwe need to use
PV_SYS_METRICview. We can run select query on
PV_SYS_METRICwith inputs of sysid and metric.
Select * from PV_SYS_METRIC where sysid=2070 and metric ='CPU_MHZ'
ROLLPERIOD: The number of consecutive calendar days previous to the time of calculation, whose hourly data is used to compute the statistics. Roll periods of 30 days, 7 days, and 1 day are used for some metrics, while for others, only a 30 day period is used.
System metric ID
Roll period reports the roll-up period. If it is less than 10000, it is expressed in DAYS. If it is greater than 10000, it is expressed in the number of hours as ROLLPERIOD-10000 (for instance, if it is 10024, it will be expressed as 10024-10000 = 24h).
The start of the roll period for this metric instance
The end of the roll period for this metric instance, which is also the time at which the statistics were calculated
The actual total validity duration of the hours that were available and were used for the computation, in seconds
The actual number of hourly samples that were available and were used for the computation
Minimum sample value found in the hourly samples
Maximum sample value found in the hourly samples
Daily rise (slope) found in the hourly samples. Calculated using robust linear regression whenever the JAVA option is found in the Oracle instance
The intercept of hourly samples calculated at the "TS" date
The 95th discrete percentile of the hourly samples
Similar percentiles for xx = 05, 10, 25, 50, and 75 of the hourly samples.
The percentile statistics and linear regression model are useful representations of the hourly sample data available in the roll period. The back-end service tries to keep the statistics and model valid for as long as possible. Each day that the service runs, it checks to see if any new hourly data has been loaded into the data warehouse from the time of the last update. If it finds no new hourly data for any metric instance, it keeps each row of that metric instance unchanged. Thus, you can use the UPDATETS field to understand how old the computation is, and the combination of TS and UPDATETS to understand the exact time window whose hourly samples have been used.