[20211111]我看华为监控程序.txt

--//下午没有什么事情，我想看看我们生产系统华为监控程序到底做一些什么。

1.环境：
SYS@XXXXX1/ORCLX> @ prxx
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 19.0.0.0.0
BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0
BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

SYS@XXXXX1/ORCLX> @ ashtop event "machine='H3C-APM'" trunc(sysdate)-1 trunc(sysdate)
    Total
  Seconds     AAS %This   EVENT                                    FIRST_SEEN          LAST_SEEN
--------- ------- ------- ---------------------------------------- ------------------- -------------------
    14566      .2   99% |                                          2021-11-10 00:03:24 2021-11-10 23:59:31
       80      .0    1% | control file sequential read             2021-11-10 00:34:09 2021-11-10 23:44:19
       30      .0    0% | Disk file Mirror Read                    2021-11-10 00:58:31 2021-11-10 23:29:26
       14      .0    0% | ASM file metadata operation              2021-11-10 00:39:09 2021-11-10 19:54:07
       10      .0    0% | oracle thread bootstrap                  2021-11-10 03:38:49 2021-11-10 23:38:47
        9      .0    0% | KSV master wait                          2021-11-10 00:28:49 2021-11-10 23:34:29
        6      .0    0% | PGA memory operation                     2021-11-10 03:53:29 2021-11-10 20:38:36
        5      .0    0% | Sync ASM rebalance                       2021-11-10 03:34:09 2021-11-10 20:23:28
        4      .0    0% | PX Deq: Join ACK                         2021-11-10 00:43:41 2021-11-10 05:28:51
        4      .0    0% | db file sequential read                  2021-11-10 03:33:47 2021-11-10 14:54:08
        3      .0    0% | PX Deq: Slave Session Stats              2021-11-10 01:24:09 2021-11-10 06:14:30
        3      .0    0% | PX Deq: reap credit                      2021-11-10 01:59:32 2021-11-10 18:29:13
        3      .0    0% | reliable message                         2021-11-10 00:29:30 2021-11-10 16:09:07
        1      .0    0% | CSS initialization                       2021-11-10 20:54:20 2021-11-10 20:54:20
        1      .0    0% | CSS operation: action                    2021-11-10 19:54:20 2021-11-10 19:54:20
        1      .0    0% | SQL*Net break/reset to client            2021-11-10 05:54:05 2021-11-10 05:54:05
        1      .0    0% | enq: PS - contention                     2021-11-10 19:14:21 2021-11-10 19:14:21
        1      .0    0% | gc cr block 2-way                        2021-11-10 13:03:46 2021-11-10 13:03:46
        1      .0    0% | gc cr multi block request                2021-11-10 18:14:15 2021-11-10 18:14:15
        1      .0    0% | gc current grant busy                    2021-11-10 12:24:06 2021-11-10 12:24:06
20 rows selected.
--//查看1天时间14566/3600 = 4.05小时，一天时间消耗CPU 4个小时.对于许多服务器这点消耗不算什么。

SYS@XXXXX1/ORCLX> @ ashtop sql_id "machine='H3C-APM'" trunc(sysdate)-1 trunc(sysdate)
    Total
  Seconds     AAS %This   SQL_ID        FIRST_SEEN          LAST_SEEN
--------- ------- ------- ------------- ------------------- -------------------
     2022      .0   14% | c3jafyjuwt13b 2021-11-10 00:03:25 2021-11-10 23:59:06
      685      .0    5% | f454ryjfx6syf 2021-11-10 00:03:50 2021-11-10 23:59:31
      527      .0    4% | 8fm0xfacp0b0g 2021-11-10 00:03:34 2021-11-10 23:59:14
      508      .0    3% | f6d0fpgm1w2sw 2021-11-10 00:03:38 2021-11-10 23:58:36
      500      .0    3% | 1g11ms1r6bnuj 2021-11-10 00:03:31 2021-11-10 23:59:10
      494      .0    3% | 9w8scutvwbjaw 2021-11-10 00:03:43 2021-11-10 23:59:23
      492      .0    3% | 7m6szm4t720j0 2021-11-10 00:03:39 2021-11-10 23:58:37
      492      .0    3% | 8ss7js42xzp05 2021-11-10 00:03:37 2021-11-10 23:58:35
      492      .0    3% | d946h5sr5gt69 2021-11-10 00:03:42 2021-11-10 23:59:22
      490      .0    3% | 0tha0zcyf9maq 2021-11-10 00:03:35 2021-11-10 23:58:33
      489      .0    3% | 30a5bma58q1w7 2021-11-10 00:03:49 2021-11-10 23:59:29
      487      .0    3% | 18q3m92yk5zg5 2021-11-10 00:03:48 2021-11-10 23:59:28
      485      .0    3% | czkw1ncpthxy4 2021-11-10 00:04:12 2021-11-10 23:59:09
      484      .0    3% | 6sbq34x7ckff7 2021-11-10 00:04:14 2021-11-10 23:59:11
      482      .0    3% | 5mwanf0c830mj 2021-11-10 00:03:41 2021-11-10 23:59:21
      480      .0    3% | ck5qb9zs2n34g 2021-11-10 00:03:44 2021-11-10 23:59:24
      479      .0    3% | 0zrwxj39q7u3w 2021-11-10 00:03:47 2021-11-10 23:59:27
      479      .0    3% | fpamfm2pkznu1 2021-11-10 00:03:45 2021-11-10 23:59:25
      478      .0    3% | 2v09t9vyy6zk6 2021-11-10 00:04:27 2021-11-10 23:59:26
      476      .0    3% | az4ju0qgum193 2021-11-10 00:04:17 2021-11-10 23:59:15
      475      .0    3% | b282h3vx1nh1j 2021-11-10 00:03:36 2021-11-10 23:54:15
      474      .0    3% | 2sq2bmkwuz6at 2021-11-10 00:03:40 2021-11-10 23:59:20
      472      .0    3% | 5ub6g7qwaf35x 2021-11-10 00:03:29 2021-11-10 23:59:08
      230      .0    2% | 5t9zzqmqdyxbg 2021-11-10 00:03:30 2021-11-10 23:58:28
      210      .0    1% | 9yfzqfdw2yhs4 2021-11-10 00:03:24 2021-11-10 23:59:03
      208      .0    1% | cyfdvynj0mtc8 2021-11-10 00:03:33 2021-11-10 23:59:12
      207      .0    1% | 27m1sf1nknfz2 2021-11-10 00:04:31 2021-11-10 23:44:30
       84      .0    1% | 1j262t18zrpxt 2021-11-10 00:14:32 2021-11-10 22:53:52
       75      .0    1% | ahatnp9sk1b5s 2021-11-10 00:03:46 2021-11-10 23:34:26
       48      .0    0% | czd2z83tzauux 2021-11-10 00:23:31 2021-11-10 23:58:30
       42      .0    0% | 68k7ckt95ttcf 2021-11-10 00:03:51 2021-11-10 23:34:31
       39      .0    0% | dq6bzb5xdk3h7 2021-11-10 00:24:05 2021-11-10 23:34:04
       37      .0    0% | 6uz4za48wf6j7 2021-11-10 00:53:39 2021-11-10 22:44:19
       36      .0    0% | 22356bkgsdcnh 2021-11-10 00:04:10 2021-11-10 23:39:08
       36      .0    0% | 7ksrtc8rzpawc 2021-11-10 00:14:30 2021-11-10 23:48:48
       31      .0    0% |               2021-11-10 01:24:09 2021-11-10 23:03:48
       30      .0    0% | 93jgxvdzsx4y1 2021-11-10 01:08:28 2021-11-10 23:59:07
       25      .0    0% | gd1gbr2ypfxu5 2021-11-10 00:28:49 2021-11-10 23:34:29
       24      .0    0% | 99v4t515j5j56 2021-11-10 02:23:51 2021-11-10 23:48:50
       22      .0    0% | 21t4z1r0k4cyd 2021-11-10 00:38:23 2021-11-10 23:44:03
       22      .0    0% | 9h4w5m54fq46f 2021-11-10 01:44:29 2021-11-10 23:24:27
       22      .0    0% | d78ubma8q6xj2 2021-11-10 04:14:30 2021-11-10 23:23:47
       21      .0    0% | 0gb620m9hwp4s 2021-11-10 00:39:23 2021-11-10 22:09:22
       21      .0    0% | d0h6nu0uwfjtx 2021-11-10 01:13:36 2021-11-10 22:29:17
       19      .0    0% | 3mubmnaquyqcj 2021-11-10 00:54:31 2021-11-10 22:28:49
       19      .0    0% | 5r14h528vkacs 2021-11-10 00:34:09 2021-11-10 22:13:27
       19      .0    0% | gzg2phr7fjs2j 2021-11-10 00:39:09 2021-11-10 20:23:28
       16      .0    0% | 1yq9r01hhfrs2 2021-11-10 01:38:29 2021-11-10 22:44:08
       16      .0    0% | c2ypbq9ac2qw5 2021-11-10 01:13:37 2021-11-10 23:59:18
       15      .0    0% | 8vmu6k690g87k 2021-11-10 01:54:06 2021-11-10 23:03:23
50 rows selected.

--//看看c3jafyjuwt13b的语句的具体内容：
SELECT sess.serial# serial
      ,sess.audsid
      ,sess.osuser
      ,sess.terminal
      ,sess.process
      ,sess.logon_time
      ,sess.command
      ,stat.sid
      ,sess.status
      ,sess.machine
      ,sess.username
      , (SYSDATE - sess.logon_time) * 86400 elapsed_time
      ,stat.cpu
      ,stat.memsorts
      ,stat.tablescans
      ,stat.phyreads
      ,stat.logreads
      ,stat.disksorts
      ,stat.blks_changed
      ,stat.chained_rows
      ,stat.commits
      ,stat.cursors
      ,ROUND
       (
            (  1
             - (  stat.phyreads
                / (  DECODE (stat.logreads, 0, NULL, stat.logreads)
                   + stat.phyreads)))
          * 100
       )
          buffer_cache_hitrate
  FROM (  SELECT st.sid
                ,SUM (DECODE (name, 'CPU used by this session', VALUE, 0)) cpu
                ,SUM (DECODE (name, 'sorts (disk)', VALUE, 0)) disksorts
                ,SUM (DECODE (name, 'sorts (memory)', VALUE, 0)) memsorts
                ,SUM (DECODE (SUBSTR (name, 0, 11), 'table scans', VALUE, 0))
                    tablescans
                ,SUM (DECODE (name, 'physical reads', VALUE, 0)) phyreads
                ,SUM (DECODE (name, 'session logical reads', VALUE, 0))
                    logreads
                ,SUM (DECODE (name, 'db block changes', VALUE, 0)) blks_changed
                ,SUM (DECODE (name, 'table fetch continued row', VALUE, 0))
                    chained_rows
                ,SUM (DECODE (name, 'user commits', VALUE, 0)) commits
                ,SUM (DECODE (name, 'opened cursors current', VALUE, 0))
                    cursors
            FROM V$SESSTAT st, V$STATNAME sn
           WHERE st.statistic# = sn.statistic#
        GROUP BY st.sid) stat
      ,v$session sess
 WHERE stat.sid = sess.sid
--//很明显这条语句的作用是实时收集用户的CPU,磁盘排序，表扫描，物理读，提交，打开光标等等数据信息。
--//在我看来一点用处都没有，如果一个用户执行完退出，你收集个毛，使用这东西就是忽悠人的破玩意，对于诊断一点意义都没有。
--//还不如设计一个按钮，让管理者许多时点击进入查看相关信息。
--//f454ryjfx6syf
SELECT EVENT
      ,WAITS
      ,TIME
      ,DECODE
       (
          WAITS
         ,NULL, TO_NUMBER (NULL)
         ,0, TO_NUMBER (NULL)
         ,TIME / WAITS * 1000
       )
          AVGWT
      ,PCTWTT
      ,WAIT_CLASS
  FROM (  SELECT EVENT
                ,WAITS
                ,TIME
                ,PCTWTT
                ,WAIT_CLASS
            FROM (SELECT A.EVENT_NAME EVENT
                        ,A.TOTAL_WAITS - NVL (A.TOTAL_WAITS_LAG, 0) WAITS
                        ,  (  A.TIME_WAITED_MICRO
                            - NVL (A.TIME_WAITED_MICRO_LAG, 0))
                         / 1000000
                            TIME
                        ,  100
                         * (  A.TIME_WAITED_MICRO
                            - NVL (A.TIME_WAITED_MICRO_LAG, 0))
                         / B.dbtime
                            PCTWTT
                        ,A.WAIT_CLASS WAIT_CLASS
                    FROM (SELECT SNAP_ID
                                ,DBID
                                ,INSTANCE_NUMBER
                                ,EVENT_NAME
                                ,TOTAL_WAITS
                                ,LAG (TOTAL_WAITS, 1, NULL)
                                 OVER (PARTITION BY EVENT_ID ORDER BY SNAP_ID)
                                    TOTAL_WAITS_LAG
                                ,TIME_WAITED_MICRO
                                ,LAG (TIME_WAITED_MICRO, 1, NULL)
                                 OVER (PARTITION BY EVENT_ID ORDER BY SNAP_ID)
                                    TIME_WAITED_MICRO_LAG
                                ,WAIT_CLASS
                            FROM DBA_HIST_SYSTEM_EVENT
                           WHERE     INSTANCE_NUMBER IN (SELECT instance_number
                                                           FROM v$INSTANCE)
                                 AND DBID IN (SELECT DBID
                                                FROM v$database)
                                 AND WAIT_CLASS != 'Idle') A
                        , (SELECT SNAP_ID
                                 ,DBID
                                 ,INSTANCE_NUMBER
                                 ,STAT_NAME
                                 ,dbtime
                             FROM (  SELECT SNAP_ID
                                           ,DBID
                                           ,INSTANCE_NUMBER
                                           ,STAT_NAME
                                           ,  VALUE
                                            - LAG
                                              (
                                                 VALUE
                                                ,1
                                                ,NULL
                                              )
                                              OVER
                                              (
                                                 PARTITION BY STAT_NAME
                                                 ORDER BY SNAP_ID
                                              )
                                               dbtime
                                       FROM DBA_HIST_SYS_TIME_MODEL
                                      WHERE     INSTANCE_NUMBER IN (SELECT instance_number
                                                                      FROM v$INSTANCE)
                                            AND DBID IN (SELECT DBID
                                                           FROM v$database)
                                            AND STAT_NAME = 'DB time'
                                   ORDER BY SNAP_ID DESC)
                            WHERE ROWNUM = 1) B
                   WHERE     A.SNAP_ID = B.SNAP_ID
                         AND A.DBID = B.DBID
                         AND A.INSTANCE_NUMBER = B.INSTANCE_NUMBER
                         AND A.TOTAL_WAITS > NVL (A.TOTAL_WAITS_LAG, 0)
                  UNION ALL
                  SELECT 'CPU time' EVENT
                        ,TO_NUMBER (NULL) WAITS
                        ,dbcpu / 1000000 TIME
                        ,100 * dbcpu / dbtime PCTWTT
                        ,NULL WAIT_CLASS
                    FROM (SELECT SUM
                                 (
                                    CASE STAT_NAME
                                       WHEN 'DB CPU' THEN VALUE
                                       ELSE NULL
                                    END
                                 )
                                    dbcpu
                                ,SUM
                                 (
                                    CASE STAT_NAME
                                       WHEN 'DB time' THEN VALUE
                                       ELSE NULL
                                    END
                                 )
                                    dbtime
                            FROM (SELECT SNAP_ID
                                        ,DBID
                                        ,INSTANCE_NUMBER
                                        ,stat_name
                                        ,  VALUE
                                         - LAG
                                           (
                                              VALUE
                                             ,1
                                             ,NULL
                                           )
                                           OVER
                                           (
                                              PARTITION BY STAT_NAME
                                              ORDER BY SNAP_ID
                                           )
                                            VALUE
                                        ,RANK ()
                                         OVER
                                         (
                                            PARTITION BY STAT_NAME
                                            ORDER BY SNAP_ID DESC
                                         )
                                            RANK
                                    FROM DBA_HIST_SYS_TIME_MODEL
                                   WHERE     INSTANCE_NUMBER IN (SELECT instance_number
                                                                   FROM v$INSTANCE)
                                         AND DBID IN (SELECT DBID
                                                        FROM v$database)
                                         AND STAT_NAME IN ('DB CPU', 'DB time'))
                           WHERE RANK = 1)
                   WHERE dbcpu > 0)
        ORDER BY TIME DESC, WAITS DESC)
 WHERE ROWNUM <= 10;

--//这个脚本用来收集最后1次awr的排名前10的等待事件。这个结果对于1个小时内生成awr报表的情况结果不会变的。
--//看看awr报表：
SQL ordered by Elapsed Time

    Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
    % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
    %Total - Elapsed Time as a percentage of Total DB time
    %CPU - CPU Time as a percentage of Elapsed Time
    %IO - User I/O Time as a percentage of Elapsed Time
    Captured SQL account for 36.3% of Total DB Time (s): 1,731
    Captured PL/SQL account for 0.0% of Total DB Time (s): 1,731

Elapsed Time (s) Executions Elapsed Time per Exec (s) %Total  %CPU   %IO   SQL Id              SQL Module          SQL Text
.....
41.81            12         3.48                      2.41    99.84  0.00  c3jafyjuwt13b       JDBC Thin Client    SELECT sess.serial# serial, se...
.....
14.07            12         1.17                      0.81    99.53  0.10  f454ryjfx6syf       JDBC Thin Client    SELECT EVENT, WAITS, TIME, DEC...
11.35            12         0.95                      0.66    99.61  0.07  8fm0xfacp0b0g       JDBC Thin Client    SELECT ROUND(( D.value * A.blo...
....

--//很明显5分钟做一次采集，问题在于你收集这些意义很在，对于诊断问题有多少帮助，就是一个无用垃圾的监控软件。
--//我把里面的执行sql语句看个遍，几乎对于诊断一点用处都没有，我给华为一个建议，你不如自己机器建立一个数据库，把这些
--//DBA_HIST_SYS_TIME_MODEL 之类的信息保存下来，然后自己读取这些表就ok了。
--//或者学习toad 软件里面做一个点点就能生成awr报表的界面来的更实际一些。