Sizing post #1: How to standardize ASH data for sizing

This is a first blog entry for a series of posts related to the topic of “sizing.”

The standardization of ASH data for sizing consists of aggregating the number of sesions on CPU, adding the 0’s (zeroes) to this aggregated data and filling in with sample times to have 10 seconds equidistant sample times. The original idea for standardization was not mine, it was my friend Mauro Pagano that allowed me to use his idea for my blog.

When we want to know the CPU footprint of our database we can do some research using the data from AWR. The sample data stored in AWR that comes from ASH can be used to measure the CPU footprint of a database among several other measures.

The CPU data from ASH is sampled only when there is activity, meaning that periods of time without activity do not get recorded in ASH. This causes a problem in the case we want a time series with equidistant points to visualize or analyze the CPU usage of a database.

The following article shows one of several different ways to standardize the aggregation of ASH data into 10 seconds equidistant sample times. This includes adding the times where there is no activity or in other words the 0 (zero) values for cpu data.

Being AWR our source of data we need to make sure it is configured appropriately for our purposes.

We want to make sure the snapshot interval is 60 minutes and the retention period is 45 days in addition to verifying that the STATISTICS_LEVEL parameter is set to TYPICAL or ALL.

The following command shows the value for the parameter statistics_level

SQL> show parameter statistics_level

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL

If the statistics_level parameter is not set to TYPICAL or ALL we can change it using the following command:

ALTER SYSTEM SET statistics_level = TYPICAL scope=both;

The following query shows the value for snapshot_interval and retention period

SELECT EXTRACT(DAY    FROM snap_interval)*24*60+
       EXTRACT(HOUR   FROM snap_interval)   *60+
       EXTRACT(MINUTE FROM snap_interval)           INTERVAL_IN_MINUTES,
       (EXTRACT(DAY    FROM retention)*24*60+
       EXTRACT(HOUR   FROM retention)*60+
       EXTRACT(MINUTE FROM retention))/(24*60)      RETENTION_IN_DAYS
FROM dba_hist_wr_control;

INTERVAL_IN_MINUTES RETENTION_IN_DAYS
------------------- -----------------
                 60                 8

If the snapshot_interval is not 60 minutes or the retention is not 45 days we can change them using the follwing command:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    retention => 64800,       -- Minutes (= 45 Days). Current value retained if NULL.
    interval  => 60);         -- Minutes. Current value retained if NULL.
END;
/

SELECT EXTRACT(DAY    FROM snap_interval)*24*60+
       EXTRACT(HOUR   FROM snap_interval)   *60+
       EXTRACT(MINUTE FROM snap_interval)           INTERVAL_IN_MINUTES,
       (EXTRACT(DAY    FROM retention)*24*60+
       EXTRACT(HOUR   FROM retention)*60+
       EXTRACT(MINUTE FROM retention))/(24*60)      RETENTION_IN_DAYS
FROM dba_hist_wr_control;

INTERVAL_IN_MINUTES RETENTION_IN_DAYS
------------------- -----------------
                 60                45

V$ACTIVE_SESSION_HISTORY displays sampled session activity in the database. It contains snapshots of active database sessions taken once a second. A database session is considered active if it was on the CPU or was waiting for an event that didn’t belong to the Idle wait class. This view contains one row for each active session per sample and returns the latest session sample rows first.

DBA_HIST_ACTIVE_SESS_HISTORY view displays the history of the contents of the in-memory active session history of recent system activity. This view contains snapshots of V$ACTIVE_SESSION_HISTORY.

The following query shows the sample time and the number of samples at that specific sample time. This interprets as 3 sessions active at the time 2016-12-20T16:00:53, 5 sessions active at the time 2016-12-20T16:11:34 and so on.

SELECT   TO_CHAR(sample_time, 'YYYY-MM-DD"T"HH24:MI:SS') SAMPLE_TIME
        ,COUNT(*)  NUMBER_OF_SAMPLES
FROM     dba_hist_active_sess_history
WHERE    session_state='ON CPU'
GROUP BY sample_time
ORDER BY 1;

SAMPLE_TIME         NUMBER_OF_SAMPLES
------------------- -----------------
...
...
2016-12-20T16:00:53           3
2016-12-20T16:11:34           5
2016-12-20T16:11:44           1

8411 rows selected.

If we wanted to extract only the rows related to the sessions ‘ON CPU’ and discard the sessions on wait events. We can create a table and insert the data from dba_hist_active_sess_history. Because the sample time is not equidistant meaning the samples can happen now, then 9 seconds later, then 11 seconds later, we are going to call the table NON_STANDARD_TIME

DROP TABLE non_standard_time;
CREATE TABLE non_standard_time
(sample_time  DATE,
 cpu_data     NUMBER);

DESC non_standard_time
Name					   Null?    Type
----------------------------------------- -------- ----------------------------
SAMPLE_TIME					    DATE
CPU_DATA					    NUMBER

INSERT INTO non_standard_time
SELECT   sample_time, COUNT(*) cpu_data
FROM     dba_hist_active_sess_history
WHERE    session_state = 'ON CPU'
GROUP BY sample_time
ORDER BY 1;

8414 rows created.

SELECT TO_CHAR(sample_time,'YYYY-MM-DD"T"HH24:MI:SS') sample_time
      ,cpu_data 
FROM   non_standard_time
ORDER BY 1;

SAMPLE_TIME            CPU_DATA
------------------- -----------
...
...
2016-12-20T15:33:20           1
2016-12-20T15:43:41           1
2016-12-20T16:00:53           3
2016-12-20T16:11:34           5
2016-12-20T16:11:44           1
2016-12-20T16:51:08           1
2016-12-20T16:56:08           1
2016-12-20T16:56:18           1

8414 rows selected.

We need to standardize the data. The data right now is not equidistant and does not include the time where the samples are 0, meaning the ASH data does not get recorded when there is no activity. In order for us to have an equidistant sample time and include the times when there is no activity we standardize the data as follows:

The following query will show the minimum and maximum times in AWR view dba_hist_active_sess_history

COL min_time format a30
COL max_time format a30
SELECT MIN(to_char(sample_time,'YYYY-MM-DD"T"HH24:MI:SS')) min_time
      ,MAX(to_char(sample_time,'YYYY-MM-DD"T"HH24:MI:SS')) max_time
FROM   dba_hist_active_sess_history
WHERE  session_state = 'ON CPU';

MIN_TIME                       MAX_TIME
------------------------------ ------------------------------
2016-11-23T01:16:19            2016-12-20T16:56:18

This is the same information from our non_standard_time table

SELECT MIN(to_char(sample_time,'YYYY-MM-DD"T"HH24:MI:SS')) min_time
      ,MAX(to_char(sample_time,'YYYY-MM-DD"T"HH24:MI:SS')) max_time
FROM   non_standard_time;

MIN_TIME                       MAX_TIME
------------------------------ ------------------------------
2016-11-23T01:16:19            2016-12-20T16:56:18

Start Time: 2016-11-23T01:16:19 -> 2016-11-23T01:16:10

Table should have:

2016-11-23T01:16:10
2016-11-23T01:16:20
2016-11-23T01:16:30
2016-11-23T01:16:40
...
...

Until around
2016-12-20T16:56:10

Create a new table with the sample time starting at 00 seconds and 10s interval
Sample time should not have gaps in this new table meaning all 10s intervals should be there
Fill in this new table with ASH data
Fill in with 0s when there is no row for a specific 10s interval.

DROP TABLE cpu_standard;
CREATE TABLE cpu_standard
(sample_time DATE,
 cpu_data    NUMBER);

The following PL/SQL block fills the table with 0s for cpu_data column and sample_time at 10s intervals

DECLARE
 sample_10s DATE := TO_DATE('2016-11-23T01:16:10','YYYY-MM-DD"T"HH24:MI:SS');
BEGIN
LOOP
 INSERT INTO cpu_standard
 VALUES (sample_10s, 0);
 sample_10s := sample_10s + ( 1 / ( 24 * 60 * 6 ) );
 IF (sample_10s > TO_DATE('2016-12-20T16:56:10', 'YYYY-MM-DD"T"HH24:MI:SS'))
 THEN EXIT;
 END IF;
END LOOP;
END;
/

SELECT TO_CHAR(sample_time,'YYYY-MM-DD"T"HH24:MI:SS') sample_time
 ,cpu_data
FROM cpu_standard
ORDER BY 1;

SAMPLE_TIME CPU_DATA
------------------- -----------
...
...
2016-12-20T16:55:40 0
2016-12-20T16:55:50 0
2016-12-20T16:56:00 0
2016-12-20T16:56:10 0

238921 rows selected.

The table at this point has only predefined dates with 10 seconds distance between each row and all the values are 0.

We need to bring in the data from AWR into this table

The rule we are going to follow is to truncate the last digit, for example for a sample time of 09 seconds we take that value for our 00 second

seconds 
00 -> 00
01 -> 00
02 -> 00
...
08 -> 00
09 -> 00
--------
10 -> 10
11 -> 10
12 -> 10
...
18 -> 10
19 -> 10
--------
50 -> 50
51 -> 50
...
58 -> 50
59 -> 50
--------

We are simply replacing the last digit with a 0.

Before we update the table cpu_standard lets verify that the data treatment we are doing is correct

SELECT *
FROM 
(
SELECT TO_CHAR( TRUNC( sample_time, 'mi' ) + 
      ( TRUNC( ( TO_NUMBER( TO_CHAR( sample_time, 'ss' ) ) / 10 ) ) * ( 1 / ( 24 * 60 * 6 ) ) ),
      'YYYY-MM-DD"T"HH24:MI:SS') sample_time 
FROM  non_standard_time
ORDER BY sample_time DESC
)
WHERE rownum<=4
ORDER BY 1;

SAMPLE_TIME
-------------------
2016-12-20T16:11:40
2016-12-20T16:51:00
2016-12-20T16:56:00
2016-12-20T16:56:10

Compared to the data in non_standard_time we can observe that the last digit has been converted to 0.

SAMPLE_TIME            CPU_DATA
------------------- -----------
...
...
2016-12-20T16:11:44           1
2016-12-20T16:51:08           1
2016-12-20T16:56:08           1
2016-12-20T16:56:18           1

8414 rows selected.

Updating the cpu_standard table to bring the non-zero values to its respective 10 second sample.

Up to this point we have two tables:

CPU_STANDARD

SQL> desc cpu_standard
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 SAMPLE_TIME					                    DATE
 CPU_DATA					                        NUMBER

NON_STANDARD_TIME has values in sample time that are not equidistant

SELECT TO_CHAR(sample_time,'YYYY-MM-DD"T"HH24:MI:SS') sample_time
      ,cpu_data 
FROM   non_standard_time
ORDER BY 1;

SAMPLE_TIME            CPU_DATA
------------------- -----------
...
...
2016-12-20T15:33:20           1
2016-12-20T15:43:41           1
2016-12-20T16:00:53           3
2016-12-20T16:11:34           5
2016-12-20T16:11:44           1
2016-12-20T16:51:08           1
2016-12-20T16:56:08           1
2016-12-20T16:56:18           1

8414 rows selected.

CPU_STANDARD table has the data with sample time equidistant 10 seconds apart on each row and all values as 0s

SELECT TO_CHAR(sample_time,'YYYY-MM-DD"T"HH24:MI:SS') sample_time
      ,cpu_data
FROM   cpu_standard
ORDER BY 1;

SAMPLE_TIME            CPU_DATA
------------------- -----------
...
...
2016-12-20T16:55:40           0
2016-12-20T16:55:50           0
2016-12-20T16:56:00           0
2016-12-20T16:56:10           0

238921 rows selected.

The next step would be to merge the data in a way that CPU_STANDARD has the CPU_DATA from the NON_STANDARD_TIME table.

There may be several ways to do it, lets just do it explicilty step by step to understand better what is happening.

We are going to add a new column to the NON_STANDARD_TIME table to hold the standard time

ALTER TABLE non_standard_time
ADD (sample_time_std  DATE);

We update that new added column to have the standard corresponding date value

UPDATE non_standard_time
SET    sample_time_std =
 TRUNC( sample_time, 'mi' ) + 
      ( TRUNC( ( TO_NUMBER( TO_CHAR( sample_time, 'ss' ) ) / 10 ) ) * ( 1 / ( 24 * 60 * 6 ) ) );

8414 rows updated.

COMMIT;

The table NON_STANDARD_TIME has now a column with the sample time standardized.

SELECT TO_CHAR(sample_time,'YYYY-MM-DD"T"HH24:MI:SS') sample_time
      ,cpu_data
      ,TO_CHAR(sample_time_std,'YYYY-MM-DD"T"HH24:MI:SS') sample_time_std
FROM   non_standard_time
ORDER BY 1;

SAMPLE_TIME            CPU_DATA SAMPLE_TIME_STD
------------------- ----------- -------------------
...
...
2016-12-20T16:11:44           1 2016-12-20T16:11:40
2016-12-20T16:51:08           1 2016-12-20T16:51:00
2016-12-20T16:56:08           1 2016-12-20T16:56:00
2016-12-20T16:56:18           1 2016-12-20T16:56:10

8414 rows selected.

In this case we have now the same standardized sample times in both tables NON_STANDARD_TIME and CPU_STANDARD

We can join the two tables to bring the cpu_data values into CPU_STANDARD

UPDATE cpu_standard
SET    cpu_data  = ( SELECT MAX(cpu_data) 
                     FROM non_standard_time ns
                     WHERE  ns.sample_time_std = cpu_standard.sample_time)
WHERE EXISTS (
  SELECT 'X'
  FROM non_standard_time ns
  WHERE cpu_standard.sample_time = ns.sample_time_std);

8414 rows updated.

COMMIT;

SELECT TO_CHAR(sample_time,'YYYY-MM-DD"T"HH24:MI:SS') sample_time
      ,cpu_data
FROM   cpu_standard
ORDER BY 1;

SAMPLE_TIME            CPU_DATA
------------------- -----------
...
...
2016-12-20T16:55:40           0
2016-12-20T16:55:50           0
2016-12-20T16:56:00           1
2016-12-20T16:56:10           1

238921 rows selected.

The table cpu_standard can be used to graph the cpu activity of the database and to further analyze the data for sizing purposes.

Author: Jorge Barba

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s