[20201228]無聊的監測軟體.txt

--//最近在最佳化一個專案，等我最佳化完成後，發現排在前面的基本是監測軟體發出的命令。

1.環境：
SYS@192.168.99.105:1521/bills> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.使用ashtop檢視：
SYS@192.168.99.105:1521/bills> @ ashtop sql_id,machine 1=1 trunc(sysdate-1) trunc(sysdate)
    Total
Seconds     AAS %This   SQL_ID        MACHINE     FIRST_SEEN          LAST_SEEN
--------- ------- ------- ------------- ----------- ------------------- -------------------
    11366      .1   76% |               dm01dbadm02 2020-12-27 00:00:01 2020-12-27 23:59:59
     1540      .0   10% |               dm01dbadm01 2020-12-27 00:00:01 2020-12-27 23:59:44
      829      .0    6% | 4zbzjuu5h34dn IMC         2020-12-27 00:02:25 2020-12-27 23:57:41
      223      .0    1% |               xxxdzpj     2020-12-27 00:01:31 2020-12-27 23:55:52
       94      .0    1% | 2w5dgfjvasy4j IMC         2020-12-27 00:47:29 2020-12-27 23:47:38
       66      .0    0% | c5vp872ytwr03 IMC         2020-12-27 00:07:38 2020-12-27 22:52:42
       63      .0    0% | 7y3xscmmqfymn IMC         2020-12-27 00:17:31 2020-12-27 23:52:42
       56      .0    0% | 3ddgu71paks5d IMC         2020-12-27 00:02:28 2020-12-27 22:32:41
       55      .0    0% | 9yfzqfdw2yhs4 IMC         2020-12-27 00:02:24 2020-12-27 23:42:38
       41      .0    0% | 8b4txypt6ttws IMC         2020-12-27 02:52:28 2020-12-27 23:52:38
       33      .0    0% | 3pd27sf83zkm1 xxxdzpj     2020-12-27 00:05:29 2020-12-27 22:45:58
       22      .0    0% | fz1w4jjrrdzs3 xxxdzpj     2020-12-27 04:04:32 2020-12-27 21:40:03
       21      .0    0% |               IMC         2020-12-27 03:07:39 2020-12-27 23:22:42
       20      .0    0% | 8g7tjhp1j0ky3 IMC         2020-12-27 00:12:33 2020-12-27 21:52:42
       18      .0    0% | gtb8cvtdq4fjd IMC         2020-12-27 01:32:33 2020-12-27 23:52:43
       14      .0    0% | 355mhatf4w6r1 xxxdzpj     2020-12-27 05:08:37 2020-12-27 16:09:55
       12      .0    0% | 89camvzd2vfu8 xxxdzpj     2020-12-27 01:09:31 2020-12-27 23:07:33
       12      .0    0% | ana2tbsjs9dxn xxxdzpj     2020-12-27 00:14:05 2020-12-27 21:15:27
       12      .0    0% | bunssq950snhf dm01dbadm01 2020-12-27 01:00:10 2020-12-27 21:00:15
       11      .0    0% | 0pav43j3wnx53 IMC         2020-12-27 01:37:29 2020-12-27 20:12:37
       11      .0    0% | cr988d50t86za IMC         2020-12-27 03:37:30 2020-12-27 22:22:37
       10      .0    0% | 1yq9r01hhfrs2 IMC         2020-12-27 00:12:25 2020-12-27 23:07:43
       10      .0    0% | 6c23qpas152z3 IMC         2020-12-27 00:07:33 2020-12-27 22:32:37
        9      .0    0% | 5948723a03538 xxxdzpj     2020-12-27 00:52:15 2020-12-27 23:34:11
        9      .0    0% | bunssq950snhf dm01dbadm02 2020-12-27 03:00:22 2020-12-27 16:00:30
        9      .0    0% | c6xvvzvqdyy0n IMC         2020-12-27 02:02:33 2020-12-27 23:37:42
        8      .0    0% | 676hkc25z79uw xxxdzpj     2020-12-27 00:52:32 2020-12-27 22:43:33
        8      .0    0% | 9nv8wjbpjzqn9 IMC         2020-12-27 01:37:34 2020-12-27 22:27:42
        8      .0    0% | a3pa94nrutww1 xxxdzpj     2020-12-27 01:13:32 2020-12-27 01:13:58
        8      .0    0% | ft7z47dw39y8t xxxdzpj     2020-12-27 00:13:38 2020-12-27 19:45:33
30 rows selected.

--//注意看FIRST_SEEN,LAST_SEEN列，說明查詢一天沒有問題，雖然是星期天。你可以發現都是所謂MACHINE='IMC'機器出現的sql語句多。
--//隨便看幾個sqlid：
SYS@192.168.99.105:1521/bills> @ sqlid 4zbzjuu5h34dn
SQL_ID        SQLTEXT
------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4zbzjuu5h34dn select a.spaceName, a.status, NVL (b.total_bytes,0) total_bytes, a.free_bytes, a.free_blocks, b.phyrds, b.phywrts, b.readtim, b.writetim, a.fsfi from (SELECT t.tablespace_name spaceName,t.contents,t.s
              tatus status,NVL (f.free_bytes,0) free_bytes, NVL (f.free_blocks,0) free_blocks, fsfi FROM sys.dba_tablespaces t, (SELECT tablespace_name, SUM(bytes) free_bytes, SUM(blocks) free_blocks, sqrt(max(bloc
              ks)/sum(blocks))*(100/SQRT(SQRT(COUNT(BLOCKS)))) fsfi FROM sys.dba_free_space GROUP BY tablespace_name) f WHERE t.tablespace_name = f.tablespace_name(+) ORDER BY t.tablespace_name) a left outer join (
              SELECT d.tablespace_name spaceName, SUM(d.bytes) total_bytes, SUM(f.phyrds) phyrds, SUM(f.phywrts) phywrts, SUM(f.readtim) readtim, SUM(f.writetim) writetim FROM sys.dba_data_files d, V$filestat f WHE
              RE d.file_id = f.file# GROUP BY d.tablespace_name) b on a.SPACENAME = b.SPACENAME

SYS@192.168.99.105:1521/bills> @ sqlid 2w5dgfjvasy4j
SQL_ID        SQLTEXT
------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2w5dgfjvasy4j SELECT 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.b
              lks_changed, stat.chained_rows, stat.commits, stat.cursors, round((1-(stat.phyreads/DECODE(stat.logreads,0,NULL,stat.logreads)))*100) buffer_cache_hitrate from (SELECT st.sid, SUM(DECODE(name, 'CPU us
              ed 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

> @ sqlid c5vp872ytwr03 ''
SQL_ID        SQLTEXT
------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
c5vp872ytwr03 select sql_text, elapsedtime, cputime, executions, disk_reads, buffer_gets from (select sql_text, trunc(elapsed_time/1000000) as elapsedtime, trunc(cpu_time/1000000) as cputime, executions, disk_reads
              , buffer_gets from v$sqlarea order by elapsed_time desc) where rownum<=10

--//還不如說它寫錯了，我管的資料庫可是rac。

> @ ashtop sql_id,machine,inst_id machine='IMC' trunc(sysdate-1) trunc(sysdate)
    Total
Seconds     AAS %This   SQL_ID        MACHINE INST_ID FIRST_SEEN          LAST_SEEN
--------- ------- ------- ------------- ------- ------- ------------------- -------------------
      829      .0   60% | 4zbzjuu5h34dn IMC           1 2020-12-27 00:02:25 2020-12-27 23:57:41
       94      .0    7% | 2w5dgfjvasy4j IMC           1 2020-12-27 00:47:29 2020-12-27 23:47:38
       66      .0    5% | c5vp872ytwr03 IMC           1 2020-12-27 00:07:38 2020-12-27 22:52:42
       63      .0    5% | 7y3xscmmqfymn IMC           1 2020-12-27 00:17:31 2020-12-27 23:52:42
       56      .0    4% | 3ddgu71paks5d IMC           1 2020-12-27 00:02:28 2020-12-27 22:32:41
       55      .0    4% | 9yfzqfdw2yhs4 IMC           1 2020-12-27 00:02:24 2020-12-27 23:42:38
       41      .0    3% | 8b4txypt6ttws IMC           1 2020-12-27 02:52:28 2020-12-27 23:52:38
       20      .0    1% | 8g7tjhp1j0ky3 IMC           1 2020-12-27 00:12:33 2020-12-27 21:52:42
       20      .0    1% |               IMC           1 2020-12-27 03:07:39 2020-12-27 23:22:42
       18      .0    1% | gtb8cvtdq4fjd IMC           1 2020-12-27 01:32:33 2020-12-27 23:52:43
       11      .0    1% | 0pav43j3wnx53 IMC           1 2020-12-27 01:37:29 2020-12-27 20:12:37
       11      .0    1% | cr988d50t86za IMC           1 2020-12-27 03:37:30 2020-12-27 22:22:37
       10      .0    1% | 1yq9r01hhfrs2 IMC           1 2020-12-27 00:12:25 2020-12-27 23:07:43
       10      .0    1% | 6c23qpas152z3 IMC           1 2020-12-27 00:07:33 2020-12-27 22:32:37
        9      .0    1% | c6xvvzvqdyy0n IMC           1 2020-12-27 02:02:33 2020-12-27 23:37:42
        8      .0    1% | 9nv8wjbpjzqn9 IMC           1 2020-12-27 01:37:34 2020-12-27 22:27:42
        6      .0    0% | 0ws7ahf1d78qa IMC           1 2020-12-27 06:17:37 2020-12-27 22:27:41
        3      .0    0% | 459f3z9u4fb3u IMC           1 2020-12-27 10:27:38 2020-12-27 20:42:40
        3      .0    0% | 4raxd2zd98ju0 IMC           1 2020-12-27 07:02:36 2020-12-27 10:07:51
        3      .0    0% | 5ccpu0yhnyyd1 IMC           1 2020-12-27 05:32:35 2020-12-27 19:47:41
        3      .0    0% | 8q2qq3a76hqft IMC           1 2020-12-27 01:47:33 2020-12-27 13:02:40
        2      .0    0% | 02hnhz4sz6k0s IMC           1 2020-12-27 10:07:37 2020-12-27 13:12:40
        2      .0    0% | 0sq185a51hayv IMC           1 2020-12-27 17:32:41 2020-12-27 21:57:42
        2      .0    0% | 2y0m5yyt0hww5 IMC           1 2020-12-27 18:52:42 2020-12-27 22:37:42
        2      .0    0% | 4a512wmw7mywn IMC           1 2020-12-27 04:37:35 2020-12-27 15:12:49
        2      .0    0% | 5jpwu73jqyujj IMC           1 2020-12-27 18:17:37 2020-12-27 18:42:37
        2      .0    0% | 8c83qyvk0pskw IMC           1 2020-12-27 01:52:33 2020-12-27 13:07:40
        2      .0    0% | 8xvdyjuv7dnxs IMC           1 2020-12-27 10:02:37 2020-12-27 12:47:40
        2      .0    0% | f77dttu4n2cpw IMC           1 2020-12-27 00:12:29 2020-12-27 22:52:41
        2      .0    0% | fv931pkqm98n6 IMC           1 2020-12-27 12:57:39 2020-12-27 21:07:51
30 rows selected.
--//^_^，我的業務主要在第2個例項跑，這樣的監測軟體無用的嗎?不就是騙錢的東西嗎？我真心不知道我同事寫的巡檢記錄有用嗎？
--//把有問題的第2例項有問題的語句完成漏掉了。

[20201228]無聊的監測軟體.txt

相關文章