暂无图片
暂无图片
17
暂无图片
暂无图片
暂无图片
首页 / 可视化Oracle性能图表之“平均可运行进程”篇

可视化Oracle性能图表之“平均可运行进程”篇

原创 赵全文 2021-11-21
4870

目录

前言

最近我在墨天轮上连续发表了两篇关于“可视化Oracle性能图表”的原创文章，它们分别是：

让我感到惊喜的是，这两篇文章收到了“3K+”的浏览量，如图所示，


这也激励着我继续进行原创文章的发表。那么，今天我给大家分享的是可视化Oracle性能图表之“平均可运行进程”篇。说到这里，可能有的小伙伴会问，什么是Oracle的“平均可运行进程”呢？不急，容我慢慢道来。也许我们听到最多的就是Oracle的“平均活动会话”了，其实Oracle官方没有明确地提过这个东西，如果你使用“EMCC 13.5”监控Oracle数据库的话，应该会在被监控数据库的“性能主页”上第一个看到它，下面是两张相应的屏幕截图：



从第二张图中，你可以看到“平均可运行进程”的图表，“平均可运行进程”就是单位时间内的CPU时间。实际上我们从图表右侧显示的四个图例（实例前台CPU，实例后台CPU，非数据库主机CPU和平均负载）也能明白，这个“平均可运行进程”可以通过单位时间（每秒）内运行在操作系统上的Oracle数据库的CPU时间（包括前台进程和后台进程）和非数据库主机的CPU时间以及平均负载（单位时间内操作系统上运行的CPU队列）这四个方面来进行考量。应该这么说，在先前版本的EMCC中你看到的应该是下图中的“可运行进程”图表。因此，在最新版本的EMCC 13.5中进行了增强，除了包括“平均负载”之外，还增加了三个不同的CPU。


如果我们不使用EMCC的话，是不是就没有办法直观地显示这个图表呢？答案是否定的，接下来，我会带你逐步并最终达到可视化“平均可运行进程”的图表。和前两篇文章的方法论一样，我仍然是从下面的八个维度进行详细地阐述。

[返回顶部目录]

维度目录列表

最近1小时的平均可运行进程

感谢Kyle Hailey

上个月底，我在推特上看到了Kyle Hailey发了一条有趣的推文，我越读越觉得特别熟悉，它分明就是上个月中下旬我在FreeLists的Oracle-L空间里发的一个技术帖子中提到的这个“平均可运行进程”图表，推文内容如图所示：


于是，我就借机向他表示感谢并请教如何利用SQL语句才能查询出这个图表的数据并进一步可视化。他很热心地回复我，他没有做过这个，但是给我推荐了他曾经写过的一篇博客文章，说我也许会感兴趣，详见如下两图：



果不其然，我反复读了几篇他的文章和一些朋友们在评论区给他发的消息以及他的回复之后，终于写出了最近1小时的“平均可运行进程”的SQL语句。于是，接连四天给他回复了我对他的博客文章的一些理解，如图所示：


直接上代码，我写出的SQL代码是这样的：

-- Average Runnable Processes in Last 1 Hour.

SET LINESIZE 200
SET PAGESIZE 300

COLUMN metric_name FORMAT a25

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

WITH ins_fg_cpu AS
(
  SELECT end_time sample_time
       , DECODE(metric_name, 'CPU Usage Per Sec', 'Instance Foreground CPU') metric_name
       , ROUND(value/1e2, 2) value
  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
),
ins_bg_cpu AS
(
  SELECT end_time sample_time
       , DECODE(metric_name, 'Background CPU Usage Per Sec', 'Instance Background CPU') metric_name
       , ROUND(value/1e2, 2) value
  FROM v$sysmetric_history
  WHERE metric_name = 'Background CPU Usage Per Sec'
  AND   group_id = 2
  AND   end_time >= SYSDATE - INTERVAL '60' MINUTE
  ORDER BY sample_time
),
host_cpu AS
(
  SELECT end_time sample_time
       , DECODE(metric_name, 'Host CPU Usage Per Sec', 'Host CPU') metric_name
       , ROUND(value/1e2, 2) value
  FROM v$sysmetric_history
  WHERE metric_name = 'Host CPU Usage Per Sec'
  AND   group_id = 2
  AND   end_time >= SYSDATE - INTERVAL '60' MINUTE
  ORDER BY sample_time
),
non_db_host_cpu AS
(
  SELECT hc.sample_time
       , 'Non-Database Host CPU' metric_name
       , hc.value - fc.value - bc.value value
  FROM host_cpu hc
     , ins_fg_cpu fc
     , ins_bg_cpu bc
  WHERE hc.sample_time = fc.sample_time
  AND   fc.sample_time = bc.sample_time
  ORDER BY hc.sample_time
),
load_average AS
(
  SELECT end_time sample_time
       , DECODE(metric_name, 'Current OS Load', 'Load Average') metric_name
       , ROUND(value, 2) value
  FROM v$sysmetric_history
  WHERE metric_name = 'Current OS Load'
  AND   group_id = 2
  AND   end_time >= SYSDATE - INTERVAL '60' MINUTE
  ORDER BY sample_time
)
SELECT * FROM ins_fg_cpu
UNION ALL
SELECT * FROM ins_bg_cpu
UNION ALL
SELECT * FROM non_db_host_cpu
UNION ALL
SELECT * FROM load_average
;

当然，代码有点多，我就简单解释一下。Oracle动态性能视图“v$sysmetric_history”中的“metric_name”的值“CPU Usage Per Sec”就是我在EMCC13.5中看到的“平均可运行进程”图表的第一个图例（图例条目从下往上数，因为三个CPU都是以“面积堆叠图”来展示的）“Instance Foreground CPU”；以此类推，“metric_name”的值“Background CPU Usage Per Sec”对应于第二个图例“Instance Background CPU”，但是第三个图例“Non-Database Host CPU”不能直接从metric_name的值中得到了，不过metric_name有一个值是“Host CPU Usage Per Sec”，将它与它俩（“CPU Usage Per Sec”和“Background CPU Usage Per Sec”）的和进行相减就是“Non-Database Host CPU”。此外，metric_name的值“Current OS Load”对应于第四个图例“Load Average”（以“折线图”来展示的，非“面积堆叠图”）。然后将四个临时表（ins_fg_cpu，ins_bg_cpu，non_db_host_cpu和load_average）依次进行UNION ALL操作，就是最终的SQL代码。非常巧合的是，在Kyle Hailey的那篇文章的评论区也提到了相似的观点，见下图：


[返回最近1小时的平均可运行进程]

[返回维度目录列表]

[返回顶部目录]

对比“SQL查询数据”和“EMCC 13.5的图表显示数据”

为了验证我写的SQL代码的查询数据能否和EMCC 13.5中“最近1小时的平均可运行进程”图表的显示数据一致，我专门把两者在“同一时间（2021-11-20 14:13）”的数据通过四组屏幕截图进行了前后对比，它们分别为：









从上面的四组对比图中，我们可以看出，我写的“SQL代码”完全吻合“EMCC 13.5”的图表数据。

[返回最近1小时的平均可运行进程]

[返回维度目录列表]

[返回顶部目录]

用Oracle SQL Developer 21.2可视化

在前两篇文章中，我曾使用Oracle SQL Developer 21.2中的用户自定义报告来对其它Oracle性能图表进行了一系列的可视化展示（细心的读者已经留意到，我使用的图表类型是常见的“折线图”）。现在我也用同样的方法来可视化最近1小时的平均可运行进程。注意：我们这里要使用的图表类型应该是“组合图”（按照图例从下往上数，前三个是“面积堆叠图”，后一个是“折线图”）。当然，前面的几步设置操作相当顺畅，在这里略过，直到点击“应用”按钮以后，却发现图表的可视化效果让我大跌眼镜。不过，我猜应该是SQL Developer出现了Bug。于是，我在ODC（Oracle Developer Community）的SQL Developer空间发了一个讨论的帖子，希望得到Oracle社区朋友们的反馈。不幸的是，没有回音。接着，我又到AskTOM上发了一遍，Connor McDonald给我的反馈是他已经转到了SQL Dev folks那里，静等下一个版本中会得到修复吧。其中，预览效果（前两图）是正确的，实际效果（后一图）是错误的。详见如下三张相应的屏幕截图：




[返回最近1小时的平均可运行进程]

[返回维度目录列表]

[返回顶部目录]

用Oracle APEX 21.2可视化

果断放弃SQL Developer的自定义报告以后，我决定采用最新版本的Oracle APEX 21.2来寻找解决方案。通过申请工作空间和注册账号等一系列基本的操作以后，我得到了一个APEX工作空间。如图所示：




接着，我在SQL*Plus里使用SPOOL命令包裹我前面的SQL代码一起执行生成一个CSV格式的文件并导出到我的电脑，然后将该CSV文件导入到我刚刚创建的APEX工作空间并同时生成了一个ARP的应用。在该应用的主页中我创建了一个“Interactive Grid”的报表。在运行该应用程序以后，我发现我的报表页面有一个“Actions”的下拉按钮列表，在里面有一个“Chart”，这个正好是我期待的图表功能。当我点击它以后，弹出一个对话框，里面包括许多常见的图表类型，可惜没有找到我想要的“组合图表”，详见如下两图：



[返回最近1小时的平均可运行进程]

[返回维度目录列表]

[返回顶部目录]

用Microsoft Office Excel可视化

果断放弃Oracle APEX的图表以后，我决定采用Microsoft Office Excel来寻找解决方案。也许这个方法最原始，但也是最本质的，退一步来讲，追本溯源，值得借鉴。首先，让我们来看一下Excel中的“面积堆叠图”的基本原理，下面这张图介绍得透彻明了：


从上图，我们可以了解到，Excel数据表中的“时间”代表“面积堆叠图”中的横轴，表格中的“分类”代表“面积堆叠图”中的图例，表格中的“其它单元格的值”代表“面积堆叠图”中的纵轴。通过“平均可运行进程”图表中的横轴、纵轴和图例进行类比，Excel数据表应该是，时间是SQL查询中的“采样时间”，分类是SQL查询中的“度量名字”，其它单元格的值是SQL查询中的“值”。这里我们不能直接使用先前的SQL查询来得到Excel数据表。又因为Excel数据表的所有数据列名（采样时间）正好是SQL查询中的所有行（采样时间列的值），所以我们需要把先前的SQL查询进行一下改良，即进行“行转列”操作以后，就可以得到相应的Excel数据表；又因为我们每次查询的最近1小时的“采样时间”也是不断变化的，所以我们将要进行的改良操作，不是“固定行转列”，而是“动态行转列”。

接着，我用先前的SQL查询创建一个“视图”，然后再对这个视图进行“动态行转列”操作。由于我是在SYSTEM用户下进行的查询，所以在创建“视图”之前，需要提前给v$sysmetric_history（和dba_hist_sysmetric_summary，后面七个维度需要查这张表，再此一并授权）做一些“授权”操作，详见如下代码：

DESC dba_sys_privs
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 GRANTEE                                                           NOT NULL VARCHAR2(30)
 PRIVILEGE                                                         NOT NULL VARCHAR2(40)
 ADMIN_OPTION                                                               VARCHAR2(3)

SELECT privilege FROM dba_sys_privs WHERE grantee = 'SYSTEM' ORDER BY 1;

PRIVILEGE
--------------------------------------------------------------------------------
CREATE MATERIALIZED VIEW
CREATE TABLE
GLOBAL QUERY REWRITE
SELECT ANY TABLE
UNLIMITED TABLESPACE

GRANT select on v_$sysmetric_history TO system;
GRANT select on dba_hist_sysmetric_summary TO system;

SET LINESIZE 100

COLUMN table_name FORMAT a28
COLUMN privilege  FORMAT a10

SELECT table_name
     , privilege
FROM dba_tab_privs
WHERE grantee = 'SYSTEM'
AND table_name IN ('V_$SYSMETRIC_HISTORY', 'DBA_HIST_SYSMETRIC_SUMMARY')
ORDER BY 1
;

TABLE_NAME                   PRIVILEGE
---------------------------- ----------
DBA_HIST_SYSMETRIC_SUMMARY   SELECT
V_$SYSMETRIC_HISTORY         SELECT

创建视图“arp_in_last_1_hour”，代码如下所示：

PROMPT ===========================================
PROMPT  Average Runnable Processes in Last 1 Hour
PORMPT ===========================================

SET LINESIZE 200
SET PAGESIZE 300

COLUMN sample_time FORMAT a12
COLUMN metric_name FORMAT a25
COLUMN value       FORMAT 999,999.99

-- 
-- Creating a view named "arp_in_last_1_hour" to show "sample_time" and "value" based on
-- the four metrics amongst "Instance Foreground CPU", "Instance Background CPU",
-- "Non-Database Host CPU" and "Load Average" from v$sysmetric_history in last 1 hour.
-- 

CREATE OR REPLACE VIEW arp_in_last_1_hour
AS
WITH
ins_fg_cpu AS
(
  SELECT TO_CHAR(end_time, 'hh24:mi:ss') sample_time
       , DECODE(metric_name, 'CPU Usage Per Sec', 'Instance Foreground CPU') metric_name
       , ROUND(value/1e2, 2) value
  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
),
ins_bg_cpu AS
(
  SELECT TO_CHAR(end_time, 'hh24:mi:ss') sample_time
       , DECODE(metric_name, 'Background CPU Usage Per Sec', 'Instance Background CPU') metric_name
       , ROUND(value/1e2, 2) value
  FROM v$sysmetric_history
  WHERE metric_name = 'Background CPU Usage Per Sec'
  AND   group_id = 2
  AND   end_time >= SYSDATE - INTERVAL '60' MINUTE
  ORDER BY sample_time
),
host_cpu AS
(
  SELECT TO_CHAR(end_time, 'hh24:mi:ss') sample_time
       , DECODE(metric_name, 'Host CPU Usage Per Sec', 'Host CPU') metric_name
       , ROUND(value/1e2, 2) value
  FROM v$sysmetric_history
  WHERE metric_name = 'Host CPU Usage Per Sec'
  AND   group_id = 2
  AND   end_time >= SYSDATE - INTERVAL '60' MINUTE
  ORDER BY sample_time
),
non_db_host_cpu AS
(
  SELECT hc.sample_time
       , 'Non-Database Host CPU' metric_name
       , hc.value - fc.value - bc.value value
  FROM host_cpu hc
     , ins_fg_cpu fc
     , ins_bg_cpu bc
  WHERE hc.sample_time = fc.sample_time
  AND   fc.sample_time = bc.sample_time
  ORDER BY hc.sample_time
),
load_average AS
(
  SELECT TO_CHAR(end_time, 'hh24:mi:ss') sample_time
       , DECODE(metric_name, 'Current OS Load', 'Load Average') metric_name
       , ROUND(value, 2) value
  FROM v$sysmetric_history
  WHERE metric_name = 'Current OS Load'
  AND   group_id = 2
  AND   end_time >= SYSDATE - INTERVAL '60' MINUTE
  ORDER BY sample_time
)
SELECT * FROM ins_fg_cpu
UNION ALL
SELECT * FROM ins_bg_cpu
UNION ALL
SELECT * FROM non_db_host_cpu
UNION ALL
SELECT * FROM load_average
;

我们通过创建一个存储过程“pro_convert_rows_to_columns”对视图“arp_in_last_1_hour”进行“动态行转列”操作，然后再创建另一个视图“arp_in_last_1_hour_result”来保存前面的操作结果，这两步操作都在“同一存储过程”里完成，详见如下代码：

-- 
-- Creating a procedure named "pro_convert_rows_to_columns" to dynamically convert "sample_time"
-- from rows to columns based on the previous view "arp_in_last_1_hour".
-- 

CREATE OR REPLACE PROCEDURE pro_convert_rows_to_columns
AUTHID CURRENT_USER
IS
  v_sql VARCHAR2(4000);
  CURSOR cur_samp_time IS
  SELECT sample_time
  FROM arp_in_last_1_hour
  WHERE metric_name = 'Instance Foreground CPU'
  ORDER BY sample_time;
BEGIN
  v_sql := Q'[SELECT metric_name]';

  FOR v_samp_time IN cur_samp_time
  LOOP
    v_sql := v_sql || Q'[, MAX(DECODE(sample_time, ']'
                   || v_samp_time.sample_time
                   || Q'[', value)) AS "]'
                   || v_samp_time.sample_time
                   || Q'["]';
  END LOOP;
  
  v_sql := v_sql || Q'[ FROM arp_in_last_1_hour GROUP BY metric_name]'
                 || Q'[ ORDER BY DECODE(metric_name,]'
                 || Q'[  'Instance Foreground CPU', 1]'
                 || Q'[, 'Instance Background CPU', 2]'
                 || Q'[, 'Non-Database Host CPU'  , 3]'
                 || Q'[, 'Load Average'           , 4]'
                 || Q'[)]';

  v_sql := 'CREATE OR REPLACE VIEW arp_in_last_1_hour_result AS ' || v_sql;

  EXECUTE IMMEDIATE v_sql;
END;
/

执行创建成功的存储过程“pro_convert_rows_to_columns”并查询创建成功的视图“arp_in_last_1_hour_result”，具体代码是这样的：

-- 
-- Running the previous procedure "pro_convert_rows_to_columns" to create view
-- "arp_in_last_1_hour_result" to save the result of converting rows to columns dynamically.
-- 
-- Firstly executing "SET SQLFORMAT csv" on Oracle SQL Developer 21.2 next running the following SQL query
-- by clicking the button of "Run Script" or pressing F5 to show the CSV format, finally save this CSV file
-- "arp_1.csv" to your local computer.
-- 

EXECUTE pro_convert_rows_to_columns;

SET SQLFORMAT csv;

SELECT * FROM arp_in_last_1_hour_result;

操作步骤详见如下两图：



此时，CSV文件“arp_1.csv”已经保存到我的本地电脑。接下来，我们将其导入到Excel，通过简单的几步导入操作之后，我们最终需要的Excel数据表就生成了。操作步骤依次请看下面八张相应的屏幕截图：









下一步，我们给Excel数据表的所在边框加一个框线并用“鼠标选中”整个表，然后去创建我们期盼已久的“组合图”（“面积堆叠图”和“折线图”），操作步骤依次详见如下五张屏幕截图：






到这里，最近1小时的平均可运行进程的可视化图表（“组合图”）就生成了。接着，进行几步快速地设置，如：添加“图表标题”，显示并添加“横/纵坐标轴标题”，设置纵轴值的显示方式为“数字”并保留“两位小数”，设置“横”坐标轴类型为“文本坐标轴”，调整“三个CPU”图例的“填充颜色”和“平均负载”图例的“轮廓颜色”（用鼠标左键选择每个图例的所在区域，然后点击鼠标右键弹出颜色设置菜单），详见如下十张屏幕截图：










那么，我们对比一下EMCC 13.5中的“最近1小时（同一时间段）”的平均可运行进程的图表，见下图：


完美，从整个图表数据的趋势来看，应该是基本一致的，哈哈！！！

[返回最近1小时的平均可运行进程]

[返回维度目录列表]

[返回顶部目录]

最近24小时的平均可运行进程

因为阐述上一个维度的“平均可运行进程”图表可视化花了大量的篇幅，所以针对后面这七个维度的图表可视化，我将如法炮制，在此省略基本的操作步骤，所有代码依次为：

PROMPT =============================================
PROMPT  Average Runnable Processes in Last 24 Hours
PORMPT =============================================

SET LINESIZE 200
SET PAGESIZE 300

COLUMN sample_time FORMAT a20
COLUMN metric_name FORMAT a25
COLUMN value       FORMAT 999,999.99

-- 
-- Creating a view named "arp_in_last_24_hours" to show "sample_time" and "value" based on
-- the four metrics amongst "Instance Foreground CPU", "Instance Background CPU",
-- "Non-Database Host CPU" and "Load Average" from dba_hist_sysmetric_summary in last 24 hours.
-- 

CREATE OR REPLACE VIEW arp_in_last_24_hours
AS
WITH
ins_fg_cpu AS
(
  SELECT TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi') sample_time
       , DECODE(metric_name, 'CPU Usage Per Sec', 'Instance Foreground CPU') metric_name
       , ROUND(average/1e2, 2) value
  FROM dba_hist_sysmetric_summary
  WHERE metric_name = 'CPU Usage Per Sec'
  AND   group_id = 2
  AND   end_time >= SYSDATE - INTERVAL '24' HOUR
  ORDER BY sample_time
),
ins_bg_cpu AS
(
  SELECT TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi') sample_time
       , DECODE(metric_name, 'Background CPU Usage Per Sec', 'Instance Background CPU') metric_name
       , ROUND(average/1e2, 2) value
  FROM dba_hist_sysmetric_summary
  WHERE metric_name = 'Background CPU Usage Per Sec'
  AND   group_id = 2
  AND   end_time >= SYSDATE - INTERVAL '24' HOUR
  ORDER BY sample_time
),
host_cpu AS
(
  SELECT TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi') sample_time
       , DECODE(metric_name, 'Host CPU Usage Per Sec', 'Host CPU') metric_name
       , ROUND(average/1e2, 2) value
  FROM dba_hist_sysmetric_summary
  WHERE metric_name = 'Host CPU Usage Per Sec'
  AND   group_id = 2
  AND   end_time >= SYSDATE - INTERVAL '24' HOUR
  ORDER BY sample_time
),
non_db_host_cpu AS
(
  SELECT hc.sample_time
       , 'Non-Database Host CPU' metric_name
       , hc.value - fc.value - bc.value value
  FROM host_cpu hc
     , ins_fg_cpu fc
     , ins_bg_cpu bc
  WHERE hc.sample_time = fc.sample_time
  AND   fc.sample_time = bc.sample_time
  ORDER BY hc.sample_time
),
load_average AS
(
  SELECT TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi') sample_time
       , DECODE(metric_name, 'Current OS Load', 'Load Average') metric_name
       , ROUND(average, 2) value
  FROM dba_hist_sysmetric_summary
  WHERE metric_name = 'Current OS Load'
  AND   group_id = 2
  AND   end_time >= SYSDATE - INTERVAL '24' HOUR
  ORDER BY sample_time
)
SELECT * FROM ins_fg_cpu
UNION ALL
SELECT * FROM ins_bg_cpu
UNION ALL
SELECT * FROM non_db_host_cpu
UNION ALL
SELECT * FROM load_average
;
-- 
-- Creating a procedure named "pro_convert_rows_to_columns_2" to dynamically convert "sample_time"
-- from rows to columns based on the previous view "arp_in_last_24_hours".
-- 

CREATE OR REPLACE PROCEDURE pro_convert_rows_to_columns_2
AUTHID CURRENT_USER
IS
  v_sql VARCHAR2(4000);
  CURSOR cur_samp_time IS
  SELECT sample_time
  FROM arp_in_last_24_hours
  WHERE metric_name = 'Instance Foreground CPU'
  ORDER BY sample_time;
BEGIN
  v_sql := Q'[SELECT metric_name]';

  FOR v_samp_time IN cur_samp_time
  LOOP
    v_sql := v_sql || Q'[, MAX(DECODE(sample_time, ']'
                   || v_samp_time.sample_time
                   || Q'[', value)) AS "]'
                   || v_samp_time.sample_time
                   || Q'["]';
  END LOOP;
  
  v_sql := v_sql || Q'[ FROM arp_in_last_24_hours GROUP BY metric_name]'
                 || Q'[ ORDER BY DECODE(metric_name,]'
                 || Q'[  'Instance Foreground CPU', 1]'
                 || Q'[, 'Instance Background CPU', 2]'
                 || Q'[, 'Non-Database Host CPU'  , 3]'
                 || Q'[, 'Load Average'           , 4]'
                 || Q'[)]';

  v_sql := 'CREATE OR REPLACE VIEW arp_in_last_24_hours_result AS ' || v_sql;

  EXECUTE IMMEDIATE v_sql;
END;
/
-- 
-- Running the previous procedure "pro_convert_rows_to_columns_2" to create view
-- "arp_in_last_24_hours_result" to save the result of converting rows to columns dynamically.
-- 
-- Firstly executing "SET SQLFORMAT csv" on Oracle SQL Developer 21.2 next running the following SQL query
-- by clicking the button of "Run Script" or pressing F5 to show the CSV format, finally save this CSV file
-- "arp_2.csv" to your local computer.
-- 

EXECUTE pro_convert_rows_to_columns_2;

SET SQLFORMAT csv;

SELECT * FROM arp_in_last_24_hours_result;

最终的图表效果见下图：

[返回维度目录列表]

[返回顶部目录]

最近7天的平均可运行进程（按每小时间隔）

同理，详见所有SQL代码：

PROMPT ===================================================================
PROMPT  Average Runnable Processes in Last 7 Days (interval by each hour)
PORMPT ===================================================================

SET LINESIZE 200
SET PAGESIZE 700

COLUMN sample_time FORMAT a20
COLUMN metric_name FORMAT a25
COLUMN value       FORMAT 999,999.99

-- 
-- Creating a view named "arp_in_last_7_days" to show "sample_time" and "value" based on
-- the four metrics amongst "Instance Foreground CPU", "Instance Background CPU",
-- "Non-Database Host CPU" and "Load Average" from dba_hist_sysmetric_summary
-- in last 7 days (interval by each hour).
-- 

CREATE OR REPLACE VIEW arp_in_last_7_days
AS
WITH
ins_fg_cpu AS
(
  SELECT TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi') sample_time
       , DECODE(metric_name, 'CPU Usage Per Sec', 'Instance Foreground CPU') metric_name
       , ROUND(average/1e2, 2) value
  FROM dba_hist_sysmetric_summary
  WHERE metric_name = 'CPU Usage Per Sec'
  AND   group_id = 2
  AND   end_time >= SYSDATE - INTERVAL '6' DAY
  ORDER BY sample_time
),
ins_bg_cpu AS
(
  SELECT TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi') sample_time
       , DECODE(metric_name, 'Background CPU Usage Per Sec', 'Instance Background CPU') metric_name
       , ROUND(average/1e2, 2) value
  FROM dba_hist_sysmetric_summary
  WHERE metric_name = 'Background CPU Usage Per Sec'
  AND   group_id = 2
  AND   end_time >= SYSDATE - INTERVAL '6' DAY
  ORDER BY sample_time
),
host_cpu AS
(
  SELECT TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi') sample_time
       , DECODE(metric_name, 'Host CPU Usage Per Sec', 'Host CPU') metric_name
       , ROUND(average/1e2, 2) value
  FROM dba_hist_sysmetric_summary
  WHERE metric_name = 'Host CPU Usage Per Sec'
  AND   group_id = 2
  AND   end_time >= SYSDATE - INTERVAL '6' DAY
  ORDER BY sample_time
),
non_db_host_cpu AS
(
  SELECT hc.sample_time
       , 'Non-Database Host CPU' metric_name
       , hc.value - fc.value - bc.value value
  FROM host_cpu hc
     , ins_fg_cpu fc
     , ins_bg_cpu bc
  WHERE hc.sample_time = fc.sample_time
  AND   fc.sample_time = bc.sample_time
  ORDER BY hc.sample_time
),
load_average AS
(
  SELECT TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi') sample_time
       , DECODE(metric_name, 'Current OS Load', 'Load Average') metric_name
       , ROUND(average, 2) value
  FROM dba_hist_sysmetric_summary
  WHERE metric_name = 'Current OS Load'
  AND   group_id = 2
  AND   end_time >= SYSDATE - INTERVAL '6' DAY
  ORDER BY sample_time
)
SELECT * FROM ins_fg_cpu
UNION ALL
SELECT * FROM ins_bg_cpu
UNION ALL
SELECT * FROM non_db_host_cpu
UNION ALL
SELECT * FROM load_average
;
-- 
-- Creating a procedure named "pro_convert_rows_to_columns_3" to dynamically convert "sample_time"
-- from rows to columns based on the previous view "arp_in_last_7_days".
-- 

CREATE OR REPLACE PROCEDURE pro_convert_rows_to_columns_3
AUTHID CURRENT_USER
IS
  v_sql CLOB;
  CURSOR cur_samp_time IS
  SELECT sample_time
  FROM arp_in_last_7_days
  WHERE metric_name = 'Instance Foreground CPU'
  ORDER BY sample_time;
BEGIN
  v_sql := Q'[SELECT metric_name]';

  FOR v_samp_time IN cur_samp_time
  LOOP
    v_sql := v_sql || Q'[, MAX(DECODE(sample_time, ']'
                   || v_samp_time.sample_time
                   || Q'[', value)) AS "]'
                   || v_samp_time.sample_time
                   || Q'["]';
  END LOOP;
  
  v_sql := v_sql || Q'[ FROM arp_in_last_7_days GROUP BY metric_name]'
                 || Q'[ ORDER BY DECODE(metric_name,]'
                 || Q'[  'Instance Foreground CPU', 1]'
                 || Q'[, 'Instance Background CPU', 2]'
                 || Q'[, 'Non-Database Host CPU'  , 3]'
                 || Q'[, 'Load Average'           , 4]'
                 || Q'[)]';

  v_sql := 'CREATE OR REPLACE VIEW arp_in_last_7_days_result AS ' || v_sql;

  EXECUTE IMMEDIATE v_sql;
END;
/
-- 
-- Running the previous procedure "pro_convert_rows_to_columns_3" to create view
-- "arp_in_last_7_days_result" to save the result of converting rows to columns dynamically.
-- 
-- Firstly executing "SET SQLFORMAT csv" on Oracle SQL Developer 21.2 next running the following SQL query
-- by clicking the button of "Run Script" or pressing F5 to show the CSV format, finally save this CSV file
-- "arp_3.csv" to your local computer.
-- 

EXECUTE pro_convert_rows_to_columns_3;

SET SQLFORMAT csv;

SELECT * FROM arp_in_last_7_days_result;

最后生成的图表见下图：

[返回维度目录列表]

[返回顶部目录]

最近7天的平均可运行进程（按每天间隔）

该维度的所有SQL代码如下所示：

PROMPT ==================================================================
PROMPT  Average Runnable Processes in Last 7 Days (interval by each day)
PORMPT ==================================================================

SET LINESIZE 200
SET PAGESIZE 100

COLUMN sample_time FORMAT a12
COLUMN metric_name FORMAT a25
COLUMN value       FORMAT 999,999.99

-- 
-- Creating a view named "arp_in_last_7_days_2" to show "sample_time" and "value" based on
-- the four metrics amongst "Instance Foreground CPU", "Instance Background CPU",
-- "Non-Database Host CPU" and "Load Average" based on the view "arp_in_last_7_days"
-- in last 7 days (interval by each day).
-- 

CREATE OR REPLACE VIEW arp_in_last_7_days_2
AS
SELECT TO_CHAR(TO_DATE(sample_time, 'yyyy-mm-dd hh24:mi'), 'yyyy-mm-dd') sample_time
     , metric_name
     , ROUND(AVG(value), 2) value
FROM arp_in_last_7_days
GROUP BY TO_CHAR(TO_DATE(sample_time, 'yyyy-mm-dd hh24:mi'), 'yyyy-mm-dd')
       , metric_name
ORDER BY DECODE(metric_name, 'Instance Foreground CPU', 1
                           , 'Instance Background CPU', 2
                           , 'Non-Database Host CPU'  , 3
                           , 'Load Average'           , 4
               )
       , sample_time
;
-- 
-- Creating a procedure named "pro_convert_rows_to_columns_4" to dynamically convert "sample_time"
-- from rows to columns based on the previous view "arp_in_last_7_days_2".
-- 

CREATE OR REPLACE PROCEDURE pro_convert_rows_to_columns_4
AUTHID CURRENT_USER
IS
  v_sql VARCHAR2(4000);
  CURSOR cur_samp_time IS
  SELECT sample_time
  FROM arp_in_last_7_days_2
  WHERE metric_name = 'Instance Foreground CPU'
  ORDER BY sample_time;
BEGIN
  v_sql := Q'[SELECT metric_name]';

  FOR v_samp_time IN cur_samp_time
  LOOP
    v_sql := v_sql || Q'[, MAX(DECODE(sample_time, ']'
                   || v_samp_time.sample_time
                   || Q'[', value)) AS "]'
                   || v_samp_time.sample_time
                   || Q'["]';
  END LOOP;
  
  v_sql := v_sql || Q'[ FROM arp_in_last_7_days_2 GROUP BY metric_name]'
                 || Q'[ ORDER BY DECODE(metric_name,]'
                 || Q'[  'Instance Foreground CPU', 1]'
                 || Q'[, 'Instance Background CPU', 2]'
                 || Q'[, 'Non-Database Host CPU'  , 3]'
                 || Q'[, 'Load Average'           , 4]'
                 || Q'[)]';

  v_sql := 'CREATE OR REPLACE VIEW arp_in_last_7_days_2_result AS ' || v_sql;

  EXECUTE IMMEDIATE v_sql;
END;
/
-- 
-- Running the previous procedure "pro_convert_rows_to_columns_4" to create view
-- "arp_in_last_7_days_2_result" to save the result of converting rows to columns dynamically.
-- 
-- Firstly executing "SET SQLFORMAT csv" on Oracle SQL Developer 21.2 next running the following SQL query
-- by clicking the button of "Run Script" or pressing F5 to show the CSV format, finally save this CSV file
-- "arp_4.csv" to your local computer.
-- 

EXECUTE pro_convert_rows_to_columns_4;

SET SQLFORMAT csv;

SELECT * FROM arp_in_last_7_days_2_result;

最终生成的可视化图表是下面的效果图：

[返回维度目录列表]

[返回顶部目录]

最近31天的平均可运行进程（按每小时间隔）

相应的SQL代码如下所示：

PROMPT ====================================================================
PROMPT  Average Runnable Processes in Last 31 Days (interval by each hour)
PORMPT ====================================================================

SET LINESIZE 200
SET PAGESIZE 3000

COLUMN sample_time FORMAT a20
COLUMN metric_name FORMAT a25
COLUMN value       FORMAT 999,999.99

-- 
-- Creating a view named "arp_in_last_31_days" to show "sample_time" and "value" based on
-- the four metrics amongst "Instance Foreground CPU", "Instance Background CPU",
-- "Non-Database Host CPU" and "Load Average" from dba_hist_sysmetric_summary
-- in last 31 days (interval by each hour).
-- 

CREATE OR REPLACE VIEW arp_in_last_31_days
AS
WITH
ins_fg_cpu AS
(
  SELECT TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi') sample_time
       , DECODE(metric_name, 'CPU Usage Per Sec', 'Instance Foreground CPU') metric_name
       , ROUND(average/1e2, 2) value
  FROM dba_hist_sysmetric_summary
  WHERE metric_name = 'CPU Usage Per Sec'
  AND   group_id = 2
  AND   end_time >= SYSDATE - INTERVAL '30' DAY
  ORDER BY sample_time
),
ins_bg_cpu AS
(
  SELECT TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi') sample_time
       , DECODE(metric_name, 'Background CPU Usage Per Sec', 'Instance Background CPU') metric_name
       , ROUND(average/1e2, 2) value
  FROM dba_hist_sysmetric_summary
  WHERE metric_name = 'Background CPU Usage Per Sec'
  AND   group_id = 2
  AND   end_time >= SYSDATE - INTERVAL '30' DAY
  ORDER BY sample_time
),
host_cpu AS
(
  SELECT TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi') sample_time
       , DECODE(metric_name, 'Host CPU Usage Per Sec', 'Host CPU') metric_name
       , ROUND(average/1e2, 2) value
  FROM dba_hist_sysmetric_summary
  WHERE metric_name = 'Host CPU Usage Per Sec'
  AND   group_id = 2
  AND   end_time >= SYSDATE - INTERVAL '30' DAY
  ORDER BY sample_time
),
non_db_host_cpu AS
(
  SELECT hc.sample_time
       , 'Non-Database Host CPU' metric_name
       , hc.value - fc.value - bc.value value
  FROM host_cpu hc
     , ins_fg_cpu fc
     , ins_bg_cpu bc
  WHERE hc.sample_time = fc.sample_time
  AND   fc.sample_time = bc.sample_time
  ORDER BY hc.sample_time
),
load_average AS
(
  SELECT TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi') sample_time
       , DECODE(metric_name, 'Current OS Load', 'Load Average') metric_name
       , ROUND(average, 2) value
  FROM dba_hist_sysmetric_summary
  WHERE metric_name = 'Current OS Load'
  AND   group_id = 2
  AND   end_time >= SYSDATE - INTERVAL '30' DAY
  ORDER BY sample_time
)
SELECT * FROM ins_fg_cpu
UNION ALL
SELECT * FROM ins_bg_cpu
UNION ALL
SELECT * FROM non_db_host_cpu
UNION ALL
SELECT * FROM load_average
;
-- 
-- Creating a procedure named "pro_convert_rows_to_columns_5" to dynamically convert "sample_time"
-- from rows to columns based on the previous view "arp_in_last_31_days".
-- 

CREATE OR REPLACE PROCEDURE pro_convert_rows_to_columns_5
AUTHID CURRENT_USER
IS
  v_sql CLOB;
  CURSOR cur_samp_time IS
  SELECT sample_time
  FROM arp_in_last_31_days
  WHERE metric_name = 'Instance Foreground CPU'
  ORDER BY sample_time;
BEGIN
  v_sql := Q'[SELECT metric_name]';

  FOR v_samp_time IN cur_samp_time
  LOOP
    v_sql := v_sql || Q'[, MAX(DECODE(sample_time, ']'
                   || v_samp_time.sample_time
                   || Q'[', value)) AS "]'
                   || v_samp_time.sample_time
                   || Q'["]';
  END LOOP;
  
  v_sql := v_sql || Q'[ FROM arp_in_last_31_days GROUP BY metric_name]'
                 || Q'[ ORDER BY DECODE(metric_name,]'
                 || Q'[  'Instance Foreground CPU', 1]'
                 || Q'[, 'Instance Background CPU', 2]'
                 || Q'[, 'Non-Database Host CPU'  , 3]'
                 || Q'[, 'Load Average'           , 4]'
                 || Q'[)]';

  v_sql := 'CREATE OR REPLACE VIEW arp_in_last_31_days_result AS ' || v_sql;

  EXECUTE IMMEDIATE v_sql;
END;
/
-- 
-- Running the previous procedure "pro_convert_rows_to_columns_5" to create view
-- "arp_in_last_31_days_result" to save the result of converting rows to columns dynamically.
-- 
-- Firstly executing "SET SQLFORMAT csv" on Oracle SQL Developer 21.2 next running the following SQL query
-- by clicking the button of "Run Script" or pressing F5 to show the CSV format, finally save this CSV file
-- "arp_5.csv" to your local computer.
-- 

EXECUTE pro_convert_rows_to_columns_5;

SET SQLFORMAT csv;

SELECT * FROM arp_in_last_31_days_result;

经过一系列的操作步骤之后（详见第一维度，这里省略。。。），最终展现出来的图表为：

[返回维度目录列表]

[返回顶部目录]

最近31天的平均可运行进程（按每天间隔）

同理，所有的SQL代码如下所示：

PROMPT ===================================================================
PROMPT  Average Runnable Processes in Last 31 Days (interval by each day)
PORMPT ===================================================================

SET LINESIZE 200
SET PAGESIZE 100

COLUMN sample_time FORMAT a12
COLUMN metric_name FORMAT a25
COLUMN value       FORMAT 999,999.99

-- 
-- Creating a view named "arp_in_last_31_days_2" to show "sample_time" and "value" based on
-- the four metrics amongst "Instance Foreground CPU", "Instance Background CPU",
-- "Non-Database Host CPU" and "Load Average" based on the view "arp_in_last_31_days"
-- in last 31 days (interval by each day).
-- 

CREATE OR REPLACE VIEW arp_in_last_31_days_2
AS
SELECT TO_CHAR(TO_DATE(sample_time, 'yyyy-mm-dd hh24:mi'), 'yyyy-mm-dd') sample_time
     , metric_name
     , ROUND(AVG(value), 2) value
FROM arp_in_last_31_days
GROUP BY TO_CHAR(TO_DATE(sample_time, 'yyyy-mm-dd hh24:mi'), 'yyyy-mm-dd')
       , metric_name
ORDER BY DECODE(metric_name, 'Instance Foreground CPU', 1
                           , 'Instance Background CPU', 2
                           , 'Non-Database Host CPU'  , 3
                           , 'Load Average'           , 4
               )
       , sample_time
;
-- 
-- Creating a procedure named "pro_convert_rows_to_columns_6" to dynamically convert "sample_time"
-- from rows to columns based on the previous view "arp_in_last_31_days_2".
-- 

CREATE OR REPLACE PROCEDURE pro_convert_rows_to_columns_6
AUTHID CURRENT_USER
IS
  v_sql VARCHAR2(4000);
  CURSOR cur_samp_time IS
  SELECT sample_time
  FROM arp_in_last_31_days_2
  WHERE metric_name = 'Instance Foreground CPU'
  ORDER BY sample_time;
BEGIN
  v_sql := Q'[SELECT metric_name]';

  FOR v_samp_time IN cur_samp_time
  LOOP
    v_sql := v_sql || Q'[, MAX(DECODE(sample_time, ']'
                   || v_samp_time.sample_time
                   || Q'[', value)) AS "]'
                   || v_samp_time.sample_time
                   || Q'["]';
  END LOOP;
  
  v_sql := v_sql || Q'[ FROM arp_in_last_31_days_2 GROUP BY metric_name]'
                 || Q'[ ORDER BY DECODE(metric_name,]'
                 || Q'[  'Instance Foreground CPU', 1]'
                 || Q'[, 'Instance Background CPU', 2]'
                 || Q'[, 'Non-Database Host CPU'  , 3]'
                 || Q'[, 'Load Average'           , 4]'
                 || Q'[)]';

  v_sql := 'CREATE OR REPLACE VIEW arp_in_last_31_days_2_result AS ' || v_sql;

  EXECUTE IMMEDIATE v_sql;
END;
/
-- 
-- Running the previous procedure "pro_convert_rows_to_columns_6" to create view
-- "arp_in_last_31_days_2_result" to save the result of converting rows to columns dynamically.
--  
-- Firstly executing "SET SQLFORMAT csv" on Oracle SQL Developer 21.2 next running the following SQL query
-- by clicking the button of "Run Script" or pressing F5 to show the CSV format, finally save this CSV file
-- "arp_6.csv" to your local computer.
-- 

EXECUTE pro_convert_rows_to_columns_6;

SET SQLFORMAT csv;

SELECT * FROM arp_in_last_31_days_2_result;

最后生成的图表见下图：

[返回维度目录列表]

[返回顶部目录]

自定义时间段的平均可运行进程（按每小时间隔）

因为这个维度是关于“自定义时间段”，所以在SQL*Plus中执行代码时，需要手动输入替代变量“开始时间”和“结束时间”之后才能创建成功视图“arp_custom_time_period”。代码部分和输入“替代变量”的操作分别如下所示：

PROMPT =======================================================================
PROMPT  Average Runnable Processes Custom Time Period (interval by each hour)
PORMPT =======================================================================

SET VERIFY OFF

SET LINESIZE 200
SET PAGESIZE 3000

COLUMN sample_time FORMAT a20
COLUMN metric_name FORMAT a25
COLUMN value       FORMAT 999,999.99

-- 
-- Creating a view named "arp_custom_time_period" to show "sample_time" and "value" based on
-- the four metrics amongst "Instance Foreground CPU", "Instance Background CPU",
-- "Non-Database Host CPU" and "Load Average" from dba_hist_sysmetric_summary
-- custom time period (interval by each hour).
-- 

CREATE OR REPLACE VIEW arp_custom_time_period
AS
WITH
ins_fg_cpu AS
(
  SELECT TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi') sample_time
       , DECODE(metric_name, 'CPU Usage Per Sec', 'Instance Foreground CPU') metric_name
       , ROUND(average/1e2, 2) value
  FROM dba_hist_sysmetric_summary
  WHERE metric_name = 'CPU Usage Per Sec'
  AND   group_id = 2
  AND   (end_time BETWEEN TO_DATE('&&start_date', 'yyyy-mm-dd hh24:mi')
                  AND     TO_DATE('&&end_date', 'yyyy-mm-dd hh24:mi')
        )
  ORDER BY sample_time
),
ins_bg_cpu AS
(
  SELECT TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi') sample_time
       , DECODE(metric_name, 'Background CPU Usage Per Sec', 'Instance Background CPU') metric_name
       , ROUND(average/1e2, 2) value
  FROM dba_hist_sysmetric_summary
  WHERE metric_name = 'Background CPU Usage Per Sec'
  AND   group_id = 2
  AND   (end_time BETWEEN TO_DATE('&&start_date', 'yyyy-mm-dd hh24:mi')
                  AND     TO_DATE('&&end_date', 'yyyy-mm-dd hh24:mi')
        )
  ORDER BY sample_time
),
host_cpu AS
(
  SELECT TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi') sample_time
       , DECODE(metric_name, 'Host CPU Usage Per Sec', 'Host CPU') metric_name
       , ROUND(average/1e2, 2) value
  FROM dba_hist_sysmetric_summary
  WHERE metric_name = 'Host CPU Usage Per Sec'
  AND   group_id = 2
  AND   (end_time BETWEEN TO_DATE('&&start_date', 'yyyy-mm-dd hh24:mi')
                  AND     TO_DATE('&&end_date', 'yyyy-mm-dd hh24:mi')
        )
  ORDER BY sample_time
),
non_db_host_cpu AS
(
  SELECT hc.sample_time
       , 'Non-Database Host CPU' metric_name
       , hc.value - fc.value - bc.value value
  FROM host_cpu hc
     , ins_fg_cpu fc
     , ins_bg_cpu bc
  WHERE hc.sample_time = fc.sample_time
  AND   fc.sample_time = bc.sample_time
  ORDER BY hc.sample_time
),
load_average AS
(
  SELECT TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi') sample_time
       , DECODE(metric_name, 'Current OS Load', 'Load Average') metric_name
       , ROUND(average, 2) value
  FROM dba_hist_sysmetric_summary
  WHERE metric_name = 'Current OS Load'
  AND   group_id = 2
  AND   (end_time BETWEEN TO_DATE('&&start_date', 'yyyy-mm-dd hh24:mi')
                  AND     TO_DATE('&&end_date', 'yyyy-mm-dd hh24:mi')
        )
  ORDER BY sample_time
)
SELECT * FROM ins_fg_cpu
UNION ALL
SELECT * FROM ins_bg_cpu
UNION ALL
SELECT * FROM non_db_host_cpu
UNION ALL
SELECT * FROM load_average
;


随后的代码依次为：

-- 
-- Creating a procedure named "pro_convert_rows_to_columns_7" to dynamically convert "sample_time"
-- from rows to columns based on the previous view "arp_custom_time_period".
-- 

CREATE OR REPLACE PROCEDURE pro_convert_rows_to_columns_7
AUTHID CURRENT_USER
IS
  v_sql CLOB;
  CURSOR cur_samp_time IS
  SELECT sample_time
  FROM arp_custom_time_period
  WHERE metric_name = 'Instance Foreground CPU'
  ORDER BY sample_time;
BEGIN
  v_sql := Q'[SELECT metric_name]';

  FOR v_samp_time IN cur_samp_time
  LOOP
    v_sql := v_sql || Q'[, MAX(DECODE(sample_time, ']'
                   || v_samp_time.sample_time
                   || Q'[', value)) AS "]'
                   || v_samp_time.sample_time
                   || Q'["]';
  END LOOP;
  
  v_sql := v_sql || Q'[ FROM arp_custom_time_period GROUP BY metric_name]'
                 || Q'[ ORDER BY DECODE(metric_name,]'
                 || Q'[  'Instance Foreground CPU', 1]'
                 || Q'[, 'Instance Background CPU', 2]'
                 || Q'[, 'Non-Database Host CPU'  , 3]'
                 || Q'[, 'Load Average'           , 4]'
                 || Q'[)]';

  v_sql := 'CREATE OR REPLACE VIEW arp_custom_time_period_result AS ' || v_sql;

  EXECUTE IMMEDIATE v_sql;
END;
/
-- 
-- Running the previous procedure "pro_convert_rows_to_columns_7" to create view
-- "arp_custom_time_period_result" to save the result of converting rows to columns dynamically.
-- 
-- Firstly executing "SET SQLFORMAT csv" on Oracle SQL Developer 21.2 next running the following SQL query
-- by clicking the button of "Run Script" or pressing F5 to show the CSV format, finally save this CSV file
-- "arp_7.csv" to your local computer.
-- 

EXECUTE pro_convert_rows_to_columns_7;

SET SQLFORMAT csv;

SELECT * FROM arp_custom_time_period_result;

最后的可视化图表是下面这个效果：

[返回维度目录列表]

[返回顶部目录]

自定义时间段的平均可运行进程（按每天间隔）

同理，所有代码依次为：

PROMPT ======================================================================
PROMPT  Average Runnable Processes Custom Time Period (interval by each day)
PORMPT ======================================================================

SET VERIFY OFF

SET LINESIZE 200
SET PAGESIZE 100

COLUMN sample_time FORMAT a12
COLUMN metric_name FORMAT a25
COLUMN value       FORMAT 999,999.99

-- 
-- Creating a view named "arp_custom_time_period_2" to show "sample_time" and "value" based on
-- the four metrics amongst "Instance Foreground CPU", "Instance Background CPU",
-- "Non-Database Host CPU" and "Load Average" based on the view "arp_custom_time_period"
-- custom time period (interval by each day).
-- 

CREATE OR REPLACE VIEW arp_custom_time_period_2
AS
SELECT TO_CHAR(TO_DATE(sample_time, 'yyyy-mm-dd hh24:mi'), 'yyyy-mm-dd') sample_time
     , metric_name
     , ROUND(AVG(value), 2) value
FROM arp_custom_time_period
GROUP BY TO_CHAR(TO_DATE(sample_time, 'yyyy-mm-dd hh24:mi'), 'yyyy-mm-dd')
       , metric_name
ORDER BY DECODE(metric_name, 'Instance Foreground CPU', 1
                           , 'Instance Background CPU', 2
                           , 'Non-Database Host CPU'  , 3
                           , 'Load Average'           , 4
               )
       , sample_time
;
-- 
-- Creating a procedure named "pro_convert_rows_to_columns_8" to dynamically convert "sample_time"
-- from rows to columns based on the previous view "arp_custom_time_period_2".
-- 

CREATE OR REPLACE PROCEDURE pro_convert_rows_to_columns_8
AUTHID CURRENT_USER
IS
  v_sql VARCHAR2(4000);
  CURSOR cur_samp_time IS
  SELECT sample_time
  FROM arp_custom_time_period_2
  WHERE metric_name = 'Instance Foreground CPU'
  ORDER BY sample_time;
BEGIN
  v_sql := Q'[SELECT metric_name]';

  FOR v_samp_time IN cur_samp_time
  LOOP
    v_sql := v_sql || Q'[, MAX(DECODE(sample_time, ']'
                   || v_samp_time.sample_time
                   || Q'[', value)) AS "]'
                   || v_samp_time.sample_time
                   || Q'["]';
  END LOOP;
  
  v_sql := v_sql || Q'[ FROM arp_custom_time_period_2 GROUP BY metric_name]'
                 || Q'[ ORDER BY DECODE(metric_name,]'
                 || Q'[  'Instance Foreground CPU', 1]'
                 || Q'[, 'Instance Background CPU', 2]'
                 || Q'[, 'Non-Database Host CPU'  , 3]'
                 || Q'[, 'Load Average'           , 4]'
                 || Q'[)]';

  v_sql := 'CREATE OR REPLACE VIEW arp_custom_time_period_2_res AS ' || v_sql;

  EXECUTE IMMEDIATE v_sql;
END;
/
-- 
-- Running the previous procedure "pro_convert_rows_to_columns_8" to create view
-- "arp_custom_time_period_2_res" to save the result of converting rows to columns dynamically.
-- 
-- Firstly executing "SET SQLFORMAT csv" on Oracle SQL Developer 21.2 next running the following SQL query
-- by clicking the button of "Run Script" or pressing F5 to show the CSV format, finally save this CSV file
-- "arp_8.csv" to your local computer.
-- 

EXECUTE pro_convert_rows_to_columns_8;

SET SQLFORMAT csv;

SELECT * FROM arp_custom_time_period_2_res;

最终的可视化图表见下图：


[返回维度目录列表]

[返回顶部目录]

存储过程和视图

将“八个维度”的图表可视化完成以后，全部SQL代码生成所有的“存储过程”和“视图”详见下图所示：



至此，可视化Oracle性能图表之“平均可运行进程”篇全部分享完毕（另外，您也可以查看我在GitHub上的全部SQL源代码、CSV文件和Excel图表）。感谢您在百忙之中抽出一点宝贵时间来阅读我的这篇原创文章，欢迎给我任何反馈或提出改进建议！！！

[返回顶部目录]

参考文章

[返回顶部目录]

更新于 2021年11月23日 上午：

  • 在文章每个段落的底部，增加诸如“[返回最近1小时的平均可运行进程]”、“[返回维度目录列表]”、“[返回顶部目录]”的在多段落之间自自跳转的返回链接，这个功能非常棒哟！它类似于“AWR报告”中的每个条目底部的“Back to the prior parent entry”和“Back to Top”链接。

更新于 2021年11月25日 中午：

DarkAthena在评论区给我的回复特别棒，他说在Excel中我原先的表格完全可以将X/Y轴进行互换（将四个度量名称都放在表头，而采样时间和所有的值放在表的第一列），随后我进行了测试，这是完全可行的，而且也特别简单，这样就可以不用再创建“视图”和“存储过程”，只需将我最初的SQL查询进行稍微的改良（这里用到“静态行转列”）就可以快速地导入到Excel从而进行图表的可视化操作，如下两图所示：



这里，我使用了经典的行转列函数“MAX(DECODE(......)) GROUP BY ...”和Oracle从11g开始支持的“PIVOT() / UNPIVOT()”函数这两种方法来进行行转列操作。以“最近1小时的平均可运行进程”这个维度举例来说明：

PROMPT ===========================================
PROMPT  Average Runnable Processes in Last 1 Hour
PORMPT ===========================================

-- Statically Converting Rows to Columns by "MAX(DECODE(...)) GROUP BY ...".

SET LINESIZE 200
SET PAGESIZE 300

COLUMN sample_time FORMAT a11
COLUMN metric_name FORMAT a25
COLUMN value       FORMAT 999,999.99

WITH
ins_fg_cpu AS
(
  SELECT TO_CHAR(end_time, 'hh24:mi:ss') sample_time
       , DECODE(metric_name, 'CPU Usage Per Sec', 'Instance Foreground CPU') metric_name
       , ROUND(value/1e2, 2) value
  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
),
ins_bg_cpu AS
(
  SELECT TO_CHAR(end_time, 'hh24:mi:ss') sample_time
       , DECODE(metric_name, 'Background CPU Usage Per Sec', 'Instance Background CPU') metric_name
       , ROUND(value/1e2, 2) value
  FROM v$sysmetric_history
  WHERE metric_name = 'Background CPU Usage Per Sec'
  AND   group_id = 2
  AND   end_time >= SYSDATE - INTERVAL '60' MINUTE
  ORDER BY sample_time
),
host_cpu AS
(
  SELECT TO_CHAR(end_time, 'hh24:mi:ss') sample_time
       , DECODE(metric_name, 'Host CPU Usage Per Sec', 'Host CPU') metric_name
       , ROUND(value/1e2, 2) value
  FROM v$sysmetric_history
  WHERE metric_name = 'Host CPU Usage Per Sec'
  AND   group_id = 2
  AND   end_time >= SYSDATE - INTERVAL '60' MINUTE
  ORDER BY sample_time
),
non_db_host_cpu AS
(
  SELECT hc.sample_time
       , 'Non-Database Host CPU' metric_name
       , hc.value - fc.value - bc.value value
  FROM host_cpu hc
     , ins_fg_cpu fc
     , ins_bg_cpu bc
  WHERE hc.sample_time = fc.sample_time
  AND   fc.sample_time = bc.sample_time
  ORDER BY hc.sample_time
),
load_average AS
(
  SELECT TO_CHAR(end_time, 'hh24:mi:ss') sample_time
       , DECODE(metric_name, 'Current OS Load', 'Load Average') metric_name
       , ROUND(value, 2) value
  FROM v$sysmetric_history
  WHERE metric_name = 'Current OS Load'
  AND   group_id = 2
  AND   end_time >= SYSDATE - INTERVAL '60' MINUTE
  ORDER BY sample_time
),
arp AS
(
  SELECT * FROM ins_fg_cpu
  UNION ALL
  SELECT * FROM ins_bg_cpu
  UNION ALL
  SELECT * FROM non_db_host_cpu
  UNION ALL
  SELECT * FROM load_average
)
SELECT sample_time
     , MAX(DECODE(metric_name, 'Instance Foreground CPU', value)) "Instance Foreground CPU"
     , MAX(DECODE(metric_name, 'Instance Background CPU', value)) "Instance Background CPU"
     , MAX(DECODE(metric_name, 'Non-Database Host CPU'  , value)) "Non-Database Host CPU"
     , MAX(DECODE(metric_name, 'Load Average'           , value)) "Load Average"
FROM arp
GROUP BY sample_time
ORDER BY sample_time
;
-- Statically Converting Rows to Columns by "SELECT * FROM table_name PIVOT (MAX(column_name_1) FOR column_name_2 IN ())".

SET LINESIZE 200
SET PAGESIZE 300

COLUMN sample_time FORMAT a11
COLUMN metric_name FORMAT a25
COLUMN value       FORMAT 999,999.99

WITH
ins_fg_cpu AS
(
  SELECT TO_CHAR(end_time, 'hh24:mi:ss') sample_time
       , DECODE(metric_name, 'CPU Usage Per Sec', 'Instance Foreground CPU') metric_name
       , ROUND(value/1e2, 2) value
  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
),
ins_bg_cpu AS
(
  SELECT TO_CHAR(end_time, 'hh24:mi:ss') sample_time
       , DECODE(metric_name, 'Background CPU Usage Per Sec', 'Instance Background CPU') metric_name
       , ROUND(value/1e2, 2) value
  FROM v$sysmetric_history
  WHERE metric_name = 'Background CPU Usage Per Sec'
  AND   group_id = 2
  AND   end_time >= SYSDATE - INTERVAL '60' MINUTE
  ORDER BY sample_time
),
host_cpu AS
(
  SELECT TO_CHAR(end_time, 'hh24:mi:ss') sample_time
       , DECODE(metric_name, 'Host CPU Usage Per Sec', 'Host CPU') metric_name
       , ROUND(value/1e2, 2) value
  FROM v$sysmetric_history
  WHERE metric_name = 'Host CPU Usage Per Sec'
  AND   group_id = 2
  AND   end_time >= SYSDATE - INTERVAL '60' MINUTE
  ORDER BY sample_time
),
non_db_host_cpu AS
(
  SELECT hc.sample_time
       , 'Non-Database Host CPU' metric_name
       , hc.value - fc.value - bc.value value
  FROM host_cpu hc
     , ins_fg_cpu fc
     , ins_bg_cpu bc
  WHERE hc.sample_time = fc.sample_time
  AND   fc.sample_time = bc.sample_time
  ORDER BY hc.sample_time
),
load_average AS
(
  SELECT TO_CHAR(end_time, 'hh24:mi:ss') sample_time
       , DECODE(metric_name, 'Current OS Load', 'Load Average') metric_name
       , ROUND(value, 2) value
  FROM v$sysmetric_history
  WHERE metric_name = 'Current OS Load'
  AND   group_id = 2
  AND   end_time >= SYSDATE - INTERVAL '60' MINUTE
  ORDER BY sample_time
),
arp AS
(
  SELECT * FROM ins_fg_cpu
  UNION ALL
  SELECT * FROM ins_bg_cpu
  UNION ALL
  SELECT * FROM non_db_host_cpu
  UNION ALL
  SELECT * FROM load_average
)
SELECT *
FROM arp
PIVOT ( MAX(value)
        FOR metric_name IN
        (  'Instance Foreground CPU' AS "Instance Foreground CPU"
         , 'Instance Background CPU' AS "Instance Background CPU"
         , 'Non-Database Host CPU'   AS "Non-Database Host CPU"
         , 'Load Average'            AS "Load Average"
        )
      )
ORDER BY sample_time
;

其余七个维度的SQL代码请查看这里，因此不再贴出。

更新于 2021年11月30日 下午：

为了让通过Microsoft Office Excel可视化图表的图例颜色完全等同于EMCC中的图表图例颜色，强烈建议在这个网站上传图例图片（可以截取EMCC图表的图例保存成本地图片），使其识别出相应的RGB颜色值（十六进制），然后再使用在线进制转换得到我们想要的RGB颜色值（十进制）。本篇文章中使用到的RGB颜色值是这样的：

-- Each Legend Color from the Graph of "Average Runnable Processes" of EMCC 13.5.

Instance Foreground CPU, #35C387 -> RGB (53 , 195, 135)
Instance Background CPU, #A9F89C -> RGB (169, 248, 156)
Non-Database Host CPU  , #9A9595 -> RGB (154, 149, 149)
Load Average           , #7A7A7A -> RGB (122, 122, 122)
墨力计划oracle静态和动态行转列视图存储过程
最后修改时间：2021-11-30 14:48:48
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
4人已赞赏
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论