暂无图片
暂无图片
8
暂无图片
暂无图片
暂无图片

可视化Oracle性能图表之“平均活动会话&逻辑CPU”篇

原创 赵全文 2021-11-08
3640

如果您读了上周五我在墨天轮发表的原创博客文章 - 可视化Oracle性能图表之“平均活动会话”篇,我想您对Oracle的平均活动会话一定有所了解,尤其是我从八个维度展现的可视化图表。在那篇文章的最后我曾提到我的下一篇文章将是可视化Oracle性能图表之“平均活动会话&逻辑CPU”篇。那么,请让我娓娓道来。

同理,我们仍然是从八个维度来依次阐述。如果您觉得说来话长,那我们就长话短说。哈哈......

维度目录列表

实时的平均活动会话和逻辑CPU数目

话不多说,直接上代码:
-- Average Active Sessions & Logic CPUs in Real Time.

SET LINESIZE 200
SET PAGESIZE 200

COLUMN snap_date_time FORMAT a19
COLUMN stat_name      FORMAT a25
COLUMN stat_value     FORMAT 999,999.99

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

WITH aas AS
(
  SELECT TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi:ss') snap_date_time
       , metric_name
       , ROUND(value, 2) aas
  FROM v$sysmetric_history
  WHERE metric_name = 'Average Active Sessions'
  AND   group_id = 2
),
oscpu AS
(
  SELECT stat_name
       , value
  FROM v$osstat
  WHERE stat_name = 'NUM_CPUS'
)
SELECT s.snap_date_time                                         -- the group column
     , DECODE(u.stat_name, 'NUM_CPUS', 'Logic CPUs') stat_name  -- the series column
     , u.value stat_value                                       -- the value column
FROM oscpu u  -- "oscpu" has only a row, so using "oscpu" and "aas" to join each other to acquire the column "snap_date_time" of "aas".
   , aas s
UNION ALL
SELECT snap_date_time         -- the group column
     , metric_name stat_name  -- the series column
     , aas stat_value         -- the value column
FROM aas
ORDER BY stat_name DESC
       , snap_date_time
;

再上图,


[返回维度目录列表]

最近24小时的平均活动会话和逻辑CPU数目

SQL代码如下:

-- Average Active Sessions & Logic CPUs in Last 24 Hours.

SET LINESIZE 200
SET PAGESIZE 200

COLUMN snap_date_time FORMAT a19
COLUMN stat_name      FORMAT a25
COLUMN stat_value     FORMAT 999,999.99

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

WITH aas AS
(
  SELECT snap_id
       , dbid
       , instance_number
       , TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi:ss') snap_date_time
       , metric_name
       , ROUND(average, 2) aas
  FROM dba_hist_sysmetric_summary
  WHERE metric_name = 'Average Active Sessions'
  AND   end_time >= SYSDATE - 1
),
oscpu AS
(
  SELECT snap_id
       , dbid
       , instance_number
       , stat_name
       , value
  FROM dba_hist_osstat
  WHERE stat_name = 'NUM_CPUS'
)
SELECT s.snap_date_time                                         -- the group column
     , DECODE(u.stat_name, 'NUM_CPUS', 'Logic CPUs') stat_name  -- the series column
     , u.value stat_value                                       -- the value column
FROM aas s
   , oscpu u
WHERE s.snap_id = u.snap_id
AND   s.dbid = u.dbid
AND   s.instance_number = u.instance_number
UNION ALL
SELECT snap_date_time         -- the group column
     , metric_name stat_name  -- the series column
     , aas stat_value         -- the value column
FROM aas
ORDER BY stat_name DESC
       , snap_date_time
;

可视化的图表为:


[返回维度目录列表]

最近7天的平均活动会话和逻辑CPU数目(按每小时间隔)

SQL代码是这样的:

-- Average Active Sessions & Logic CPUs in Last 7 Days (interval by each hour).

SET LINESIZE 200
SET PAGESIZE 200

COLUMN snap_date_time FORMAT a19
COLUMN stat_name      FORMAT a25
COLUMN stat_value     FORMAT 999,999.99

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

WITH aas AS
(
  SELECT snap_id
       , dbid
       , instance_number
       , TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi:ss') snap_date_time
       , metric_name
       , ROUND(average, 2) aas
  FROM dba_hist_sysmetric_summary
  WHERE metric_name = 'Average Active Sessions'
  AND   end_time >= SYSDATE - 6
),
oscpu AS
(
  SELECT snap_id
       , dbid
       , instance_number
       , stat_name
       , value
  FROM dba_hist_osstat
  WHERE stat_name = 'NUM_CPUS'
)
SELECT s.snap_date_time                                         -- the group column
     , DECODE(u.stat_name, 'NUM_CPUS', 'Logic CPUs') stat_name  -- the series column
     , u.value stat_value                                       -- the value column
FROM aas s
   , oscpu u
WHERE s.snap_id = u.snap_id
AND   s.dbid = u.dbid
AND   s.instance_number = u.instance_number
UNION ALL
SELECT snap_date_time         -- the group column
     , metric_name stat_name  -- the series column
     , aas stat_value         -- the value column
FROM aas
ORDER BY stat_name DESC
       , snap_date_time
;

那么,对应的图表如下所示:


[返回维度目录列表]

最近7天的平均活动会话和逻辑CPU数目(按每天间隔)

具体的SQL查询代码如下:

-- Average Active Sessions & Logic CPUs in Last 7 Days (interval by each day).

SET LINESIZE 200
SET PAGESIZE 200

COLUMN snap_date  FORMAT a12
COLUMN stat_name  FORMAT a25
COLUMN stat_value FORMAT 999,999.99

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

WITH aas_per_hour AS
(
  SELECT snap_id
       , dbid
       , instance_number
       , TO_CHAR(end_time, 'yyyy-mm-dd') snap_date
       , metric_name
       , average
  FROM dba_hist_sysmetric_summary
  WHERE metric_name = 'Average Active Sessions'
  AND   end_time >= SYSDATE - 6
),
aas AS
(
  SELECT snap_date                                    -- the group column
       , metric_name                                  -- the series column
       , ROUND(SUM(average)/COUNT(snap_date), 2) aas  -- the value column
  FROM aas_per_hour
  GROUP BY snap_date
         , metric_name
),
oscpu AS
(
  SELECT snap_id
       , dbid
       , instance_number
       , stat_name
       , value
  FROM dba_hist_osstat
  WHERE stat_name = 'NUM_CPUS'
)
SELECT DISTINCT s.snap_date                                     -- the group column
     , DECODE(u.stat_name, 'NUM_CPUS', 'Logic CPUs') stat_name  -- the series column
     , u.value stat_value                                       -- the value column
FROM aas_per_hour s
   , oscpu u
WHERE s.snap_id = u.snap_id
AND   s.dbid = u.dbid
AND   s.instance_number = u.instance_number
UNION ALL
SELECT snap_date              -- the group column
     , metric_name stat_name  -- the series column
     , aas stat_value         -- the value column
FROM aas
ORDER BY stat_name DESC
       , snap_date
;

对应的可视化图表见下图:


[返回维度目录列表]

最近31天的平均活动会话和逻辑CPU数目(按每小时间隔)

完整的SQL代码详见下面的查询语句:
-- Average Active Sessions & Logic CPUs in Last 31 Days (interval by each hour).

SET LINESIZE 200
SET PAGESIZE 200

COLUMN snap_date_time FORMAT a19
COLUMN stat_name      FORMAT a25
COLUMN stat_value     FORMAT 999,999.99

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

WITH aas AS
(
  SELECT snap_id
       , dbid
       , instance_number
       , TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi:ss') snap_date_time
       , metric_name
       , ROUND(average, 2) aas
  FROM dba_hist_sysmetric_summary
  WHERE metric_name = 'Average Active Sessions'
  AND   end_time >= SYSDATE - 30
),
oscpu AS
(
  SELECT snap_id
       , dbid
       , instance_number
       , stat_name
       , value
  FROM dba_hist_osstat
  WHERE stat_name = 'NUM_CPUS'
)
SELECT s.snap_date_time                                         -- the group column
     , DECODE(u.stat_name, 'NUM_CPUS', 'Logic CPUs') stat_name  -- the series column
     , u.value stat_value                                       -- the value column
FROM aas s
   , oscpu u
WHERE s.snap_id = u.snap_id
AND   s.dbid = u.dbid
AND   s.instance_number = u.instance_number
UNION ALL
SELECT snap_date_time         -- the group column
     , metric_name stat_name  -- the series column
     , aas stat_value         -- the value column
FROM aas
ORDER BY stat_name DESC
       , snap_date_time
;

同理,对应的可视化图表如下图所示:


[返回维度目录列表]

最近31天的平均活动会话和逻辑CPU数目(按每天间隔)

SQL查询语句如下:

-- Average Active Sessions & Logic CPUs in Last 31 Days (interval by each day).

SET LINESIZE 200
SET PAGESIZE 200

COLUMN snap_date  FORMAT a12
COLUMN stat_name  FORMAT a25
COLUMN stat_value FORMAT 999,999.99

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

WITH aas_per_hour AS
(
  SELECT snap_id
       , dbid
       , instance_number
       , TO_CHAR(end_time, 'yyyy-mm-dd') snap_date
       , metric_name
       , average
  FROM dba_hist_sysmetric_summary
  WHERE metric_name = 'Average Active Sessions'
  AND   end_time >= SYSDATE - 30
),
aas AS
(
  SELECT snap_date                                    -- the group column
       , metric_name                                  -- the series column
       , ROUND(SUM(average)/COUNT(snap_date), 2) aas  -- the value column
  FROM aas_per_hour
  GROUP BY snap_date
         , metric_name
),
oscpu AS
(
  SELECT snap_id
       , dbid
       , instance_number
       , stat_name
       , value
  FROM dba_hist_osstat
  WHERE stat_name = 'NUM_CPUS'
)
SELECT DISTINCT s.snap_date                                     -- the group column
     , DECODE(u.stat_name, 'NUM_CPUS', 'Logic CPUs') stat_name  -- the series column
     , u.value stat_value                                       -- the value column
FROM aas_per_hour s
   , oscpu u
WHERE s.snap_id = u.snap_id
AND   s.dbid = u.dbid
AND   s.instance_number = u.instance_number
UNION ALL
SELECT snap_date              -- the group column
     , metric_name stat_name  -- the series column
     , aas stat_value         -- the value column
FROM aas
ORDER BY stat_name DESC
       , snap_date
;

那么,这个查询维度的图表是下面这个样子:


[返回维度目录列表]

自定义时间段的平均活动会话和逻辑CPU数目(按每小时间隔)

对应的SQL查询见下面的代码:

-- Average Active Sessions & Logic CPUs Custom Time Period (interval by each hour).

SET LINESIZE 200
SET PAGESIZE 200

COLUMN snap_date_time FORMAT a19
COLUMN stat_name      FORMAT a25
COLUMN stat_value     FORMAT 999,999.99

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

WITH aas AS
(
  SELECT snap_id
       , dbid
       , instance_number
       , TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi:ss') snap_date_time
       , metric_name
       , ROUND(average, 2) aas
  FROM dba_hist_sysmetric_summary
  WHERE metric_name = 'Average Active Sessions'
  AND   (end_time BETWEEN TO_DATE(:start_date, 'yyyy-mm-dd hh24:mi:ss')
                  AND     TO_DATE(:end_date, 'yyyy-mm-dd hh24:mi:ss')
        )
),
oscpu AS
(
  SELECT snap_id
       , dbid
       , instance_number
       , stat_name
       , value
  FROM dba_hist_osstat
  WHERE stat_name = 'NUM_CPUS'
)
SELECT s.snap_date_time                                         -- the group column
     , DECODE(u.stat_name, 'NUM_CPUS', 'Logic CPUs') stat_name  -- the series column
     , u.value stat_value                                       -- the value column
FROM aas s
   , oscpu u
WHERE s.snap_id = u.snap_id
AND   s.dbid = u.dbid
AND   s.instance_number = u.instance_number
UNION ALL
SELECT snap_date_time         -- the group column
     , metric_name stat_name  -- the series column
     , aas stat_value         -- the value column
FROM aas
ORDER BY stat_name DESC
       , snap_date_time
;

那么,它的可视化图表详见下面的3个屏幕截图(1. 绑定变量“起始时间”,2. 绑定变量“结束时间”,3. 生成的可视化图表):




[返回维度目录列表]

自定义时间段的平均活动会话和逻辑CPU数目(按每天间隔)

这个维度的SQL查询语句是:

-- Average Active Sessions & Logic CPUs Custom Time Period (interval by each day).

SET LINESIZE 200
SET PAGESIZE 200

COLUMN snap_date  FORMAT a12
COLUMN stat_name  FORMAT a25
COLUMN stat_value FORMAT 999,999.99

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

WITH aas_per_hour AS
(
  SELECT snap_id
       , dbid
       , instance_number
       , TO_CHAR(end_time, 'yyyy-mm-dd') snap_date
       , metric_name
       , average
  FROM dba_hist_sysmetric_summary
  WHERE metric_name = 'Average Active Sessions'
  AND   (end_time BETWEEN TO_DATE(:start_date, 'yyyy-mm-dd')
                  AND     TO_DATE(:end_date, 'yyyy-mm-dd')
        )
),
aas AS
(
  SELECT snap_date                                    -- the group column
       , metric_name                                  -- the series column
       , ROUND(SUM(average)/COUNT(snap_date), 2) aas  -- the value column
  FROM aas_per_hour
  GROUP BY snap_date
         , metric_name
),
oscpu AS
(
  SELECT snap_id
       , dbid
       , instance_number
       , stat_name
       , value
  FROM dba_hist_osstat
  WHERE stat_name = 'NUM_CPUS'
)
SELECT DISTINCT s.snap_date                                     -- the group column
     , DECODE(u.stat_name, 'NUM_CPUS', 'Logic CPUs') stat_name  -- the series column
     , u.value stat_value                                       -- the value column
FROM aas_per_hour s
   , oscpu u
WHERE s.snap_id = u.snap_id
AND   s.dbid = u.dbid
AND   s.instance_number = u.instance_number
UNION ALL
SELECT snap_date              -- the group column
     , metric_name stat_name  -- the series column
     , aas stat_value         -- the value column
FROM aas
ORDER BY stat_name DESC
       , snap_date
;

同理,和上一个维度的图表相似,见下面3个屏幕截图:




[返回维度目录列表]

可视化Oracle性能图表之“平均活动会话&逻辑CPU”篇的分享就到这里结束了。虽然它的篇幅没有上一篇冗长,但是两者的思路和方法论具有异曲同工之妙,只能说是在前一篇的基础上进行了更深层次的升华。因为,首先要让读者明白什么是AAS,我不得不把曾读过和学习到的一些素材进行处理和加工,其次再以文字的形式表达出来。同时,进行一下剧透,我的下一篇文章将是可视化Oracle性能图表之“平均可运行进程”篇可视化Oracle性能图表之“每秒事务数&每秒登录数”篇),敬请期待!!!

补充:

  • 这个XML文件保存到您的电脑并用鼠标右击Oracle SQL Developer的用户自定义报告,然后选择“打开报告”,点选该XML文件进行导入;
  • 您也可以从我的GitHub查看这篇文章中提及到的所有SQL源代码;

更新于 2021年11月9日 下午:

  • 替换之前的“无序项目列表”的所有HTML代码(锚点设置)中的链接为在“当前页面”之内跳转而不是在“新窗口”打开;如,之前是 <a href="......#....." target="_blank">......</a>,之后是 <a href="......#....." target="_parent">......</a>;
  • 替换之前的“[返回维度目录列表]”的所有HTML代码(锚点设置)中的链接为“真正返回到维度目录列表”而不是打开“新窗口”;如,之前是 <a href="......#....." target="_blank">[返回维度目录列表]</a>,之后是 <a href="......#....." target="_parent">[返回维度目录列表]</a>;

更新于 2021年11月25日 上午:

  • 计划有变,更改文章结尾段落中的剧透内容为可视化Oracle性能图表之“平均可运行进程”篇

最后修改时间:2021-11-25 11:01:55
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
5人已赞赏
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论