ASH raw data can be summarized across various dimensions, offering multiple perspectives for analyzing session activity. This allows you to observe workload distribution from different angles. Below are several SQL query examples demonstrating how to summarize ASH data across various dimensions.
Session State Breakdown by Category
The SQL query below calculates the percentage distribution of session activity across CPU, WAIT, and I/O categories for yesterday’s peak hour, specifically from 12:00 PM to 1:00 PM.
SELECT
totalAS,
TRUNC(oncpu / (oncpu + waiting) * 100, 2) AS "CPU%",
TRUNC(waiting / (oncpu + waiting) * 100, 2) AS "Waiting%",
TRUNC(userIO / waiting * 100, 2) AS "User IO%",
TRUNC(sysIO / waiting * 100, 2) AS "System IO%",
TRUNC((waiting - userIO - sysIO) / waiting * 100, 2) AS "Other%"
FROM (
SELECT
COUNT(*) AS totalAS,
SUM(DECODE(session_state, 'ON CPU', 1, 0)) AS oncpu,
SUM(DECODE(session_state, 'WAITING', 1, 0)) AS waiting,
SUM(DECODE(session_state, 'WAITING', DECODE(wait_class, 'User I/O', 1, 0))) AS userIO,
SUM(DECODE(session_state, 'WAITING', DECODE(wait_class, 'System I/O', 1, 0))) AS sysIO
FROM
DBA_HIST_ACTIVE_SESS_HISTORY
WHERE
sample_time BETWEEN TRUNC(SYSDATE) - 1 + INTERVAL '12' HOUR
AND TRUNC(SYSDATE) - 1 + INTERVAL '13' HOUR
) a;
An example of output:
TOTALAS CPU% Waiting% User IO% System IO% Other%
52 63.46 36.53 0 15.78 84.21
Session Origin Analysis
The SQL query below combines MACHINE,PROGRAM, MODULE and ACTION to offer a granular view of from where the sessions originated.
SELECT
NVL(s.machine, 'Unknown') AS machine,
NVL(s.program, 'Unknown') AS program,
NVL(s.module, 'Unknown') AS module,
NVL(s.action, 'Unknown') AS action,
COUNT(*) AS cnt,
TO_CHAR(100 * TRUNC(RATIO_TO_REPORT(COUNT(*)) OVER (), 4), 'FM990.99') || '%' AS "%"
FROM
v$active_session_history a
LEFT JOIN v$session s ON a.session_id = s.sid
WHERE
sample_time > SYSDATE - 30 / 60 / 24
GROUP BY
s.machine,
s.program,
s.module,
s.action
ORDER BY
COUNT(*) DESC
FETCH FIRST 10 ROWS ONLY;
A possible output can like below:
Session Activity Summary
The following query summarizes active sessions by session type, state, and SQL presence. It offers insights into how the workload is distributed between foreground/background sessions and SQL versus non-SQL operations:
SELECT
ash.session_type SESS_TYPE,
session_state STATUS,
DECODE(NVL(sql_id, '-1'), '-1', 'nonsql', 'sql') SQL_TYPE,
COUNT(DISTINCT TO_CHAR(session_id) || TO_CHAR(session_serial#)) SESS_CNT,
TO_CHAR(100 * TRUNC(RATIO_TO_REPORT(COUNT(*)) OVER (), 4), 'FM990.99') || '%' AS "%"
FROM v$active_session_history ash
WHERE sample_time > SYSDATE - 30 / 24 / 60
AND (
(ash.session_state = 'ON CPU')
OR (ash.session_type != 'BACKGROUND')
)
GROUP BY ash.session_type,
ash.session_state,
DECODE(NVL(sql_id, '-1'), '-1', 'nonsql', 'sql')
ORDER BY COUNT(*);
Example output:
SESS_TYPE STATUS SQL_TYPE SESS_CNT PERCENTAGE
BACKGROUND ON CPU sql 2 0.02
FOREGROUND WAITING sql 33 1.88
FOREGROUND ON CPU nonsql 34 4.27
BACKGROUND ON CPU nonsql 27 7.01
FOREGROUND WAITING nonsql 51 33.8
FOREGROUND ON CPU sql 36 53.03
6 rows selected.
With the techniques shown above, you can customize your own SQL queries to summarize activities across different dimensions to suit your specific needs.
Yuan Yao, An Oracle ACE's Blog 
Leave a comment