Skip to Main Content
  • Questions
  • What is the SQL statement for the Active Sessions Per Activity Class from EMCC 13.5?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Quanwen.

Asked: November 10, 2021 - 9:22 am UTC

Last updated: December 01, 2021 - 2:37 am UTC

Version: 11.2.0.4.0

Viewed 100+ times

You Asked

Hey Connor :-),

I've seen this pretty nice graph about "Active Sessions Per Activity Class" from EMCC 13.5, take a looking at the following 2 number of screenshots.

https://quanwenzhao.files.wordpress.com/2021/11/active-sessions-per-activity-class_1.png

https://quanwenzhao.files.wordpress.com/2021/11/active-sessions-per-activity-class_2.png

Hence I've spent a couple of time writing the similar SQL query, here's my code:

-- Active Sessions (in ASH) Per Activity Class from EMCC in Last 1 Hour.

SET LINESIZE 200
SET PAGESIZE 200

COLUMN sample_time    FORMAT a19
COLUMN activity_class FORMAT a15

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

SELECT TRUNC(CAST(sample_time AS DATE), 'mi') sample_time
  -- , CAST(sample_time AS DATE) sample_time
     , DECODE(wait_class, 'User I/O', 'User I/O', NULL, 'CPU', 'Wait') activity_class
  -- , time_waited consume_time
     , ROUND(COUNT(*)/6e1, 4) active_sessions
FROM v$active_session_history
WHERE session_type = 'FOREGROUND'
AND   (wait_class <> 'Idle' OR wait_class IS NULL)
AND   CAST(sample_time AS DATE) >= SYSDATE - 1/24
GROUP BY TRUNC(CAST(sample_time AS DATE), 'mi')
       , DECODE(wait_class, 'User I/O', 'User I/O', NULL, 'CPU', 'Wait')
ORDER BY activity_class
       , sample_time
;


The corresponding query result is as below,

SAMPLE_TIME         ACTIVITY_CLASS  ACTIVE_SESSIONS
------------------- --------------- ---------------
2021-11-10 15:50:00 CPU                       .4167
2021-11-10 15:51:00 CPU                          .3
2021-11-10 15:52:00 CPU                       .4667
2021-11-10 15:53:00 CPU                         .55
2021-11-10 15:54:00 CPU                      1.0333
2021-11-10 15:55:00 CPU                         1.2
2021-11-10 15:56:00 CPU                       .3833
2021-11-10 15:57:00 CPU                      1.6667
2021-11-10 15:58:00 CPU                      1.4333
2021-11-10 15:59:00 CPU                         3.3
2021-11-10 16:00:00 CPU                         1.6
2021-11-10 16:01:00 CPU                      2.0167
2021-11-10 16:02:00 CPU                      1.3667
2021-11-10 16:03:00 CPU                      1.9167
2021-11-10 16:04:00 CPU                      3.2167
2021-11-10 16:05:00 CPU                      3.0833
2021-11-10 16:06:00 CPU                         5.1
2021-11-10 16:07:00 CPU                      1.5667
2021-11-10 16:08:00 CPU                      1.3333
2021-11-10 16:09:00 CPU                          .8
2021-11-10 16:10:00 CPU                        1.25
2021-11-10 16:11:00 CPU                      1.7667
2021-11-10 16:12:00 CPU                      2.3833
2021-11-10 16:13:00 CPU                      1.2833
2021-11-10 16:14:00 CPU                         .35
2021-11-10 16:15:00 CPU                         1.3
2021-11-10 16:16:00 CPU                       .3333
2021-11-10 16:17:00 CPU                       .2167
2021-11-10 16:18:00 CPU                      3.4667
2021-11-10 16:19:00 CPU                       .3833
2021-11-10 16:20:00 CPU                       .3667
2021-11-10 16:21:00 CPU                         .25
2021-11-10 16:22:00 CPU                       .7167
2021-11-10 16:23:00 CPU                         3.8
2021-11-10 16:24:00 CPU                        7.25
2021-11-10 16:25:00 CPU                       19.55
2021-11-10 16:26:00 CPU                     17.0667
2021-11-10 16:27:00 CPU                        13.9
2021-11-10 16:28:00 CPU                      1.1167
2021-11-10 16:29:00 CPU                       .4333
2021-11-10 16:30:00 CPU                      4.0333
2021-11-10 16:31:00 CPU                      3.7833
2021-11-10 16:32:00 CPU                      6.3167
2021-11-10 16:33:00 CPU                      6.0833
2021-11-10 16:34:00 CPU                        4.95
2021-11-10 16:35:00 CPU                      1.2167
2021-11-10 16:36:00 CPU                         4.2
2021-11-10 16:37:00 CPU                     12.2667
2021-11-10 16:38:00 CPU                     14.4333
2021-11-10 16:39:00 CPU                     12.5833
2021-11-10 16:40:00 CPU                      7.7167
2021-11-10 16:41:00 CPU                      6.0667
2021-11-10 16:42:00 CPU                      4.0167
2021-11-10 16:43:00 CPU                      2.8833
2021-11-10 16:44:00 CPU                      1.2333
2021-11-10 16:45:00 CPU                      1.3333
2021-11-10 16:46:00 CPU                      1.1667
2021-11-10 16:47:00 CPU                      1.4833
2021-11-10 16:48:00 CPU                      3.4167
2021-11-10 16:49:00 CPU                         1.4
2021-11-10 16:50:00 CPU                      1.6333
2021-11-10 16:06:00 User I/O                  .0333
2021-11-10 16:11:00 User I/O                  .0167
2021-11-10 15:50:00 Wait                      .0167
2021-11-10 15:51:00 Wait                      .5167
2021-11-10 15:52:00 Wait                         .5
2021-11-10 15:53:00 Wait                      .4167
2021-11-10 15:54:00 Wait                         .6
2021-11-10 15:55:00 Wait                         .8
2021-11-10 15:56:00 Wait                         .7
2021-11-10 15:57:00 Wait                      .4167
2021-11-10 15:58:00 Wait                        .85
2021-11-10 15:59:00 Wait                      .3667
2021-11-10 16:00:00 Wait                      .6167
2021-11-10 16:01:00 Wait                      .5167
2021-11-10 16:02:00 Wait                      .8833
2021-11-10 16:03:00 Wait                      .2333
2021-11-10 16:04:00 Wait                      .6667
2021-11-10 16:05:00 Wait                      .2333
2021-11-10 16:06:00 Wait                     4.4167
2021-11-10 16:07:00 Wait                      .7167
2021-11-10 16:08:00 Wait                     1.0833
2021-11-10 16:09:00 Wait                      .4167
2021-11-10 16:10:00 Wait                      .7333
2021-11-10 16:11:00 Wait                      .4167
2021-11-10 16:12:00 Wait                       1.35
2021-11-10 16:13:00 Wait                        .25
2021-11-10 16:14:00 Wait                      .3833
2021-11-10 16:15:00 Wait                      .2667
2021-11-10 16:16:00 Wait                      .3333
2021-11-10 16:17:00 Wait                      .1667
2021-11-10 16:18:00 Wait                        .35
2021-11-10 16:19:00 Wait                      .3167
2021-11-10 16:20:00 Wait                       1.05
2021-11-10 16:21:00 Wait                        .35
2021-11-10 16:22:00 Wait                     1.0833
2021-11-10 16:23:00 Wait                      .3667
2021-11-10 16:24:00 Wait                        1.3
2021-11-10 16:25:00 Wait                      .2167
2021-11-10 16:26:00 Wait                      .3167
2021-11-10 16:27:00 Wait                      .5833
2021-11-10 16:28:00 Wait                      .1333
2021-11-10 16:29:00 Wait                      .0667
2021-11-10 16:30:00 Wait                        .45
2021-11-10 16:31:00 Wait                         .8
2021-11-10 16:32:00 Wait                        1.5
2021-11-10 16:33:00 Wait                         .5
2021-11-10 16:34:00 Wait                        .35
2021-11-10 16:35:00 Wait                      .2833
2021-11-10 16:36:00 Wait                      .4833
2021-11-10 16:37:00 Wait                      .2333
2021-11-10 16:38:00 Wait                      .3167
2021-11-10 16:39:00 Wait                      .0667
2021-11-10 16:40:00 Wait                      .2167
2021-11-10 16:41:00 Wait                      .2667
2021-11-10 16:42:00 Wait                      .1667
2021-11-10 16:43:00 Wait                      .9333
2021-11-10 16:44:00 Wait                         .4
2021-11-10 16:45:00 Wait                         .4
2021-11-10 16:46:00 Wait                        .35
2021-11-10 16:47:00 Wait                         .7
2021-11-10 16:48:00 Wait                      .2833
2021-11-10 16:49:00 Wait                      .4167
2021-11-10 16:50:00 Wait                      .0667

124 rows selected.


As you can see from the previous query result the "Wait" is "4.4167" (and "User I/O" is ".0333") on "2021-11-10 16:06:00" but it's not the same value ("Wait" is "4.890" and "User I/O" is "0.02560") from my prior screenshots between "Wait" and "User I/O" on the same time "4:06 PM".

So my SQL query is not accurate I think at least, could you help me correcting it? Thanks in advance!

Best Regards
Quanwen Zhao

and Connor said...

ROUND(COUNT(*)/6e1, 4) active_sessions is historical because we simply counted the rows for it to be the number of seconds.

Try this instead - this column arrived in 12.2 I think

SUM(usecs_per_row)/1e6

Rating

  (4 ratings)

Comments

Still having a big difference between the value showing from EMCC and my SQL query.

Quanwen Zhao, November 12, 2021 - 8:22 am UTC

Now I have observed the same graph "Active Sessions" from Activity Class on 19.3 (the initial version is 11.2.0.4.0) but still having a big difference between the value showing from EMCC and my SQL query when replacing "ROUND(COUNT(*)/6e1, 4) " with "ROUND(SUM(usecs_per_row)/1e6/6e1, 4)". Such as, "2021-11-12 15:42:00 CPU .0167" on my SQL query and "2021-11-12 03:42 PM 0.4226 CPU" from EMCC.

Here's the SQL query result.

SAMPLE_TIME         ACTIVITY_CLASS  ACTIVE_SESSIONS
------------------- --------------- ---------------
2021-11-12 15:13:00 CPU                       .0502
2021-11-12 15:14:00 CPU                       .0503
2021-11-12 15:16:00 CPU                       .0838
2021-11-12 15:17:00 CPU                       .1675
2021-11-12 15:18:00 CPU                       .0837
2021-11-12 15:19:00 CPU                       .2009
2021-11-12 15:20:00 CPU                        .201
2021-11-12 15:21:00 CPU                       .3687
2021-11-12 15:22:00 CPU                       .1675
2021-11-12 15:23:00 CPU                       .2177
2021-11-12 15:24:00 CPU                       .2345
2021-11-12 15:25:00 CPU                       .1843
2021-11-12 15:26:00 CPU                       .2849
2021-11-12 15:27:00 CPU                       .2345
2021-11-12 15:28:00 CPU                       .1843
2021-11-12 15:29:00 CPU                       .2513
2021-11-12 15:30:00 CPU                       .2177
2021-11-12 15:31:00 CPU                       .2682
2021-11-12 15:32:00 CPU                       .1172
2021-11-12 15:36:00 CPU                       .0503
2021-11-12 15:38:00 CPU                       .0167
2021-11-12 15:41:00 CPU                       .4355
2021-11-12 15:42:00 CPU                       .0167
2021-11-12 15:45:00 CPU                       .0335
2021-11-12 15:46:00 CPU                       .0167
2021-11-12 15:47:00 CPU                       .0335
2021-11-12 15:48:00 CPU                       .0167
2021-11-12 15:49:00 CPU                       .0335
2021-11-12 15:51:00 CPU                       .1339
2021-11-12 15:53:00 CPU                       .0168
2021-11-12 15:54:00 CPU                       .0167
2021-11-12 15:56:00 CPU                        .067
2021-11-12 15:58:00 CPU                       .0502
2021-11-12 15:59:00 CPU                       .0335
2021-11-12 16:01:00 CPU                        .067
2021-11-12 16:03:00 CPU                       .0167
2021-11-12 16:04:00 CPU                       .0335
2021-11-12 16:05:00 CPU                       .0335
2021-11-12 16:06:00 CPU                       .0502
2021-11-12 16:07:00 CPU                       .0335
2021-11-12 16:08:00 CPU                       .0502
2021-11-12 16:10:00 CPU                       .0503
2021-11-12 16:11:00 CPU                       .1005
2021-11-12 15:21:00 Wait                      .0167
2021-11-12 15:22:00 Wait                      .0335
2021-11-12 15:32:00 Wait                      .0838
2021-11-12 15:48:00 Wait                      .0335
2021-11-12 15:51:00 Wait                      .0167
2021-11-12 16:11:00 Wait                      .1507

49 rows selected.


Here's a screenshot from EMCC, https://quanwenzhao.files.wordpress.com/2021/11/active-sessions-per-activity-class_3_on_19.3-1.png

Best Regards
Quanwen Zhao
Connor McDonald
November 22, 2021 - 2:09 am UTC

Spoke to the EM team - its not just a SQL, there are other scaling factors plus other bits and pieces that are going on.

So you will be able to get close, but not 100%

Which product manager of EM?

Quanwen Zhao, November 23, 2021 - 8:22 am UTC

Which top-notch product manager of EM, Connor? And how to contact he/she for me from Twitter, LinkedIn or other social media?

Or might I need spend extra time trying to explore other views (not v$active_session_history) - v$waitclassmetric_history and v$system_wait_class.

Best Regards
Quanwen Zhao

It's fairly precise to EMCC 13.5 by using this SQL query.

Quanwen Zhao, November 28, 2021 - 9:46 am UTC

My guess is right, Connor! About the "Active Sessions Per Activity Class" Graph from EMCC 13.5 should not be checking the "v$active_session_history", which should check "v$sysmetric_history" (for the legend 'CPU'), "v$waitclassmetric_history" and "v$system_wait_class". Here's my new query:

-- Active Sessions Per Activity Class (CPU, User I/O and Wait) in Last 1 Hour.

SET LINESIZE 200
SET PAGESIZE 200

COLUMN sample_time     FORMAT a11
COLUMN metric_name     FORMAT a11
COLUMN active_sessions FORMAT 999,999.9999

WITH
cpu AS
(
  SELECT TO_CHAR(end_time, 'hh24:mi:ss') sample_time
       , DECODE(metric_name, 'CPU Usage Per Sec', 'CPU') metric_name
       , ROUND(value/1e2, 4) active_sessions
  FROM v$sysmetric_history
  WHERE metric_name = 'CPU Usage Per Sec'
  AND   group_id = 2
  AND   end_time >= SYSDATE - INTERVAL '60' MINUTE
  ORDER BY sample_time
),
user_io AS
(
  SELECT TO_CHAR(wcmh.end_time, 'hh24:mi:ss') sample_time
       , swc.wait_class metric_name
       , ROUND(wcmh.time_waited/wcmh.intsize_csec, 4) active_sessions
  FROM v$waitclassmetric_history wcmh
     , v$system_wait_class swc
  WHERE wcmh.wait_class_id = swc.wait_class_id
  AND   swc.wait_class = 'User I/O'
  AND   wcmh.end_time >= SYSDATE - INTERVAL '60' MINUTE
  ORDER BY sample_time
),
wait AS
(
  SELECT TO_CHAR(wcmh.end_time, 'hh24:mi:ss') sample_time
       , 'Wait' metric_name
       , SUM(ROUND(wcmh.time_waited/wcmh.intsize_csec, 4)) active_sessions
  FROM v$waitclassmetric_history wcmh
     , v$system_wait_class swc
  WHERE wcmh.wait_class_id = swc.wait_class_id
  AND   (swc.wait_class NOT IN ('Idle', 'User I/O'))
  AND   wcmh.end_time >= SYSDATE - INTERVAL '60' MINUTE
  GROUP BY TO_CHAR(wcmh.end_time, 'hh24:mi:ss')
  ORDER BY sample_time
)
SELECT * FROM cpu
UNION ALL
SELECT * FROM user_io
UNION ALL
SELECT * FROM wait
;


The query result (omitted the rest sample_time and just left 17:07:17) is as follows:

SAMPLE_TIME METRIC_NAME ACTIVE_SESSIONS
----------- ----------- ---------------
......
17:07:17    CPU                   .6130
......
17:07:17    User I/O              .0087
......
17:07:17    Wait                 1.4924
......

180 rows selected.


Next I use the previous query result to do a comparison with the following screenshots for "Wait" and "User I/O".

https://quanwenzhao.files.wordpress.com/2021/11/wait_active_sessions_per_activity_class.png

https://quanwenzhao.files.wordpress.com/2021/11/user_io_active_sessions_per_activity_class.png

Very great! I think the product manager of EM is quite possible to use the SQL query to get the graph.

Best Regards
Quanwen Zhao
Connor McDonald
December 01, 2021 - 2:37 am UTC

nice investigative work.

Of course I can't really confirm or deny whether thats exactly where we're using :-)

The SQL queries about most of graphs from EMCC is the view with METRIC.

Quanwen Zhao, December 02, 2021 - 6:41 am UTC

Haha, Connor! I think it so.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.