暂无图片
暂无图片
5
暂无图片
暂无图片
暂无图片
首页 / 可视化Oracle性能图表之“平均活动会话”篇

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

原创 赵全文 2021-11-05
3804

库尔特·恩格莱特、约翰·贝雷斯涅维奇、塞西莉亚·格瓦西奥在”最大化数据库性能：使用数据库时间进行性能调整“的PPT第17页中提到，平均活动会话 = 所有会话的平均活动总和 (即:数据库时间) / 经过的时间。原文PPT描述如下图所示：


正如您所看到的，Average Active Sessions = DB Time / Elapsed Time. 有关该PPT的其它内容请查看这个PDF文档自行脑补。虽然“平均活动会话”我们可以通过前面描述的公式计算出来，不过您可能会问，那什么是DB Time和Elapsed Time? 在Oracle 19c数据库性能调优指导的官方文档中是这样描述DB Time的，请读下面我引用的一段介绍：

Time Model Statistics

Time model statistics use time to identify quantitative effects about specific actions performed on the database, such as logon operations and parsing. The most important time model statistic is database time, or DB time. This statistic represents the total time spent in database calls for foreground sessions and is an indicator of the total instance workload. DB time is measured cumulatively from the time of instance startup and is calculated by aggregating the CPU and wait times of all foreground sessions not waiting on idle wait events (non-idle user sessions).

Time model statistics are accessible from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.

翻译成中文的意思是：

时间模型统计

时间模型统计使用时间来确定对数据库执行的特定操作（如登录操作和解析）的定量影响。最重要的时间模型统计数据是数据库时间或数据库时间。此统计信息表示前台会话的数据库调用所花费的总时间，是实例总工作负载的指标。DB时间是从实例启动时开始累积测量的，通过聚合所有未等待空闲等待事件（非空闲用户会话）的前台会话的CPU和等待时间来计算。

可以从V$SESS_TIME_MODEL和V$SYS_TIME_MODEL视图中访问时间模型统计信息。

现在您已经知道，数据库时间来源于时间模型统计。另外，官方文档也提到了AWR报告中会收集时间模型统计信息，是这样描述的：

Automatic Workload Repository

AWR collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. This gathered data is stored both in memory and in the database, and is displayed in both reports and views.

The statistics collected and processed by AWR include:

  • Object statistics that determine both access and usage statistics of database segments

  • Time model statistics based on time usage for activities, displayed in the V$SYS_TIME_MODEL and V$SESS_TIME_MODEL views

  • Some of the system and session statistics collected in the V$SYSSTAT and V$SESSTAT views

  • SQL statements that are producing the highest load on the system, based on criteria such as elapsed time and CPU time

  • Active Session History (ASH) statistics, representing the history of recent sessions activity

翻译成中文是：

自动工作负载存储库

AWR收集、处理和维护性能统计数据，以便进行问题检测和自我调整。收集的数据存储在内存和数据库中，并显示在报告和视图中。

AWR收集和处理的统计数据包括：

  • 确定数据库段的访问和使用统计信息的对象统计信息
  • 基于活动时间使用情况的时间模型统计信息，显示在V$SYS_TIME_MODEL和V$SESS_TIME_MODEL视图中
  • V$SYSSTAT和V$SESSTAT视图中收集的一些系统和会话统计信息
  • 根据运行时间和CPU时间等条件在系统上产生最高负载的SQL语句
  • 活动会话历史记录（ASH）统计信息，表示最近会话活动的历史记录

换句话说，我们可以通过AWR报告来获取DB Time。官方文档是这样解释AWR报告的：

An AWR report shows data captured between two snapshots (or two points in time). AWR reports are divided into multiple sections. The content of the report contains the workload profile of the system for the selected range of snapshots. The HTML report includes links that can be used to navigate quickly between sections.


This section describes how to generate AWR reports and contains the following topics:

翻译成中文是：

AWR报告显示两个快照（或两个时间点）之间捕获的数据。AWR报告分为多个部分。报告内容包含所选快照范围内系统的工作负载配置文件。HTML报告包含可用于在节之间快速导航的链接。

本节介绍如何生成AWR报告，并包含以下主题：

  • 用于生成AWR报告的用户界面
  • 使用命令行界面生成AWR报告

这里，我通过EMCC用户界面收集AWR报告的步骤，详见下面两张屏幕截图：



最终AWR报告中显示的DB Time和Elapsed Time是：


所以，这个数据库的平均活动会话是 DB Time / Elapsed Time = 61.01 mins / 60.24 mins = 1.013。我们知道，Oracle默认每隔一小时会自动生成一个快照，而且每个快照的结束时间是下一个快照的开始时间，所以这些快照的采集时间都是连续的，除非期间有数据库的实例重启过。如果我们每次都要通过收集AWR报告来计算平均活动会话，这显然是不可取的，而且我们计算出的平均活动会话都是间隔一小时（连续两个快照）的时间，如果要计算更加细粒度的间隔时间（如：1分钟）的平均活动会话，那么AWR报告不能满足这个需求了。

可喜的是，Oracle从10g版本开始，提供了V$SYSMETRIC视图，它显示长持续时间（60秒）和短持续时间（15秒）系统度量的最新时间间隔捕获的系统度量值；提供了V$SYSMETRIC_HISTORY视图，它显示数据库中可用的所有系统度量值，此视图同时显示长持续时间（60秒，1小时历史记录）和短持续时间（15秒，仅一个间隔）指标；提供了DBA_HIST_SYSMETRIC_SUMMARY视图，它显示系统度量长持续时间组中所有度量值的统计摘要的历史记录。这些视图中的所有列名及其含义分别见下图所示：




我们通过查询视图V$SYSMETRIC中的列名METRIC_NAME和METRIC_UNIT可以得知，有一个度量名为Average Active Sessions和度量单元为Active Sessions。

SQL> 
SQL> SET LINESIZE 100
SQL> COLUMN metric_name FORMAT a25
SQL> COLUMN metric_unit FORMAT a15
SQL> 
SQL> SELECT DISTINCT metric_name
SQL>      , metric_unit
SQL> FROM v$sysmetric
SQL> WHERE metric_name = 'Average Active Sessions'
SQL> ;

METRIC_NAME               METRIC_UNIT
------------------------- ---------------
Average Active Sessions   Active Sessions

SQL>

接下来，我们从下面的八个维度来查看数据库的平均活动会话（Average Active Sessions，简称：AAS）。

维度目录列表

实时的平均活动会话

SQL查询语句如下：

-- Average Active Sessions in Real Time.

SET LINESIZE 200
SET PAGESIZE 200

COLUMN metric_name    FORMAT a25
COLUMN snap_date_time FORMAT a20
COLUMN aas            FORMAT 999,999.99

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

SELECT TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi:ss') snap_date_time  -- the group column
     , metric_name                                                -- the series column
     , ROUND(value, 2) aas                                        -- the value column
FROM v$sysmetric_history
WHERE metric_name = 'Average Active Sessions'
AND   group_id = 2                                                -- just retrieve the name with "System Metrics Long Duration" in v$metricgroup
ORDER BY snap_date_time
;

查询结果为：

SNAP_DATE_TIME       METRIC_NAME                       AAS
-------------------- ------------------------- -----------
2021-11-05 15:25:14  Average Active Sessions          1.10
2021-11-05 15:26:13  Average Active Sessions           .78
2021-11-05 15:27:14  Average Active Sessions           .75
2021-11-05 15:28:14  Average Active Sessions          1.21
2021-11-05 15:29:13  Average Active Sessions          1.32
2021-11-05 15:30:14  Average Active Sessions          2.53
2021-11-05 15:31:14  Average Active Sessions          3.42
2021-11-05 15:32:14  Average Active Sessions          2.63
2021-11-05 15:33:13  Average Active Sessions          2.38
2021-11-05 15:34:14  Average Active Sessions          2.97
2021-11-05 15:35:14  Average Active Sessions          4.25
2021-11-05 15:36:14  Average Active Sessions          1.51
2021-11-05 15:37:14  Average Active Sessions          3.30
2021-11-05 15:38:14  Average Active Sessions          3.40
2021-11-05 15:39:14  Average Active Sessions          3.23
2021-11-05 15:40:14  Average Active Sessions           .94
2021-11-05 15:41:14  Average Active Sessions          1.33
2021-11-05 15:42:14  Average Active Sessions          1.49
2021-11-05 15:43:14  Average Active Sessions          1.42
2021-11-05 15:44:14  Average Active Sessions          1.82
2021-11-05 15:45:14  Average Active Sessions          2.47
2021-11-05 15:46:14  Average Active Sessions          1.80
2021-11-05 15:47:14  Average Active Sessions          1.71
2021-11-05 15:48:14  Average Active Sessions          1.21
2021-11-05 15:49:14  Average Active Sessions          2.07
2021-11-05 15:50:14  Average Active Sessions          1.77
2021-11-05 15:51:14  Average Active Sessions           .90
2021-11-05 15:52:14  Average Active Sessions          1.88
2021-11-05 15:53:14  Average Active Sessions          1.01
2021-11-05 15:54:14  Average Active Sessions          2.14
2021-11-05 15:55:14  Average Active Sessions          1.62
2021-11-05 15:56:14  Average Active Sessions          1.83
2021-11-05 15:57:14  Average Active Sessions          2.72
2021-11-05 15:58:15  Average Active Sessions          2.40
2021-11-05 15:59:14  Average Active Sessions          1.44
2021-11-05 16:00:14  Average Active Sessions          1.17
2021-11-05 16:01:15  Average Active Sessions          2.08
2021-11-05 16:02:14  Average Active Sessions          1.01
2021-11-05 16:03:14  Average Active Sessions          1.77
2021-11-05 16:04:14  Average Active Sessions          2.05
2021-11-05 16:05:14  Average Active Sessions          1.26
2021-11-05 16:06:15  Average Active Sessions          1.63
2021-11-05 16:07:14  Average Active Sessions          2.26
2021-11-05 16:08:14  Average Active Sessions           .85
2021-11-05 16:09:14  Average Active Sessions          1.43
2021-11-05 16:10:14  Average Active Sessions          2.81
2021-11-05 16:11:15  Average Active Sessions          2.99
2021-11-05 16:12:14  Average Active Sessions          2.42
2021-11-05 16:13:14  Average Active Sessions          1.45
2021-11-05 16:14:14  Average Active Sessions          1.71
2021-11-05 16:15:14  Average Active Sessions          1.36
2021-11-05 16:16:14  Average Active Sessions          1.98
2021-11-05 16:17:14  Average Active Sessions          1.18
2021-11-05 16:18:15  Average Active Sessions          2.87
2021-11-05 16:19:15  Average Active Sessions          2.77
2021-11-05 16:20:14  Average Active Sessions          1.33
2021-11-05 16:21:14  Average Active Sessions          2.66
2021-11-05 16:22:14  Average Active Sessions           .78
2021-11-05 16:23:14  Average Active Sessions          1.53
2021-11-05 16:24:14  Average Active Sessions          1.28
2021-11-05 16:25:15  Average Active Sessions           .78

61 rows selected.

有了上面的SQL查询和数据，但是我们如何生成相应的可视化图表来直观地显示数据的变化趋势呢？幸运的是，Oracle SQL Developer（最新版本为21.2.1，从这里可以下载适用于各个平台的版本）给我们提供了非常方便的用户自定义报告，只需10个步骤，就可以快速地生成“实时的平均活动会话”的用户自定义报告，所有操作依次如下图：











最终效果图，是这样的。


如果您觉得前面的操作步骤有些繁琐，也可以直接从我的GitHub代码仓库下载这个XML文件并保存到您的电脑，然后打开SQL Developer直接导入到用户自定义报告“Average Active Sessions”文件夹当中（第1、2步创建文件夹必不可少）也是可以的。

[返回维度目录列表]

最近24小时的平均活动会话

SQL查询语句如下：

-- Average Active Sessions in Last 24 Hours.

SET LINESIZE 200
SET PAGESIZE 200

COLUMN metric_name    FORMAT a25
COLUMN snap_date_time FORMAT a20
COLUMN aas            FORMAT 999,999.99

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

SELECT TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi:ss') snap_date_time  -- the group column
     , metric_name                                                -- the series column
     , ROUND(average, 2) aas                                      -- the value column
FROM dba_hist_sysmetric_summary
WHERE metric_name = 'Average Active Sessions'
AND   end_time >= SYSDATE - 1
ORDER BY snap_date_time
;

查询结果为：

SNAP_DATE_TIME       METRIC_NAME                       AAS
-------------------- ------------------------- -----------
2021-11-04 17:00:02  Average Active Sessions          1.44
2021-11-04 18:00:03  Average Active Sessions          1.24
2021-11-04 19:00:03  Average Active Sessions           .66
2021-11-04 20:00:03  Average Active Sessions           .66
2021-11-04 21:00:03  Average Active Sessions          1.12
2021-11-04 22:00:05  Average Active Sessions           .82
2021-11-04 23:00:05  Average Active Sessions          1.46
2021-11-05 00:00:06  Average Active Sessions           .59
2021-11-05 01:00:06  Average Active Sessions           .36
2021-11-05 02:00:06  Average Active Sessions           .27
2021-11-05 03:00:07  Average Active Sessions           .25
2021-11-05 04:00:07  Average Active Sessions           .59
2021-11-05 05:00:08  Average Active Sessions           .36
2021-11-05 06:00:09  Average Active Sessions           .28
2021-11-05 07:00:10  Average Active Sessions           .34
2021-11-05 08:00:10  Average Active Sessions           .61
2021-11-05 09:00:11  Average Active Sessions           .99
2021-11-05 10:00:11  Average Active Sessions          1.22
2021-11-05 11:00:11  Average Active Sessions          1.12
2021-11-05 12:00:12  Average Active Sessions           .99
2021-11-05 12:59:13  Average Active Sessions          1.48
2021-11-05 14:00:13  Average Active Sessions          1.00
2021-11-05 15:00:13  Average Active Sessions          1.55
2021-11-05 16:00:14  Average Active Sessions          1.76

24 rows selected.

同理，将这个XML文件保存到您的电脑，然后直接导入到SQL Developer的用户自定义报告的“Average Active Sessions”文件夹当中。最终，生成的图表如下所示：

[返回维度目录列表]

最近7天的平均活动会话（按每小时间隔）

SQL查询语句如下：

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

SET LINESIZE 200
SET PAGESIZE 200

COLUMN metric_name    FORMAT a25
COLUMN snap_date_time FORMAT a20
COLUMN aas            FORMAT 999,999.99

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

SELECT TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi:ss') snap_date_time  -- the group column
     , metric_name                                                -- the series column
     , ROUND(average, 2) aas                                      -- the value column
FROM dba_hist_sysmetric_summary
WHERE metric_name = 'Average Active Sessions'
AND   end_time >= SYSDATE - 6
ORDER BY snap_date_time
;

查询结果为：

SNAP_DATE_TIME       METRIC_NAME                       AAS
-------------------- ------------------------- -----------
2021-10-30 16:59:56  Average Active Sessions          1.07
2021-10-30 17:59:56  Average Active Sessions          1.02
2021-10-30 18:59:57  Average Active Sessions          1.40
2021-10-30 19:59:57  Average Active Sessions          1.13
2021-10-30 20:59:58  Average Active Sessions          1.06
2021-10-30 21:59:58  Average Active Sessions          1.20
2021-10-30 22:59:59  Average Active Sessions          1.03
2021-10-31 00:00:00  Average Active Sessions           .55
2021-10-31 01:00:00  Average Active Sessions           .35
2021-10-31 02:00:01  Average Active Sessions           .26
2021-10-31 03:00:01  Average Active Sessions           .22
2021-10-31 04:00:02  Average Active Sessions           .49
2021-10-31 05:00:02  Average Active Sessions           .22
2021-10-31 06:00:03  Average Active Sessions           .23
2021-10-31 07:00:04  Average Active Sessions           .28
2021-10-31 08:00:05  Average Active Sessions           .39
2021-10-31 09:00:05  Average Active Sessions           .46
2021-10-31 10:00:05  Average Active Sessions          1.00
2021-10-31 11:00:06  Average Active Sessions          1.19
2021-10-31 12:00:06  Average Active Sessions           .72
2021-10-31 13:00:07  Average Active Sessions           .91
2021-10-31 14:00:08  Average Active Sessions           .71
2021-10-31 15:00:08  Average Active Sessions           .61
2021-10-31 16:00:08  Average Active Sessions           .91
2021-10-31 17:00:09  Average Active Sessions           .52
2021-10-31 17:59:09  Average Active Sessions           .63
2021-10-31 19:00:10  Average Active Sessions           .89
2021-10-31 20:00:10  Average Active Sessions           .55
2021-10-31 21:00:12  Average Active Sessions          1.13
2021-10-31 22:00:11  Average Active Sessions           .90
2021-10-31 22:59:12  Average Active Sessions           .74
2021-11-01 00:00:12  Average Active Sessions           .51
2021-11-01 01:00:13  Average Active Sessions           .32
2021-11-01 02:00:14  Average Active Sessions           .25
2021-11-01 03:00:14  Average Active Sessions           .22
2021-11-01 04:00:15  Average Active Sessions           .59
2021-11-01 04:59:16  Average Active Sessions           .21
2021-11-01 06:00:16  Average Active Sessions           .21
2021-11-01 07:00:17  Average Active Sessions           .23
2021-11-01 08:00:17  Average Active Sessions           .51
2021-11-01 09:00:18  Average Active Sessions          1.24
2021-11-01 09:59:18  Average Active Sessions          1.93
2021-11-01 11:00:19  Average Active Sessions          1.98
2021-11-01 12:00:19  Average Active Sessions          1.58
2021-11-01 13:00:20  Average Active Sessions           .91
2021-11-01 14:00:20  Average Active Sessions           .66
2021-11-01 14:59:21  Average Active Sessions          1.19
2021-11-01 16:00:22  Average Active Sessions          1.45
2021-11-01 17:00:22  Average Active Sessions          1.26
2021-11-01 18:00:23  Average Active Sessions           .69
2021-11-01 18:59:24  Average Active Sessions           .57
2021-11-01 20:00:24  Average Active Sessions           .92
2021-11-01 21:00:24  Average Active Sessions           .70
2021-11-01 22:00:24  Average Active Sessions           .58
2021-11-01 22:59:26  Average Active Sessions           .83
2021-11-01 23:59:26  Average Active Sessions           .56
2021-11-02 00:59:27  Average Active Sessions           .28
2021-11-02 02:00:27  Average Active Sessions           .23
2021-11-02 03:00:27  Average Active Sessions           .20
2021-11-02 04:00:28  Average Active Sessions           .53
2021-11-02 04:59:29  Average Active Sessions           .22
2021-11-02 05:59:29  Average Active Sessions           .20
2021-11-02 06:59:30  Average Active Sessions           .22
2021-11-02 08:00:30  Average Active Sessions           .30
2021-11-02 09:00:31  Average Active Sessions           .68
2021-11-02 10:00:31  Average Active Sessions           .83
2021-11-02 10:59:32  Average Active Sessions           .96
2021-11-02 11:59:33  Average Active Sessions           .84
2021-11-02 13:00:33  Average Active Sessions           .53
2021-11-02 14:00:34  Average Active Sessions           .54
2021-11-02 14:59:35  Average Active Sessions           .66
2021-11-02 15:59:34  Average Active Sessions          1.16
2021-11-02 17:00:35  Average Active Sessions           .91
2021-11-02 18:00:36  Average Active Sessions          1.05
2021-11-02 18:59:36  Average Active Sessions           .57
2021-11-02 19:59:37  Average Active Sessions           .53
2021-11-02 20:59:38  Average Active Sessions           .66
2021-11-02 22:00:38  Average Active Sessions           .83
2021-11-02 23:00:39  Average Active Sessions          1.28
2021-11-02 23:59:39  Average Active Sessions           .31
2021-11-03 00:59:40  Average Active Sessions           .25
2021-11-03 01:59:40  Average Active Sessions           .23
2021-11-03 02:59:41  Average Active Sessions           .21
2021-11-03 04:00:41  Average Active Sessions           .68
2021-11-03 05:00:42  Average Active Sessions           .21
2021-11-03 05:59:42  Average Active Sessions           .20
2021-11-03 06:59:43  Average Active Sessions           .22
2021-11-03 07:59:43  Average Active Sessions           .29
2021-11-03 08:59:44  Average Active Sessions           .68
2021-11-03 10:00:45  Average Active Sessions           .98
2021-11-03 10:59:46  Average Active Sessions          1.62
2021-11-03 11:59:46  Average Active Sessions          1.00
2021-11-03 12:59:46  Average Active Sessions           .93
2021-11-03 13:59:47  Average Active Sessions           .55
2021-11-03 14:59:47  Average Active Sessions           .74
2021-11-03 15:59:48  Average Active Sessions          1.05
2021-11-03 16:59:48  Average Active Sessions           .95
2021-11-03 17:59:49  Average Active Sessions           .96
2021-11-03 19:00:50  Average Active Sessions           .74
2021-11-03 19:59:50  Average Active Sessions           .51
2021-11-03 20:59:51  Average Active Sessions           .56
2021-11-03 21:59:52  Average Active Sessions           .59
2021-11-03 22:59:52  Average Active Sessions           .50
2021-11-04 00:00:53  Average Active Sessions           .37
2021-11-04 00:59:53  Average Active Sessions           .28
2021-11-04 01:59:53  Average Active Sessions           .23
2021-11-04 02:59:54  Average Active Sessions           .21
2021-11-04 03:59:54  Average Active Sessions           .45
2021-11-04 04:59:55  Average Active Sessions           .24
2021-11-04 05:59:55  Average Active Sessions           .21
2021-11-04 06:59:56  Average Active Sessions           .25
2021-11-04 07:59:56  Average Active Sessions           .30
2021-11-04 08:59:57  Average Active Sessions           .71
2021-11-04 09:59:58  Average Active Sessions          1.47
2021-11-04 10:59:59  Average Active Sessions          1.28
2021-11-04 11:59:59  Average Active Sessions          1.15
2021-11-04 13:00:00  Average Active Sessions           .71
2021-11-04 14:00:00  Average Active Sessions           .75
2021-11-04 15:00:01  Average Active Sessions          1.14
2021-11-04 16:00:01  Average Active Sessions          1.78
2021-11-04 17:00:02  Average Active Sessions          1.44
2021-11-04 18:00:03  Average Active Sessions          1.24
2021-11-04 19:00:03  Average Active Sessions           .66
2021-11-04 20:00:03  Average Active Sessions           .66
2021-11-04 21:00:03  Average Active Sessions          1.12
2021-11-04 22:00:05  Average Active Sessions           .82
2021-11-04 23:00:05  Average Active Sessions          1.46
2021-11-05 00:00:06  Average Active Sessions           .59
2021-11-05 01:00:06  Average Active Sessions           .36
2021-11-05 02:00:06  Average Active Sessions           .27
2021-11-05 03:00:07  Average Active Sessions           .25
2021-11-05 04:00:07  Average Active Sessions           .59
2021-11-05 05:00:08  Average Active Sessions           .36
2021-11-05 06:00:09  Average Active Sessions           .28
2021-11-05 07:00:10  Average Active Sessions           .34
2021-11-05 08:00:10  Average Active Sessions           .61
2021-11-05 09:00:11  Average Active Sessions           .99
2021-11-05 10:00:11  Average Active Sessions          1.22
2021-11-05 11:00:11  Average Active Sessions          1.12
2021-11-05 12:00:12  Average Active Sessions           .99
2021-11-05 12:59:13  Average Active Sessions          1.48
2021-11-05 14:00:13  Average Active Sessions          1.00
2021-11-05 15:00:13  Average Active Sessions          1.55
2021-11-05 16:00:14  Average Active Sessions          1.76

144 rows selected.

同理，保存该XML文件到您的SQL Developer的自定义报告当中。最后，生成的图表为：

[返回维度目录列表]

最近7天的平均活动会话（按每天间隔）

SQL查询语句如下：

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

SET LINESIZE 200
SET PAGESIZE 200

COLUMN metric_name FORMAT a25
COLUMN snap_date   FORMAT a12
COLUMN aas         FORMAT 999,999.99

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

WITH aas_per_hour AS (
SELECT 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
)
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
ORDER BY snap_date
;

查询结果为：

SNAP_DATE    METRIC_NAME                       AAS
------------ ------------------------- -----------
2021-10-30   Average Active Sessions          1.13
2021-10-31   Average Active Sessions           .62
2021-11-01   Average Active Sessions           .80
2021-11-02   Average Active Sessions           .61
2021-11-03   Average Active Sessions           .64
2021-11-04   Average Active Sessions           .79
2021-11-05   Average Active Sessions           .81

7 rows selected.

同理，保存这个XML文件并导入您的SQL Developer当中。最终的图表效果如下所示：

[返回维度目录列表]

最近31天的平均活动会话（按每小时间隔）

SQL查询语句如下：

-- Average Active Sessions in Last 31 Days (interval by each hour).

SET LINESIZE 200
SET PAGESIZE 200

COLUMN metric_name    FORMAT a25
COLUMN snap_date_time FORMAT a20
COLUMN aas            FORMAT 999,999.99

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

SELECT TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi:ss') snap_date_time  -- the group column
     , metric_name                                                -- the series column
     , ROUND(average, 2) aas                                      -- the value column
FROM dba_hist_sysmetric_summary
WHERE metric_name = 'Average Active Sessions'
AND   end_time >= SYSDATE - 30
ORDER BY snap_date_time
;

查询结果为：

SNAP_DATE_TIME       METRIC_NAME                       AAS
-------------------- ------------------------- -----------
......               ......                           ....
2021-10-31 08:00:05  Average Active Sessions           .39
2021-10-31 09:00:05  Average Active Sessions           .46
2021-10-31 10:00:05  Average Active Sessions          1.00
2021-10-31 11:00:06  Average Active Sessions          1.19
2021-10-31 12:00:06  Average Active Sessions           .72
2021-10-31 13:00:07  Average Active Sessions           .91
2021-10-31 14:00:08  Average Active Sessions           .71
2021-10-31 15:00:08  Average Active Sessions           .61
2021-10-31 16:00:08  Average Active Sessions           .91
2021-10-31 17:00:09  Average Active Sessions           .52
2021-10-31 17:59:09  Average Active Sessions           .63
2021-10-31 19:00:10  Average Active Sessions           .89
2021-10-31 20:00:10  Average Active Sessions           .55
2021-10-31 21:00:12  Average Active Sessions          1.13
2021-10-31 22:00:11  Average Active Sessions           .90
2021-10-31 22:59:12  Average Active Sessions           .74
2021-11-01 00:00:12  Average Active Sessions           .51
2021-11-01 01:00:13  Average Active Sessions           .32
2021-11-01 02:00:14  Average Active Sessions           .25
2021-11-01 03:00:14  Average Active Sessions           .22
2021-11-01 04:00:15  Average Active Sessions           .59
2021-11-01 04:59:16  Average Active Sessions           .21
2021-11-01 06:00:16  Average Active Sessions           .21
2021-11-01 07:00:17  Average Active Sessions           .23
2021-11-01 08:00:17  Average Active Sessions           .51
2021-11-01 09:00:18  Average Active Sessions          1.24
2021-11-01 09:59:18  Average Active Sessions          1.93
2021-11-01 11:00:19  Average Active Sessions          1.98
2021-11-01 12:00:19  Average Active Sessions          1.58
2021-11-01 13:00:20  Average Active Sessions           .91
2021-11-01 14:00:20  Average Active Sessions           .66
2021-11-01 14:59:21  Average Active Sessions          1.19
2021-11-01 16:00:22  Average Active Sessions          1.45
2021-11-01 17:00:22  Average Active Sessions          1.26
2021-11-01 18:00:23  Average Active Sessions           .69
2021-11-01 18:59:24  Average Active Sessions           .57
2021-11-01 20:00:24  Average Active Sessions           .92
2021-11-01 21:00:24  Average Active Sessions           .70
2021-11-01 22:00:24  Average Active Sessions           .58
2021-11-01 22:59:26  Average Active Sessions           .83
2021-11-01 23:59:26  Average Active Sessions           .56
2021-11-02 00:59:27  Average Active Sessions           .28
2021-11-02 02:00:27  Average Active Sessions           .23
2021-11-02 03:00:27  Average Active Sessions           .20
2021-11-02 04:00:28  Average Active Sessions           .53
2021-11-02 04:59:29  Average Active Sessions           .22
2021-11-02 05:59:29  Average Active Sessions           .20
2021-11-02 06:59:30  Average Active Sessions           .22
2021-11-02 08:00:30  Average Active Sessions           .30
2021-11-02 09:00:31  Average Active Sessions           .68
2021-11-02 10:00:31  Average Active Sessions           .83
2021-11-02 10:59:32  Average Active Sessions           .96
2021-11-02 11:59:33  Average Active Sessions           .84
2021-11-02 13:00:33  Average Active Sessions           .53
2021-11-02 14:00:34  Average Active Sessions           .54
2021-11-02 14:59:35  Average Active Sessions           .66
2021-11-02 15:59:34  Average Active Sessions          1.16
2021-11-02 17:00:35  Average Active Sessions           .91
2021-11-02 18:00:36  Average Active Sessions          1.05
2021-11-02 18:59:36  Average Active Sessions           .57
2021-11-02 19:59:37  Average Active Sessions           .53
2021-11-02 20:59:38  Average Active Sessions           .66
2021-11-02 22:00:38  Average Active Sessions           .83
2021-11-02 23:00:39  Average Active Sessions          1.28
2021-11-02 23:59:39  Average Active Sessions           .31
2021-11-03 00:59:40  Average Active Sessions           .25
2021-11-03 01:59:40  Average Active Sessions           .23
2021-11-03 02:59:41  Average Active Sessions           .21
2021-11-03 04:00:41  Average Active Sessions           .68
2021-11-03 05:00:42  Average Active Sessions           .21
2021-11-03 05:59:42  Average Active Sessions           .20
2021-11-03 06:59:43  Average Active Sessions           .22
2021-11-03 07:59:43  Average Active Sessions           .29
2021-11-03 08:59:44  Average Active Sessions           .68
2021-11-03 10:00:45  Average Active Sessions           .98
2021-11-03 10:59:46  Average Active Sessions          1.62
2021-11-03 11:59:46  Average Active Sessions          1.00
2021-11-03 12:59:46  Average Active Sessions           .93
2021-11-03 13:59:47  Average Active Sessions           .55
2021-11-03 14:59:47  Average Active Sessions           .74
2021-11-03 15:59:48  Average Active Sessions          1.05
2021-11-03 16:59:48  Average Active Sessions           .95
2021-11-03 17:59:49  Average Active Sessions           .96
2021-11-03 19:00:50  Average Active Sessions           .74
2021-11-03 19:59:50  Average Active Sessions           .51
2021-11-03 20:59:51  Average Active Sessions           .56
2021-11-03 21:59:52  Average Active Sessions           .59
2021-11-03 22:59:52  Average Active Sessions           .50
2021-11-04 00:00:53  Average Active Sessions           .37
2021-11-04 00:59:53  Average Active Sessions           .28
2021-11-04 01:59:53  Average Active Sessions           .23
2021-11-04 02:59:54  Average Active Sessions           .21
2021-11-04 03:59:54  Average Active Sessions           .45
2021-11-04 04:59:55  Average Active Sessions           .24
2021-11-04 05:59:55  Average Active Sessions           .21
2021-11-04 06:59:56  Average Active Sessions           .25
2021-11-04 07:59:56  Average Active Sessions           .30
2021-11-04 08:59:57  Average Active Sessions           .71
2021-11-04 09:59:58  Average Active Sessions          1.47
2021-11-04 10:59:59  Average Active Sessions          1.28
2021-11-04 11:59:59  Average Active Sessions          1.15
2021-11-04 13:00:00  Average Active Sessions           .71
2021-11-04 14:00:00  Average Active Sessions           .75
2021-11-04 15:00:01  Average Active Sessions          1.14
2021-11-04 16:00:01  Average Active Sessions          1.78
2021-11-04 17:00:02  Average Active Sessions          1.44
2021-11-04 18:00:03  Average Active Sessions          1.24
2021-11-04 19:00:03  Average Active Sessions           .66
2021-11-04 20:00:03  Average Active Sessions           .66
2021-11-04 21:00:03  Average Active Sessions          1.12
2021-11-04 22:00:05  Average Active Sessions           .82
2021-11-04 23:00:05  Average Active Sessions          1.46
2021-11-05 00:00:06  Average Active Sessions           .59
2021-11-05 01:00:06  Average Active Sessions           .36
2021-11-05 02:00:06  Average Active Sessions           .27
2021-11-05 03:00:07  Average Active Sessions           .25
2021-11-05 04:00:07  Average Active Sessions           .59
2021-11-05 05:00:08  Average Active Sessions           .36
2021-11-05 06:00:09  Average Active Sessions           .28
2021-11-05 07:00:10  Average Active Sessions           .34
2021-11-05 08:00:10  Average Active Sessions           .61
2021-11-05 09:00:11  Average Active Sessions           .99
2021-11-05 10:00:11  Average Active Sessions          1.22
2021-11-05 11:00:11  Average Active Sessions          1.12
2021-11-05 12:00:12  Average Active Sessions           .99
2021-11-05 12:59:13  Average Active Sessions          1.48
2021-11-05 14:00:13  Average Active Sessions          1.00
2021-11-05 15:00:13  Average Active Sessions          1.55
2021-11-05 16:00:14  Average Active Sessions          1.76

720 rows selected.

然后，将这个XML文件导入您的SQL Developer。最终，生成的图表为：

[返回维度目录列表]

最近31天的平均活动会话（按每天间隔）

SQL查询语句如下：

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

SET LINESIZE 200
SET PAGESIZE 200

COLUMN metric_name FORMAT a25
COLUMN snap_date   FORMAT a12
COLUMN aas         FORMAT 999,999.99

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

WITH aas_per_hour AS (
SELECT 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
)
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
ORDER BY snap_date
;

查询结果为：

SNAP_DATE    METRIC_NAME                       AAS
------------ ------------------------- -----------
2021-10-06   Average Active Sessions           .49
2021-10-07   Average Active Sessions           .59
2021-10-08   Average Active Sessions          1.08
2021-10-09   Average Active Sessions          1.35
2021-10-10   Average Active Sessions          1.12
2021-10-11   Average Active Sessions          1.97
2021-10-12   Average Active Sessions          1.92
2021-10-13   Average Active Sessions          1.99
2021-10-14   Average Active Sessions          1.89
2021-10-15   Average Active Sessions          2.44
2021-10-16   Average Active Sessions          1.53
2021-10-17   Average Active Sessions          1.58
2021-10-18   Average Active Sessions          2.77
2021-10-19   Average Active Sessions          2.83
2021-10-20   Average Active Sessions          3.05
2021-10-21   Average Active Sessions          2.05
2021-10-22   Average Active Sessions          1.99
2021-10-23   Average Active Sessions          1.41
2021-10-24   Average Active Sessions          1.57
2021-10-25   Average Active Sessions          2.41
2021-10-26   Average Active Sessions          1.68
2021-10-27   Average Active Sessions          1.30
2021-10-28   Average Active Sessions          1.51
2021-10-29   Average Active Sessions          1.27
2021-10-30   Average Active Sessions           .90
2021-10-31   Average Active Sessions           .62
2021-11-01   Average Active Sessions           .80
2021-11-02   Average Active Sessions           .61
2021-11-03   Average Active Sessions           .64
2021-11-04   Average Active Sessions           .79
2021-11-05   Average Active Sessions           .81

31 rows selected.

然后，将这个XML文件导入SQL Developer。最终的图表是这样的：

[返回维度目录列表]

自定义时间段的平均活动会话（按每小时间隔）

SQL查询语句如下：

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

SET LINESIZE 200
SET PAGESIZE 200

COLUMN metric_name    FORMAT a25
COLUMN snap_date_time FORMAT a20
COLUMN aas            FORMAT 999,999.99

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

SELECT TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi:ss') snap_date_time  -- the group column
     , metric_name                                                -- the series column
     , ROUND(average, 2) aas                                      -- the value column
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')
      )
ORDER BY snap_date_time
;

由于上面“自定义时间段”的SQL语句中使用了绑定变量，所以在SQL*Plus中执行会报错，这里不再显示查询结果。同时将这个XML文件导入SQL Developer。这里切记，在运行图表时，首先需要输入两个绑定变量“开始时间”和“结束时间”的值，然后才能生成相应的图表，两个步骤分别如下所示：


[返回维度目录列表]

自定义时间段的平均活动会话（按每天间隔）

SQL查询语句如下：

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

SET LINESIZE 200
SET PAGESIZE 200

COLUMN metric_name FORMAT a25
COLUMN snap_date   FORMAT a12
COLUMN aas         FORMAT 999,999.99

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

WITH aas_per_hour AS (
SELECT 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')
      )
)
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
ORDER BY snap_date
;

同理，不再显示上面SQL语句的查询结果。然后将这个XML文件导入SQL Developer。和前面的图表一样，两个操作步骤分别为：



[返回维度目录列表]

可视化Oracle性能图表之“平均活动会话”篇，今天就分享到这里结束了。下一篇我将在“平均活动会话”图表的基础上再增加一个“逻辑CPU数目”，相信那样的图表显示效果会更加形象和美观更容易观察到目前数据库是否存在性能瓶颈，小伙伴们不要走开哟，敬请期待！！！

参考文章：

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

  • 因为我的数据库中AWR报告的保留时间是一个月31天，如果您的数据库的AWR报告没有那么长的保留时间，两个维度中的“最近31天的平均活动会话(...)/(...)”会和我的图表有所差距；
  • 将最后段落中的“美观”二字划上“删除线”标记，并增加内容“更容易观察到目前数据库是否存在性能瓶颈”；
  • 给八个维度的无序列表增加HTML的锚点功能，单击每一个无序列表项都能自动跳转到文章相应的段落标题处；
  • 增加“参考文章”列表；
墨力计划oracle平均活动会话
最后修改时间：2021-11-06 16:29:34
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
3人已赞赏
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论