Simulating Oracle Performance Hub with Text-Based ASH Visualizations

The Performance Hub in Oracle Enterprise Manager Cloud Control is an integrated dashboard for performance monitoring and diagnostics. However, this useful graphical tools may not always be available due to access limitations or licensing restrictions. Fortunately, all graphical representations are inherently supported by underlying commands. Specifically, the Performance Hub is materialized by Active Session History (ASH), which can also be utilized to create a text-based version of the performance data.

The image below demonstrates a side-by-side comparison between the Activity tab of the Performance Hub and a text-based counterpart I generated by querying raw ASH data. This layout not only facilitates comparison but also serves to validate the accuracy of the text-based method in representing session activity patterns.

In this text-based session activity chart, different types of activity are symbolized using characters:

+ for CPU usage
= for User I/O waits
- for other wait events

As observed, the peaks and troughs in the text representation closely align with those in the graphical interface, confirming the fidelity of the approach.

The following SQL script builds the character-based visual representation of session activity by analyzing recent ASH data. It segments activity into CPU usage, User I/O, and other waits, and renders a proportional bar graph using text symbols. This makes it possible to monitor system load trends even in purely terminal-based environments.

-- Author: Yuan Yao
-- Dated: Aug 2024
set pagesize 1000
define analysis_hours = 1
define max_count = 3700
define sampling_interval_secs = 100
define graph_bar_count = 50

-- Description: This SQL script generates a simulated graphical display of the performance hub using a text-based graph.

SELECT to_char(to_date(tday, 'YYYYMMDD') + (tmod * &sampling_interval_secs / 86400), 'DD-MON HH24:MI:SS') sample_time,
       active_sessions,
       cpu,
       user_io,
       other_waits,
       substr(
           rpad('+', round((cpu * &graph_bar_count) / &max_count), '+') ||
           rpad('=', round((user_io * &graph_bar_count) / &max_count), '=') ||
           rpad('-', round((other_waits * &graph_bar_count) / &max_count), '-') ||
           rpad(' ', &graph_bar_count - round((cpu * &graph_bar_count) / &max_count) - round((user_io * &graph_bar_count) / &max_count) - round((other_waits * &graph_bar_count) / &max_count), ' '),
           1, &graph_bar_count
       ) visualized_activities
FROM (
    SELECT to_char(sample_time, 'YYYYMMDD') tday,
           trunc(to_char(sample_time, 'SSSSS') / &sampling_interval_secs) tmod,
           count(*) active_sessions,
           sum(decode(session_state, 'ON CPU', 1, 0)) cpu,
           sum(decode(wait_class, 'User I/O', 1, 0)) user_io,
           sum(decode(wait_class, 'User I/O', 0, NULL, 0, 1)) other_waits
    FROM v$active_session_history
    where sample_time > sysdate - &analysis_hours / 24
    GROUP BY trunc(to_char(sample_time, 'SSSSS') / &sampling_interval_secs), to_char(sample_time, 'YYYYMMDD')
) ash
ORDER BY to_date(tday, 'YYYYMMDD') + (tmod * &sampling_interval_secs / 86400);

The following is an example of output:

old  1: SELECT to_char(to_date(tday, 'YYYYMMDD') + (tmod * &sampling_interval_secs / 86400), 'DD-MON HH24:MI:SS') sample_time,
new  1: SELECT to_char(to_date(tday, 'YYYYMMDD') + (tmod * 100 / 86400), 'DD-MON HH24:MI:SS') sample_time,
old  7:            rpad('+', round((cpu * &graph_bar_count) / &max_count), '+') ||
new  7:            rpad('+', round((cpu * 50) / 3700), '+') ||
old  8:            rpad('=', round((user_io * &graph_bar_count) / &max_count), '=') ||
new  8:            rpad('=', round((user_io * 50) / 3700), '=') ||
old  9:            rpad('-', round((other_waits * &graph_bar_count) / &max_count), '-') ||
new  9:            rpad('-', round((other_waits * 50) / 3700), '-') ||
old  10:            rpad(' ', &graph_bar_count - round((cpu * &graph_bar_count) / &max_count) - round((user_io * &graph_bar_count) / &max_count) - round((other_waits * &graph_bar_count) / &max_count), ' '),
new  10:            rpad(' ', 50 - round((cpu * 50) / 3700) - round((user_io * 50) / 3700) - round((other_waits * 50) / 3700), ' '),
old  11:            1, &graph_bar_count
new  11:            1, 50
old  15:            trunc(to_char(sample_time, 'SSSSS') / &sampling_interval_secs) tmod,
new  15:            trunc(to_char(sample_time, 'SSSSS') / 100) tmod,
old  23:     GROUP BY trunc(to_char(sample_time, 'SSSSS') / &sampling_interval_secs), to_char(sample_time, 'YYYYMMDD')
new  23:     GROUP BY trunc(to_char(sample_time, 'SSSSS') / 100), to_char(sample_time, 'YYYYMMDD')
old  25: ORDER BY to_date(tday, 'YYYYMMDD') + (tmod * &sampling_interval_secs / 86400)
new  25: ORDER BY to_date(tday, 'YYYYMMDD') + (tmod * 100 / 86400)
SAMPLE_TIME         ACTIVE_SESSIONS    CPU   USER_IO   OTHER_WAITS VISUALIZED_ACTIVITIES
20-AUG 15:06:40                  18     13         0             5
20-AUG 15:08:20                  15      6         0             9
20-AUG 15:10:00                  17     12         0             5
20-AUG 15:11:40                  12     11         0             1
20-AUG 15:13:20                 819    502       114           203 +++++++==---
20-AUG 15:15:00                2802   1659        79          1064 ++++++++++++++++++++++=--------------
20-AUG 15:16:40                1323    567        58           698 ++++++++=---------
20-AUG 15:18:20                1034    496       123           415 +++++++==------
20-AUG 15:20:00                1721   1038        99           584 ++++++++++++++=--------
20-AUG 15:21:40                1753   1044        47           662 ++++++++++++++=---------
20-AUG 15:23:20                1694   1145        25           524 +++++++++++++++-------
20-AUG 15:25:00                1706   1111        23           572 +++++++++++++++--------
20-AUG 15:26:40                  94     35        22            37 -
20-AUG 15:28:20                2914   2174       618           122 +++++++++++++++++++++++++++++========--
20-AUG 15:30:00                1826   1334       433            59 ++++++++++++++++++======-
20-AUG 15:31:40                1226   1162        47            17 ++++++++++++++++=
20-AUG 15:33:20                1135   1086        41             8 +++++++++++++++=
20-AUG 15:35:00                 914    867        27            20 ++++++++++++
20-AUG 15:36:40                  38     22         0            16
20-AUG 15:38:20                  32     24         0             8
20-AUG 15:40:00                1554    984       112           458 +++++++++++++==------
20-AUG 15:41:40                3427   2095        63          1269 ++++++++++++++++++++++++++++=-----------------
20-AUG 15:43:20                3521   1792        81          1648 ++++++++++++++++++++++++=----------------------
20-AUG 15:45:00                3465   1889        83          1493 ++++++++++++++++++++++++++=--------------------
20-AUG 15:46:40                2553   1749        37           767 ++++++++++++++++++++++++=----------
20-AUG 15:48:20                  46     30         0            16
20-AUG 15:50:00                 253     48        15           190 +---
20-AUG 15:51:40                 553    353        74           126 +++++=--
20-AUG 15:53:20                3504   2021        80          1403 +++++++++++++++++++++++++++=-------------------
20-AUG 15:55:00                3237   2165        30          1042 +++++++++++++++++++++++++++++--------------
20-AUG 15:56:40                3631   1837        74          1720 +++++++++++++++++++++++++=-----------------------
20-AUG 15:58:20                3572   1885        59          1628 +++++++++++++++++++++++++=----------------------
20-AUG 16:00:00                3580   2027       162          1391 +++++++++++++++++++++++++++==-------------------
20-AUG 16:01:40                3655   1721       100          1834 +++++++++++++++++++++++=-------------------------
20-AUG 16:03:20                3445   2053        46          1346 ++++++++++++++++++++++++++++=------------------
20-AUG 16:05:00                3612   1832        59          1721 +++++++++++++++++++++++++=-----------------------
20-AUG 16:06:40                1278    801        11           466 +++++++++++------

37 rows selected.

2 responses to “Simulating Oracle Performance Hub with Text-Based ASH Visualizations”

myjavaworks

26th Aug 2024

HI, In the above output, how can i read CPU details? CPU values 13,6,12,11 etc.. means what? is it seconds?

LikeLike

1. YuanOracleACE
  
  26th Aug 2024
  
  When the session_state value in v$active_session_history is “ON CPU,” it indicates that the session is actively using the CPU. Since ASH samples every second, the sum of count(*) for these samples represents the total number of seconds the session has been using the CPU.
  
  LikeLike