- What is ASH?
- AWR and Activity Types (this page)
- Average Active Sessions
- Graphical Visualizations
Previously I showed a few queries using (G)V$ACTIVE_SESSION_HISTORY, but only mentioned the DBA_HIST_ACTIVE_SESS_HISTORY view. Here I’ll extend the query patterns into this historical repository.
Where V$ACTIVE_SESSION_HISTORY is a once-per-second snapshot of sessions from memory and kept in memory, the DBA_HIST_ACTIVE_SESS_HISTORY data is persisted to disk. One out of every ten snapshots of V$ACTIVE_SESSION_HISTORY data is sampled and preserved in the Automatic Workload Repository (AWR.) Thus AWR session data consists of sampling of sampling. Remembering the partial nature of the data is important as it means you cannot use AWR as a reliable means of capturing all activity in a database and it can also affect the math of aggregations.
Before we get into the complications, lets examine some of the simple uses. First, we can use it much as shown in the previous article, but with less granularity.
SQL> SELECT sample_time, COUNT(*) cnt, RPAD('*', COUNT(*), '*') chart
2 FROM dba_hist_active_sess_history
3 WHERE sample_time >= TIMESTAMP '2018-09-20 10:03:00'
4 AND sample_time < TIMESTAMP '2018-09-20 10:07:00'
5 GROUP BY sample_time
6 ORDER BY sample_time;
SAMPLE_TIME CNT CHART
----------------------------- ------- ---------------
2018-09-20 10:03:07.754000000 9 *********
2018-09-20 10:03:17.764000000 10 **********
2018-09-20 10:03:27.784000000 9 *********
2018-09-20 10:03:37.794000000 8 ********
2018-09-20 10:03:47.863000000 8 ********
2018-09-20 10:03:57.873000000 7 *******
2018-09-20 10:04:07.893000000 10 **********
2018-09-20 10:04:17.913000000 7 *******
2018-09-20 10:04:27.923000000 6 ******
2018-09-20 10:04:37.933000000 9 *********
2018-09-20 10:04:48.003000000 12 ************
2018-09-20 10:04:58.023000000 8 ********
2018-09-20 10:05:08.033000000 8 ********
2018-09-20 10:05:18.043000000 7 *******
2018-09-20 10:05:28.063000000 12 ************
2018-09-20 10:05:38.073000000 11 ***********
2018-09-20 10:05:48.142000000 10 **********
2018-09-20 10:05:58.152000000 10 **********
2018-09-20 10:06:08.162000000 12 ************
2018-09-20 10:06:18.182000000 9 *********
2018-09-20 10:06:28.192000000 10 **********
2018-09-20 10:06:38.202000000 9 *********
2018-09-20 10:06:48.272000000 9 *********
2018-09-20 10:06:58.282000000 8 ********
As in my first article, these examples are from a single-instance system, so I don’t need to distinguish between different instance numbers.
The activity chart above is adequate to get a general idea of how much work is on your system; but it’s often more useful to determine what kind of work is happening. Are the sessions on cpu, actively trying to process something or are they waiting?
SQL> SELECT sample_time, session_state activity 2 FROM dba_hist_active_sess_history 3 WHERE sample_time >= TIMESTAMP '2018-09-20 10:03:00' AND sample_time < TIMESTAMP '2018-09-20 10:07:00' 4 ORDER BY sample_time, session_state; SAMPLE_TIME ACTIVITY -------------------------- -------- 20-SEP-18 10.03.07.754 AM ON CPU 20-SEP-18 10.03.07.754 AM ON CPU 20-SEP-18 10.03.07.754 AM WAITING 20-SEP-18 10.03.07.754 AM WAITING 20-SEP-18 10.03.07.754 AM WAITING 20-SEP-18 10.03.07.754 AM WAITING 20-SEP-18 10.03.07.754 AM WAITING 20-SEP-18 10.03.07.754 AM WAITING 20-SEP-18 10.03.07.754 AM WAITING 20-SEP-18 10.03.17.764 AM ON CPU 20-SEP-18 10.03.17.764 AM WAITING 20-SEP-18 10.03.17.764 AM WAITING 20-SEP-18 10.03.17.764 AM WAITING 20-SEP-18 10.03.17.764 AM WAITING 20-SEP-18 10.03.17.764 AM WAITING 20-SEP-18 10.03.17.764 AM WAITING 20-SEP-18 10.03.17.764 AM WAITING 20-SEP-18 10.03.17.764 AM WAITING 20-SEP-18 10.03.17.764 AM WAITING ...
Given these two pieces of information, you can produce charts similar to those show before, but indicating which type of activity is happening at each snapshot. Rather than simply using RPAD, we’ll use LISTAGG with a CASE statement to indicate which characters to concatenate into each line. Here I use an asterisk (*) for sessions on cpu, and a dash (-) for sessions waiting.
SQL> SELECT sample_time, 2 LISTAGG(CASE WHEN session_state = 'ON CPU' THEN '*' ELSE '-' END) 3 WITHIN GROUP (ORDER BY session_state) activity 4 FROM dba_hist_active_sess_history 5 WHERE sample_time > = TIMESTAMP '2018-09-20 10:03:00' 6 AND sample_time < TIMESTAMP '2018-09-20 10:07:00' 7 GROUP BY sample_time 8 ORDER BY sample_time; SAMPLE_TIME ACTIVITY 2018-09-20 10:03:07.754000000 **------- 2018-09-20 10:03:17.764000000 *--------- 2018-09-20 10:03:27.784000000 ****----- 2018-09-20 10:03:37.794000000 **------ 2018-09-20 10:03:47.863000000 ****---- 2018-09-20 10:03:57.873000000 **----- 2018-09-20 10:04:07.893000000 **-------- 2018-09-20 10:04:17.913000000 *------ 2018-09-20 10:04:27.923000000 ****-- 2018-09-20 10:04:37.933000000 ****----- 2018-09-20 10:04:48.003000000 *********--- 2018-09-20 10:04:58.023000000 **------ 2018-09-20 10:05:08.033000000 *****--- 2018-09-20 10:05:18.043000000 *****-- 2018-09-20 10:05:28.063000000 *******----- 2018-09-20 10:05:38.073000000 *********-- 2018-09-20 10:05:48.142000000 ****------ 2018-09-20 10:05:58.152000000 *********- 2018-09-20 10:06:08.162000000 **********-- 2018-09-20 10:06:18.182000000 ********- 2018-09-20 10:06:28.192000000 *********- 2018-09-20 10:06:38.202000000 ****----- 2018-09-20 10:06:48.272000000 *****---- 2018-09-20 10:06:58.282000000 ****----
ASH data has even more descriptive data about waiting sessions, including the classes and specific events for each session. However, it becomes harder to create a text-based chart with quickly identifiable symbols to capture all of the activity types.
Here, I generally limit the chart into 3 character types at most. One for CPU, one for some specific wait class or event, and the last for everything else. For example, one class I frequently want to track is User I/O, so I will add a plus-sign (+) for each session in that wait class. In this particular sampling window, we can see some I/O waits, but not a lot, indicating there probably wasn’t an I/O problem during that time.
SQL> SELECT sample_time, 2 LISTAGG(CASE WHEN session_state = 'ON CPU' THEN '*' 3 WHEN wait_class = 'User I/O' THEN '+' 4 ELSE '-' END) 5 WITHIN GROUP (ORDER BY session_state, 6 CASE WHEN wait_class = 'User I/O' THEN 1 ELSE 2 END) activity 7 FROM dba_hist_active_sess_history 8 WHERE sample_time > = TIMESTAMP '2018-09-20 10:03:00' 9 AND sample_time < TIMESTAMP '2018-09-20 10:07:00' 10 GROUP BY sample_time 11 ORDER BY sample_time; SAMPLE_TIME ACTIVITY 2018-09-20 10:03:07.754000000 **+------ 2018-09-20 10:03:17.764000000 *--------- 2018-09-20 10:03:27.784000000 ****+---- 2018-09-20 10:03:37.794000000 **------ 2018-09-20 10:03:47.863000000 ****---- 2018-09-20 10:03:57.873000000 **+---- 2018-09-20 10:04:07.893000000 **-------- 2018-09-20 10:04:17.913000000 *------ 2018-09-20 10:04:27.923000000 ****-- 2018-09-20 10:04:37.933000000 ****+---- 2018-09-20 10:04:48.003000000 *********++- 2018-09-20 10:04:58.023000000 **+----- 2018-09-20 10:05:08.033000000 *****++- 2018-09-20 10:05:18.043000000 *****-- 2018-09-20 10:05:28.063000000 *******----- 2018-09-20 10:05:38.073000000 *********-- 2018-09-20 10:05:48.142000000 ****------ 2018-09-20 10:05:58.152000000 *********- 2018-09-20 10:06:08.162000000 **********+- 2018-09-20 10:06:18.182000000 ********+ 2018-09-20 10:06:28.192000000 *********+ 2018-09-20 10:06:38.202000000 ****++--- 2018-09-20 10:06:48.272000000 *****---- 2018-09-20 10:06:58.282000000 ****+---
And finally, as in the first article, if you’re using Oracle’s SQLcl tool with ansiconsole formatting, you can add color to your chart to help highlight the different activity types. Here I’ll color the ON CPU sessions green and the User I/O sessions red
SELECT sample_time,
regexp_replace(regexp_replace(
LISTAGG(CASE WHEN session_state = 'ON CPU' THEN '*'
WHEN wait_class = 'User I/O' THEN '+'
ELSE '-' END)
WITHIN GROUP (ORDER BY session_state,
CASE WHEN wait_class = 'User I/O' THEN 1 ELSE 2 END)
,'(\*+)','@|bg_green \1|@')
,'(\++)','@|bg_red \1|@')
activity
FROM dba_hist_active_sess_history
WHERE sample_time > = TIMESTAMP '2018-09-20 10:03:00'
AND sample_time < TIMESTAMP '2018-09-20 10:07:00'
GROUP BY sample_time
ORDER BY sample_time;
All of the examples to this point have kept each snapshot time distinct from the others. Next time we’ll look at grouping snapshots across time. As always, questions and comments are welcome.