SQL*Net more data to client意味着什么

最新推荐文章于 2024-02-07 14:49:41 发布

老农民挖数据

最新推荐文章于 2024-02-07 14:49:41 发布

阅读量8.3k

点赞数

分类专栏： oracle_sql tuning 文章标签： sql oracle 服务器网络数据库 disk

本文链接：https://blog.csdn.net/shushugood/article/details/7939485

版权

oracle_sql tuning 专栏收录该内容

37 篇文章 0 订阅

订阅专栏

今天下班时候，接到领导一个任务，查看数据库sqlldr入库堆积情况

首先查看了io cpu swap情况，情况良好

 Glance C.05.00.000             20:22:56   racdb1     ia64                                                                          Current  Avg  High
------------------------------------------------------------------------------------------------------------------------------------------------------
CPU  Util                                                                                                                            |  1%    1%    2%
Disk Util   F     F                                                                                                                  |  6%    8%   22%
Mem  Util   S                          SU                                            UF                                      F       | 95%   95%   95%
Swap Util   U              UR                                              R                                                         | 54%   54%   54%
------------------------------------------------------------------------------------------------------------------------------------------------------
                                                                     PROCESS LIST                                                         Users=    5
                         User      CPU %    Thrd Disk        Memory       Block
Process Name     PID     Name   (2200% max) Cnt  IOrate      RSS/VSS      On
--------------------------------------------------------------------------------
oraagent.bin        4527 grid          3.0    45   0.0    34.3mb  106.8mb SLEEP
oracleunicom        3852 grid          1.8     1   1.4     2.6mb   15.4mb  died
java               23280 oracle        1.8    66   0.0   317.5mb   1.51gb SLEEP
ocssd.bin           4572 grid          0.8    19   2.0    74.4mb   74.4mb SLEEP
ora_dia0_uni       17911 oracle        0.6     1   0.0   185.0mb   1.20gb SLEEP
midaemon            2082 root          0.6    13   0.0   257.3mb  264.0mb SLEEP
ohasd.bin           4406 root          0.4    42   0.0    62.7mb  223.2mb SLEEP
ora_lms0_uni       17917 oracle        0.4     1   0.0   180.4mb  402.7mb SLEEP
ora_lms1_uni       17919 oracle        0.4     1   0.0   182.2mb  387.7mb SLEEP
ora_lmd0_uni       17915 oracle        0.4     1   0.0   182.3mb  440.5mb SLEEP
orarootagent        3407 root          0.4    11   0.0    26.6mb   39.6mb SLEEP
ora_lmon_uni       17913 oracle        0.2     1   2.0   180.4mb  858.6mb SLEEP
ora_pmon_uni       17895 oracle        0.2     1   0.0   170.0mb  703.2mb SLEEP
asm_dia0_+AS        6821 grid          0.2     1   0.0    41.9mb   61.6mb SLEEP
crsd.bin            3392 root          0.2    54   0.0    91.5mb  164.9mb SLEEP
ora_j007_uni       23116 oracle        0.0     1   0.0   169.9mb  306.8mb SLEEP
ora_asmb_uni       17953 oracle        0.0     1   0.0   169.4mb  689.7mb  PIPE
java                8416 grid          0.0    63   0.0    95.3mb   1.35gb SLEEP
ora_pz99_uni       17321 oracle        0.0     1   0.0   167.6mb  440.1mb SLEEP
ora_j001_uni       23101 oracle        0.0     1   0.0   170.9mb  314.9mb SLEEP
ora_p029_uni       26851 oracle        0.0     1   0.0   166.6mb  403.8mb SLEEP

以上没有问题

racdb1#[/]vmstat
         procs           memory                   page                              faults       cpu
    r     b     w      avm    free   re   at    pi   po    fr   de    sr     in     sy    cs  us sy id
    1     0     0  7453084  874872    1    0     6    0     0    0    13  11348  95846  4762   6  1 93

以上也没有问题。

继续......

我们来看看top5

Event	Waits	Time(s)	Avg wait (ms)	% DB time	Wait Class
SQL*Net more data from client	4,989,602	30,840	6	75.10	Network
DB CPU		8,318		20.26
gc buffer busy acquire	218	666	3055	1.62	Cluster
enq: TX - contention	415	555	1337	1.35	Other
enq: HW - contention	798	232	291	0.56	Configuration

在等待事件中，"SQL*Net more data to client"是最为显著的，这意味着什么呢？

这说明数据库在向客户端发送数据，而且是"more"，不停的发送，如果网络状况不好，或者网络流量过大，都可能导致这一等待非常显著。
由于通过公网访问，网络质量不够理想，出现了访问延迟的问题。

公司入库的服务器有两台linux机器 226 和228

226 的情况：

[root@cq ~]# ping 1x.1x.1xx.xx6
PING 1x.1x.1xx.x6 (1x.1x.1xx.xx6) 56(84) bytes of data.
64 bytes from 1x.1x.1xx.xx6: icmp_seq=1 ttl=64 time=1.53 ms
64 bytes from 1x.18.1xx.226: icmp_seq=2 ttl=64 time=0.179 ms
64 bytes from 1x.xx.xx.226: icmp_seq=3 ttl=64 time=0.139 ms
64 bytes from 1x.xx.1xxx.226: icmp_seq=4 ttl=64 time=0.309 ms
64 bytes from 1x.xx.1xx.226: icmp_seq=5 ttl=64 time=0.170 ms
64 bytes from 1x.xx.1xx.226: icmp_seq=6 ttl=64 time=0.143 ms
64 bytes from 1x.xx.1xx.226: icmp_seq=7 ttl=64 time=0.183 ms
64 bytes from 1x.xx.xx.226: icmp_seq=8 ttl=64 time=0.264 ms
64 bytes from 1x.xx.1xx.226: icmp_seq=9 ttl=64 time=0.298 ms
64 bytes from 1x.xx.1xx.226: icmp_seq=10 ttl=64 time=0.156 ms

该服务器没有堆积

————————————————————————————————————————

228情况：

[root@cq ~]# ping 1x
PING 1x (1x) 56(84) bytes of data.
64 bytes from 1x: icmp_seq=1 ttl=64 time=12.53 ms
64 bytes from 1x: icmp_seq=2 ttl=64 time=10.179 ms
64 bytes from 1x: icmp_seq=3 ttl=64 time=15.139 ms
64 bytes from 1x: icmp_seq=4 ttl=64 time=11.309 ms
64 bytes from 1x: icmp_seq=5 ttl=64 time=16.170 ms
64 bytes from 1x: icmp_seq=6 ttl=64 time=18.143 ms
64 bytes from 1x: icmp_seq=7 ttl=64 time=13.183 ms
64 bytes from 1x: icmp_seq=8 ttl=64 time=13.264 ms
64 bytes from 1x: icmp_seq=9 ttl=64 time=15.298 ms
64 bytes from 1x: icmp_seq=10 ttl=64 time=14.156 ms

该服务器堆积严重

问题很明显了，我什么也不用做了，网络的事情交给IT部吧，不需要DB组操心。