Introduction to ASH Data

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

The Oracle database captures session activity approximately once each second. This data can then be used to identify usage patterns and performance problems. Many performance experts have written very good suites of scripts and tools to query, analyze, and chart the ASH data. However, some of these tool sets can be somewhat intimidating for new users.

In this article I’d like to introduce a few simple queries of ASH data as well as a simple text-based charting method built from those queries. Thus forming a foundation for other, more complex queries as the users gain comfort using the ASH data effectively.

Before we begin digging in to the data, it is important to note ASH and AWR information is not automatically made available to all users. In addition to the object privileges needed to read the views, there are licensing requirements. Simply SELECTing data from V$ACTIVE_SESSION_HISTORY or DBA_HIST_ACTIVE_SESS_HISTORY requires the Oracle Diagnostic Pack license.

Probably the most common usage of ASH data is simply measuring how much activity is on the system at various points in time. When activity is low the database starts to become idle and should be responsive to user requests. As activity increases users start to compete for the limited CPU capacity. A simple method of measuring activity is to simply count the number active sessions at various points in time. At the most basic level this would simply be counting the number of sessions captured in each sample time. For example, in the following query we select a window of 20 seconds. The system varied from a low of 4 active sessions up to a high of 18 active sessions. These numbers, without any greater context simply indicate the system had varying levels of activity. There is no indicator that the 4 was “good” or the 18 was “bad” but the measurements are a start.

SQL>   SELECT sample_time, COUNT(*) cnt
  2      FROM gv$active_session_history
  3     WHERE sample_time >= TIMESTAMP '2018-09-15 09:00:00'
  4       AND sample_time < TIMESTAMP '2018-09-15 09:00:21'
  5  GROUP BY sample_time
  6  ORDER BY sample_time;
SAMPLE_TIME                       CNT
2018-09-15 09:00:00.969000000       6
2018-09-15 09:00:01.969000000       6
2018-09-15 09:00:02.969000000       4
2018-09-15 09:00:03.979000000       4
2018-09-15 09:00:04.979000000       4
2018-09-15 09:00:05.979000000       6
2018-09-15 09:00:06.979000000       6
2018-09-15 09:00:07.979000000       6
2018-09-15 09:00:08.979000000       6
2018-09-15 09:00:09.979000000       6
2018-09-15 09:00:10.979000000       4
2018-09-15 09:00:11.989000000       6
2018-09-15 09:00:12.989000000      18
2018-09-15 09:00:14.049000000      18
2018-09-15 09:00:15.049000000      18
2018-09-15 09:00:16.049000000      18
2018-09-15 09:00:17.049000000       4
2018-09-15 09:00:18.049000000       6
2018-09-15 09:00:19.049000000       6
2018-09-15 09:00:20.049000000       6

20 rows selected.

You may have noticed the view I queried is gv$active_session_history rather than just v$active_session_history. While multiple nodes in a RAC system would need to be handled, in this case there is no functional difference as my test system only has a single instance. The rest of the examples in this article are on the same system.

This strategy of pulling the numerical counts is useful, but sometimes it’s helpful to have a visualization to convey the information more easily. A simple bar chart is easy to generate by padding characters in length proportional to those counts.

SQL>   SELECT sample_time, COUNT(*) cnt, RPAD('*', COUNT(*), '*') chart
  2      FROM gv$active_session_history
  3     WHERE sample_time >= TIMESTAMP '2018-09-15 09:00:00'
  4       AND sample_time < TIMESTAMP '2018-09-15 09:00:21'
  5  GROUP BY sample_time
  6  ORDER BY sample_time;
SAMPLE_TIME                       CNT CHART
2018-09-15 09:00:00.969000000       6 ******
2018-09-15 09:00:01.969000000       6 ******
2018-09-15 09:00:02.969000000       4 ****
2018-09-15 09:00:03.979000000       4 ****
2018-09-15 09:00:04.979000000       4 ****
2018-09-15 09:00:05.979000000       6 ******
2018-09-15 09:00:06.979000000       6 ******
2018-09-15 09:00:07.979000000       6 ******
2018-09-15 09:00:08.979000000       6 ******
2018-09-15 09:00:09.979000000       6 ******
2018-09-15 09:00:10.979000000       4 ****
2018-09-15 09:00:11.989000000       6 ******
2018-09-15 09:00:12.989000000      18 ******************
2018-09-15 09:00:14.049000000      18 ******************
2018-09-15 09:00:15.049000000      18 ******************
2018-09-15 09:00:16.049000000      18 ******************
2018-09-15 09:00:17.049000000       4 ****
2018-09-15 09:00:18.049000000       6 ******
2018-09-15 09:00:19.049000000       6 ******
2018-09-15 09:00:20.049000000       6 ******

We can make our chart a little smarter by checking if the number of active sessions exceeds the CPU count, thus providing some of the missing context mentioned earlier. First, we’ll see what the cpu_count init parameter is for this database.

SQL> select value from v$parameter where name = 'cpu_count';
VALUE
8

Then we’ll adjust the chart logic to use a different character when there are more active sessions than CPUs to service them. Now the spike of 18 sessions is not only obvious that it’s larger than the rest of the activity but the exclamation characters highlight a potential problem.

SQL>   SELECT sample_time, cnt,
  2           CASE
  3             WHEN cnt <= 8 THEN RPAD('*', cnt * 2, '*')
  4             ELSE RPAD('!', cnt * 2, '!')
  5           END chart
  6      FROM (  SELECT sample_time, COUNT(*) cnt
  7                FROM gv$active_session_history
  8               WHERE sample_time >= TIMESTAMP '2018-09-15 09:00:00'
  9                 AND sample_time < TIMESTAMP '2018-09-15 09:00:21'
 10            GROUP BY sample_time)
 11  ORDER BY sample_time;
SAMPLE_TIME                       CNT CHART
2018-09-15 09:00:00.969000000       6 ************
2018-09-15 09:00:01.969000000       6 ************
2018-09-15 09:00:02.969000000       4 ********
2018-09-15 09:00:03.979000000       4 ********
2018-09-15 09:00:04.979000000       4 ********
2018-09-15 09:00:05.979000000       6 ************
2018-09-15 09:00:06.979000000       6 ************
2018-09-15 09:00:07.979000000       6 ************
2018-09-15 09:00:08.979000000       6 ************
2018-09-15 09:00:09.979000000       6 ************
2018-09-15 09:00:10.979000000       4 ********
2018-09-15 09:00:11.989000000       6 ************
2018-09-15 09:00:12.989000000      18 !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
2018-09-15 09:00:14.049000000      18 !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
2018-09-15 09:00:15.049000000      18 !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
2018-09-15 09:00:16.049000000      18 !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
2018-09-15 09:00:17.049000000       4 ********
2018-09-15 09:00:18.049000000       6 ************
2018-09-15 09:00:19.049000000       6 ************
2018-09-15 09:00:20.049000000       6 ************

20 rows selected.

If you use Oracle’s sqlcl tool then the formatting of the chart can be made even more vibrant with the use of color codes with ansiconsole formatting.

SQL>   SELECT sample_time, cnt,
  2           CASE
  3             WHEN cnt < 8 THEN '@|bg_green '
  4             WHEN cnt = 8 THEN '@|bg_yellow '
  5             ELSE '@|bg_red '
  6           END
  7           || RPAD('*', cnt, '*')
  8           || '|@'
  9               chart
 10      FROM (  SELECT sample_time, COUNT(*) cnt
 11                FROM gv$active_session_history
 12               WHERE sample_time >= TIMESTAMP '2018-09-15 09:00:00'
 13                 AND sample_time < TIMESTAMP '2018-09-15 09:00:21'
 14            GROUP BY sample_time)
 15  ORDER BY sample_time;

These queries provide the most basic form of investigation. They are useful, but at a one-second granularity the data volumes can become unwieldy for rapid diagnostic value and visualization. In my next articles in this series we’ll expand on these to show methods of querying ASH data across with greater detail or across larger time ranges while still maintaining a manageable set of results.