Close

Introduction to ASH Data, part 3

  1. What is ASH?
  2. AWR and Activity Types
  3. Average Active Sessions (this page)
  4. Graphical Visualizations

In the first 2 chapters of this series I queried the ASH data with respect to distinct sample times. While this is helpful maximizing the granularity of the analysis, it can sometimes be too much detail, too many data points for the eye to follow, or if run through a graphical engine, so many elements that the resulting chart becomes slow to render. It’s also possible the resolution of the resulting chart may be too low to adequately display all of the points distinctly; thus making the abundance of data redundant and useless.

Furthermore, sometimes what you’re looking for isn’t a detailed play-by-play of all activity but a general picture. You know there will be peaks and valleys in the system activity; but how busy is it on average?

Also, any one sample might be unusually high or unusually low, but looking at a wider view, within a given period the overall activity is what you’d expect.

All of these lead to the one of the most common aggregations across time with ASH data: Average Active Sessions (AAS.) The idea behind the calculation is simple enough, count up the sessions for each second of sampling and then divide that by the total number of seconds within the period. Thus yielding the average number of active sessions during an interval of time. The idea is simple and the math is simple for V$ACTIVE_SESSION_HISTORY because that view’s contents are sampled each second. DBA_HIST_ACTIVE_SESS_HISTORY is only populated with samples once each 10 seconds. Thus creating an average requires extrapolation for the missing time slices by multiplying the counts you do have by 10.

In the following example the active sessions counts are averaged over each minute (thus averaging over 60 seconds) within a half-hour span.

SQL>   SELECT TRUNC(sample_time, 'mi') time,
  2           ROUND(10 * COUNT(*) / 60) avg_sess_per_minute
  3      FROM dba_hist_active_sess_history
  4     WHERE sample_time >= TIMESTAMP '2018-09-15 13:00:00'
  5       AND sample_time < TIMESTAMP '2018-09-15 13:30:00'
  6  GROUP BY TRUNC(sample_time, 'mi')
  7  ORDER BY 1;
TIME                    AVG_SESS_PER_MINUTE
2018-09-15 13:00:00                       5
2018-09-15 13:01:00                       5
2018-09-15 13:02:00                       3
2018-09-15 13:03:00                       3
2018-09-15 13:04:00                       5
2018-09-15 13:05:00                       7
2018-09-15 13:06:00                       5
2018-09-15 13:07:00                       4
2018-09-15 13:08:00                       3
2018-09-15 13:09:00                       5
2018-09-15 13:10:00                       4
2018-09-15 13:11:00                       4
2018-09-15 13:12:00                       7
2018-09-15 13:13:00                       3
2018-09-15 13:14:00                       3
2018-09-15 13:15:00                       5
2018-09-15 13:16:00                       8
2018-09-15 13:17:00                       6
2018-09-15 13:18:00                      10
2018-09-15 13:19:00                       6
2018-09-15 13:20:00                       9
2018-09-15 13:21:00                       6
2018-09-15 13:22:00                       6
2018-09-15 13:23:00                       4
2018-09-15 13:24:00                       3
2018-09-15 13:25:00                       3
2018-09-15 13:26:00                       4
2018-09-15 13:27:00                       4
2018-09-15 13:28:00                       7
2018-09-15 13:29:00                       3

30 rows selected.

Another approach is to count the sessions for each sample and then apply averages outside. This way can be less efficient since it involves multiple aggregations; but it can be a simpler construction

SQL> WITH
  2      sample_counts
  3      AS
  4          (  SELECT sample_time, COUNT(*) cnt
  5               FROM dba_hist_active_sess_history
  6              WHERE sample_time >= TIMESTAMP '2018-09-15 13:00:00'
  7                AND sample_time < TIMESTAMP '2018-09-15 13:30:00'
  8           GROUP BY sample_time)
  9    SELECT TRUNC(sample_time, 'mi'),
 10           ROUND(10 * SUM(cnt) / 60) avg_sess_per_minute
 11      FROM sample_counts
 12  GROUP BY TRUNC(sample_time, 'mi')
 13  ORDER BY 1;
TRUNC(SAMPLE_TIME,'MI')     AVG_SESS_PER_MINUTE
2018-09-15 13:00:00                           5
2018-09-15 13:01:00                           5
2018-09-15 13:02:00                           3
2018-09-15 13:03:00                           3
2018-09-15 13:04:00                           5
2018-09-15 13:05:00                           7
2018-09-15 13:06:00                           5
2018-09-15 13:07:00                           4
2018-09-15 13:08:00                           3
2018-09-15 13:09:00                           5
2018-09-15 13:10:00                           4
2018-09-15 13:11:00                           4
2018-09-15 13:12:00                           7
2018-09-15 13:13:00                           3
2018-09-15 13:14:00                           3
2018-09-15 13:15:00                           5
2018-09-15 13:16:00                           8
2018-09-15 13:17:00                           6
2018-09-15 13:18:00                          10
2018-09-15 13:19:00                           6
2018-09-15 13:20:00                           9
2018-09-15 13:21:00                           6
2018-09-15 13:22:00                           6
2018-09-15 13:23:00                           4
2018-09-15 13:24:00                           3
2018-09-15 13:25:00                           3
2018-09-15 13:26:00                           4
2018-09-15 13:27:00                           4
2018-09-15 13:28:00                           7
2018-09-15 13:29:00                           3

30 rows selected.

Next, we’ll extend the previous query by adding a maximum value. It’s important to remember that DBA_HIST_ACTIVE_SESS_HISTORY is a collection of samples of samples. Thus the MAX-value seen within the samples could still be lower than the actual active session peak your system experienced. So, while these results should not be taken as absolutes, they can still offer an expanded, approximate view of what was happening.

SQL> WITH
  2      sample_counts
  3      AS
  4          (  SELECT sample_time, COUNT(*) cnt
  5               FROM dba_hist_active_sess_history
  6              WHERE sample_time >= TIMESTAMP '2018-09-15 13:00:00'
  7                AND sample_time < TIMESTAMP '2018-09-15 13:30:00'
  8           GROUP BY sample_time)
  9    SELECT TRUNC(sample_time, 'mi'),
 10           ROUND(10 * SUM(cnt) / 60) avg_sess_per_minute,
 11           MAX(cnt) max_sess_per_minute
 12      FROM sample_counts
 13  GROUP BY TRUNC(sample_time, 'mi')
 14  ORDER BY 1;
TRUNC(SAMPLE_TIME,'MI')     AVG_SESS_PER_MINUTE   MAX_SESS_PER_MINUTE
2018-09-15 13:00:00                           5                     9
2018-09-15 13:01:00                           5                     9
2018-09-15 13:02:00                           3                     3
2018-09-15 13:03:00                           3                     6
2018-09-15 13:04:00                           5                     6
2018-09-15 13:05:00                           7                    24
2018-09-15 13:06:00                           5                     6
2018-09-15 13:07:00                           4                     6
2018-09-15 13:08:00                           3                     3
2018-09-15 13:09:00                           5                     6
2018-09-15 13:10:00                           4                     6
2018-09-15 13:11:00                           4                     6
2018-09-15 13:12:00                           7                    24
2018-09-15 13:13:00                           3                     3
2018-09-15 13:14:00                           3                     3
2018-09-15 13:15:00                           5                     9
2018-09-15 13:16:00                           8                    24
2018-09-15 13:17:00                           6                     6
2018-09-15 13:18:00                          10                    27
2018-09-15 13:19:00                           6                     9
2018-09-15 13:20:00                           9                    27
2018-09-15 13:21:00                           6                     9
2018-09-15 13:22:00                           6                     6
2018-09-15 13:23:00                           4                     9
2018-09-15 13:24:00                           3                     3
2018-09-15 13:25:00                           3                     3
2018-09-15 13:26:00                           4                     6
2018-09-15 13:27:00                           4                     6
2018-09-15 13:28:00                           7                    24
2018-09-15 13:29:00                           3                     3

30 rows selected.

As with previous queries, these numbers can then be turned into a text-based bar chart to visualize the system activity. Here the average active sessions are charted with the high-watermark of maximum sessions indicated within each minute.

SQL> WITH
  2      sample_counts
  3      AS
  4          (  SELECT sample_time, COUNT(*) cnt
  5               FROM dba_hist_active_sess_history
  6              WHERE sample_time >= TIMESTAMP '2018-09-15 13:00:00'
  7                AND sample_time < TIMESTAMP '2018-09-15 13:30:00'
  8           GROUP BY sample_time)
  9    SELECT time,
 10           RPAD('*', avg_sess_per_minute, '*') ||
 11           LPAD(']',max_sess_per_minute-avg_sess_per_minute) chart2
 12      FROM (  SELECT TRUNC(sample_time, 'mi') time,
 13                     ROUND(10 * SUM(cnt) / 60) avg_sess_per_minute,
 14                     MAX(cnt) max_sess_per_minute
 15                FROM sample_counts
 16            GROUP BY TRUNC(sample_time, 'mi'))
 17  ORDER BY 1;
TIME                  CHART2
2018-09-15 13:00:00   *****   ]
2018-09-15 13:01:00   *****   ]
2018-09-15 13:02:00   ***
2018-09-15 13:03:00   ***  ]
2018-09-15 13:04:00   *****]
2018-09-15 13:05:00   *******                ]
2018-09-15 13:06:00   *****]
2018-09-15 13:07:00   **** ]
2018-09-15 13:08:00   ***
2018-09-15 13:09:00   *****]
2018-09-15 13:10:00   **** ]
2018-09-15 13:11:00   **** ]
2018-09-15 13:12:00   *******                ]
2018-09-15 13:13:00   ***
2018-09-15 13:14:00   ***
2018-09-15 13:15:00   *****   ]
2018-09-15 13:16:00   ********               ]
2018-09-15 13:17:00   ******
2018-09-15 13:18:00   **********                ]
2018-09-15 13:19:00   ******  ]
2018-09-15 13:20:00   *********                 ]
2018-09-15 13:21:00   ******  ]
2018-09-15 13:22:00   ******
2018-09-15 13:23:00   ****    ]
2018-09-15 13:24:00   ***
2018-09-15 13:25:00   ***
2018-09-15 13:26:00   **** ]
2018-09-15 13:27:00   **** ]
2018-09-15 13:28:00   *******                ]
2018-09-15 13:29:00   ***

As shown above, aggregating to the minute, or any of the other native TRUNC levels (minute, hour, day, week, etc.) isn’t too difficult. If you want to aggregate to another range, for example sampling in windows of 15 minutes then the math is a little trickier but the idea is still the same. Modify the truncation logic to produce times on the hour, 15, 30, and 45 minutes past the hour and divide the count totals by 900 seconds (15 minutes * 60 seconds per minute.)

SQL> WITH
  2      sample_counts
  3      AS
  4          (  SELECT sample_time, COUNT(*) cnt
  5               FROM dba_hist_active_sess_history
  6              WHERE sample_time >= TIMESTAMP '2018-09-15 07:00:00'
  7                AND sample_time < TIMESTAMP '2018-09-15 17:00:00'
  8           GROUP BY sample_time)
  9    SELECT time,
 10           RPAD('*', avg_sess_per_window, '*') ||
 11           LPAD(']',max_sess_per_window-avg_sess_per_window) chart
 12      FROM (  SELECT TRUNC(sample_time, 'hh24')
 13                      + FLOOR((TO_NUMBER(TO_CHAR(sample_time, 'mi'), '99')) / 15) * 15 / 1440 time,
 14                     ROUND(10 * SUM(cnt) / (15*60)) avg_sess_per_window,
 15                     MAX(cnt) max_sess_per_window
 16                FROM sample_counts
 17            GROUP BY TRUNC(sample_time, 'hh24')
 18                      + FLOOR((TO_NUMBER(TO_CHAR(sample_time, 'mi'), '99')) / 15) * 15 / 1440)
 19  ORDER BY 1;
TIME                  CHART
2018-09-15 07:00:00   ******************                                 ]
2018-09-15 07:15:00   ***********                        ]
2018-09-15 07:30:00   **********                             ]
2018-09-15 07:45:00   *********                          ]
2018-09-15 08:00:00   **********                         ]
2018-09-15 08:15:00   **********                         ]
2018-09-15 08:30:00   **********                             ]
2018-09-15 08:45:00   *********                          ]
2018-09-15 09:00:00   ********                           ]
2018-09-15 09:15:00   ******                             ]
2018-09-15 09:30:00   *****                          ]
2018-09-15 09:45:00   *****                          ]
2018-09-15 10:00:00   ********                       ]
2018-09-15 10:15:00   *******                            ]
2018-09-15 10:30:00   ******                             ]
2018-09-15 10:45:00   ********                           ]
2018-09-15 11:00:00   ******                         ]
2018-09-15 11:15:00   ****                               ]
2018-09-15 11:30:00   ******                         ]
2018-09-15 11:45:00   *****                          ]
2018-09-15 12:00:00   *****                  ]
2018-09-15 12:15:00   ****                   ]
2018-09-15 12:30:00   ****                   ]
2018-09-15 12:45:00   ****                   ]
2018-09-15 13:00:00   ****                   ]
2018-09-15 13:15:00   *****                     ]
2018-09-15 13:30:00   ****                   ]
2018-09-15 13:45:00   ****                   ]
2018-09-15 14:00:00   ****                   ]
2018-09-15 14:15:00   *****                     ]
2018-09-15 14:30:00   *****                  ]
2018-09-15 14:45:00   ****                   ]
2018-09-15 15:00:00   ****                      ]
2018-09-15 15:15:00   ****                   ]
2018-09-15 15:30:00   ****                   ]
2018-09-15 15:45:00   ****                   ]
2018-09-15 16:00:00   ****                   ]
2018-09-15 16:15:00   ****                   ]
2018-09-15 16:30:00   ****                   ]
2018-09-15 16:45:00   ****                   ]

This method can easily be modified to use 5 minute, 10 minute, or other windows simply by replacing each “15” above with the corresponding window length in minutes. The method doesn’t require even division within an hour. For example, if you used 13 minutes for your sampling windows you’d get windows beginning at 00, 13, 26, and 39 minutes after the hour, each lasting 13 minutes and a final remainder window of 8 minutes starting at 52 minutes after the hour.

And finally, the same idea can be used with arbitrary time windows. Instead of having a fixed duration for each window, you simply divide each window’s count by the duration (in seconds) of that window. Below I’ve selected 5 different windows ranging in duration from 1 minute up to an hour. The same approach as above still applies.

SQL> WITH
  2      sample_times
  3      AS
  4          (SELECT TO_DATE('2018-09-15 07:00:00', 'yyyy-mm-dd hh24:mi:ss') start_time,
  5                  TO_DATE('2018-09-15 08:00:00', 'yyyy-mm-dd hh24:mi:ss') end_time
  6             FROM DUAL
  7           UNION ALL
  8           SELECT TO_DATE('2018-09-15 08:00:00', 'yyyy-mm-dd hh24:mi:ss') start_time,
  9                  TO_DATE('2018-09-15 08:05:00', 'yyyy-mm-dd hh24:mi:ss') end_time
 10             FROM DUAL
 11           UNION ALL
 12           SELECT TO_DATE('2018-09-15 08:30:00', 'yyyy-mm-dd hh24:mi:ss') start_time,
 13                  TO_DATE('2018-09-15 09:00:00', 'yyyy-mm-dd hh24:mi:ss') end_time
 14             FROM DUAL
 15           UNION ALL
 16           SELECT TO_DATE('2018-09-15 13:00:00', 'yyyy-mm-dd hh24:mi:ss') start_time,
 17                  TO_DATE('2018-09-15 13:01:00', 'yyyy-mm-dd hh24:mi:ss') end_time
 18             FROM DUAL
 19           UNION ALL
 20           SELECT TO_DATE('2018-09-15 13:30:00', 'yyyy-mm-dd hh24:mi:ss') start_time,
 21                  TO_DATE('2018-09-15 14:00:00', 'yyyy-mm-dd hh24:mi:ss') end_time
 22             FROM DUAL),
 23      sample_counts
 24      AS
 25          (  SELECT ash.sample_time, COUNT(ash.sample_id) cnt
 26               FROM dba_hist_active_sess_history ash, sample_times t
 27              WHERE ash.sample_time >= t.start_time
 28                AND ash.sample_time < t.end_time
 29           GROUP BY ash.sample_time)
 30    SELECT start_time,
 31           end_time,
 32           RPAD('*', avg_sess_per_window, '*')
 33            || LPAD(']', max_sess_per_window - avg_sess_per_window) chart
 34      FROM (  SELECT t.start_time,
 35                     t.end_time,
 36                     ROUND(10 * SUM(c.cnt) /
 37                         ((t.end_time - t.start_time) * 86400)) avg_sess_per_window,
 38                     MAX(c.cnt) max_sess_per_window
 39                FROM sample_counts c, sample_times t
 40               WHERE c.sample_time >= t.start_time
 41                 AND c.sample_time < t.end_time
 42            GROUP BY t.start_time, t.end_time)
 43  ORDER BY 1;
START_TIME            END_TIME              CHART
2018-09-15 07:00:00   2018-09-15 08:00:00   ************                                       ]
2018-09-15 08:00:00   2018-09-15 08:05:00   **********                         ]
2018-09-15 08:30:00   2018-09-15 09:00:00   **********                             ]
2018-09-15 13:00:00   2018-09-15 13:01:00   *****   ]
2018-09-15 13:30:00   2018-09-15 14:00:00   ****                   ]

Hopefully these queries and the previous articles give a good start in pulling data with standard SQL tools, both numerically and with text-based charting. In the next chapter we’ll look at using other tools with graphical capabilities to make more sophisticated charts with finer visual granularities.