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 1000+ 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)

Is this answer out of date? If it is, please let us know via a Comment

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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

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