[20211111]我看華為監控程式.txt
[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報表的介面來的更實際一些。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2841763/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- nagios監控華為5700交換機iOS
- 我們如何做SQL監控？SQL
- 動態監控程式
- 程式實時監控
- Docker視覺化監控?看這篇文章Docker視覺化
- 華為AI監控好望攝像機怎樣？AI
- Java 監控基礎 - 使用 JMX 監控和管理 Java 程式Java
- 【AIX】AIX程式監控工具AI
- Java程式監控指標Java指標
- 監控目錄、主機、程式程式
- [20211111]奇怪的ashtop輸出.txt
- 我常用的主機監控Shell指令碼指令碼
- 小程式異常監控收集
- Elastic AMP監控.NET程式效能AST
- 小程式日誌監控工具
- memcached程式埠監控指令碼指令碼
- Python監控日誌程式Python
- 黑盒監控、日誌監控
- Process Monitor監控目錄 - 監控檔案被哪個程式操作了
- 6.prometheus監控--監控dockerPrometheusDocker
- 我看程式設計師 (轉)程式設計師
- Linux程式管理與效能監控Linux
- JS實現監控微信小程式JS微信小程式
- 微信小程式函式呼叫監控微信小程式函式
- 華為前員工：我在華為敲程式碼知道的事
- 測試已死？我看未必！分享我在華為做敏捷測試的那些流程……敏捷測試
- 來看華為總監的使用者行為分析三要素
- TiDB監控實現--存活監控TiDB
- 聊聊前端監控——錯誤監控篇前端
- nagios監控例項 -- PostgreSQL監控iOSSQL
- 我經歷過的監控系統演進史
- 我從 4 年網頁監控中所學到的網頁
- APM效能監控軟體的監控型別服務及監控流程型別
- 【SQL監控】SQL完全監控的指令碼SQL指令碼
- 伺服器監控之 ping 監控伺服器
- 3-主機監控、應用監控
- linux安裝Arthas監控java程式LinuxJava
- Container及其內部程式監控剖析AI