首页 » ORACLE 9i-23ai » Oracle AWR中 “Logons” 和 “User logons” 区别

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

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

Load Profile

Per Second Per Transaction Per Exec Per Call
DB Time(s): 67.8 0.1 0.00 0.00
DB CPU(s): 40.7 0.1 0.00 0.00
Background CPU(s): 1.4 0.0 0.00 0.00
Redo size (bytes): 1,903,005.2 3,377.1
Logical read (blocks): 3,280,769.8 5,822.2
Block changes: 10,279.4 18.2
Physical read (blocks): 13,819.3 24.5
Physical write (blocks): 3,589.0 6.4
Read IO requests: 5,913.0 10.5
Write IO requests: 301.7 0.5
Read IO (MB): 108.0 0.2
Write IO (MB): 28.0 0.1
IM scan rows: 0.0 0.0
Session Logical Read IM: 0.0 0.0
Global Cache blocks received: 1,336.2 2.4
Global Cache blocks served: 885.9 1.6
User calls: 100,849.5 179.0
Parses (SQL): 24,562.8 43.6
Hard parses (SQL): 15.1 0.0
SQL Work Area (MB): 202.3 0.4
Logons: 2,214.1 3.9
User logons: 71.4 0.1
Executes (SQL): 32,058.1 56.9
Rollbacks: 25.1 0.0
Transactions: 563.5

可以看到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

Statistic Total per Second per Trans
user logons cumulative 130,846 71.40 0.13
user logouts cumulative 130,894 71.43 0.13
打赏

,

对不起,这篇文章暂时关闭评论。