Oracle AWR中 “Logons” 和 “User logons” 区别

在查看AWR时LOGONS每秒实际比listener log中的每秒创建连接高出很多倍，同时AWR中还有另一个指标user logons比较接近，”Logons” 和 “User logons” 是有区别的，用于诊断连接风暴是应该使用users logons.

Load Profile

可以看到logons/s 2214, 而user logons/s 71, 统计信息来自2个不同的指标,如测试机显示:

SQL> select H.SNAP_ID,
TO_CHAR(S.BEGIN_INTERVAL_TIME,'MM/DD/YYYY hh24:mi:ss') as BEGIN_INTERVAL_TIME,
TO_CHAR(S.END_INTERVAL_TIME, 'MM/DD/YYYY hh24:mi:ss') as END_INTERVAL_TIME,
H.VALUE
FROM DBA_HIST_SYSSTAT H JOIN DBA_HIST_SNAPSHOT S
ON H.SNAP_ID=S.SNAP_ID
AND H.DBID=S.DBID
AND H.INSTANCE_NUMBER=S.INSTANCE_NUMBER
  9  WHERE STAT_NAME='user logons cumulative';

   SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME        VALUE
---------- ------------------- ------------------- ----------
      1042 08/15/2023 00:00:14 08/15/2023 00:10:16          5
      1043 08/15/2023 00:10:16 08/15/2023 01:00:29          5
      1044 08/15/2023 01:00:29 08/15/2023 02:00:43          5
      1045 08/15/2023 02:00:43 08/15/2023 03:00:58          5


select H.SNAP_ID,
TO_CHAR(S.BEGIN_INTERVAL_TIME,'MM/DD/YYYY hh24:mi:ss') as BEGIN_INTERVAL_TIME,
TO_CHAR(S.END_INTERVAL_TIME, 'MM/DD/YYYY hh24:mi:ss') as END_INTERVAL_TIME,
H.VALUE
FROM DBA_HIST_SYSSTAT H JOIN DBA_HIST_SNAPSHOT S
ON H.SNAP_ID=S.SNAP_ID
AND H.DBID=S.DBID
AND H.INSTANCE_NUMBER=S.INSTANCE_NUMBER
  9  WHERE STAT_NAME='logons cumulative';

   SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME        VALUE
---------- ------------------- ------------------- ----------
      1042 08/15/2023 00:00:14 08/15/2023 00:10:16        130
      1043 08/15/2023 00:10:16 08/15/2023 01:00:29        183
      1044 08/15/2023 01:00:29 08/15/2023 02:00:43        241
      1045 08/15/2023 02:00:43 08/15/2023 03:00:58        295

SQL> @sys logons

NAME                                                                                  VALUE
---------------------------------------------------------------- --------------------------
logons cumulative                                                                        94
logons current                                                                           50
user logons cumulative                                                                    2

SQL> select /*+parallel (t 16)*/ count(*) from obj$ t;

  COUNT(*)
----------
     72779

SQL> @sys logons

NAME                                                                                  VALUE
---------------------------------------------------------------- --------------------------
logons cumulative                                                                       110
logons current                                                                           50
user logons cumulative                                                                    2

-- 如果此时再次使用sqlplus 登录一个连接 sqlplus / as sysdba

SQL> @sys logons

NAME                                                                                  VALUE
---------------------------------------------------------------- --------------------------
logons cumulative                                                                       111
logons current                                                                           51
user logons cumulative                                                                    3

SQL> l
  1* select name, value sys_value from v$sysstat where lower(name) like lower('%&1%')
 

目前我们“logons cumulative”，其中包括非用户调用，如parallel query secondary calls, Job queue processes calls

AWR logons is the figure of ‘logons cumulative’. The statistic ‘logons cumulative’ is incremented every time a process starts. So, in the case of PX, then it will increment each time a new worker begins. Please use ‘user logons cummulative’ to track the application/end user logon.

AWR中11g后增加了logout指标增强，可以判断是否有短链接频繁，使用 AWR 中的新统计信息来识别用户环境中的logon/logout风暴。

Instance Activity Stats

• Ordered by statistic name

Convert to CSV