ITPub博客

首页 > 数据库 > Oracle > [20201228]无聊的监测软件.txt

[20201228]无聊的监测软件.txt

原创 Oracle 作者:lfree 时间:2020-12-28 10:42:05 0 删除 编辑

[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实例有问题的语句完成漏掉了。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2745795/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2823
  • 访问量
    6620804