EVENT TOTAL_WAITS TIME_WAITED
---------------------------------------- ----------- -----------
SQL*Net message from client 23 111804
---session 1:
select * from scott.t1;
---session 2:
SYS@tstdb1-SQL> select event,total_waits,time_waited from v$session_event where sid=266 and event in ('SQL*Net message from client')
EVENT TOTAL_WAITS TIME_WAITED
---------------------------------------- ----------- -----------
SQL*Net message from client 27 198030
前后对比发现SQL*Net message from client从23->27，增长了4次，从tracefile里可以清楚的看出这21行记录是按照1->15->5分三批次返回的
FETCH #4573469256:c=0,e=119,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=838529891,tim=11896171488908
WAIT #4573469256: nam='SQL*Net message from client' ela= 272 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=11896171489214
WAIT #4573469256: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=11896171489244
FETCH #4573469256:c=0,e=40,p=0,cr=1,cu=0,mis=0,r=15,dep=0,og=1,plh=838529891,tim=11896171489275
WAIT #4573469256: nam='SQL*Net message from client' ela= 735 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=11896171490033
WAIT #4573469256: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=11896171490068
FETCH #4573469256:c=0,e=31,p=0,cr=1,cu=0,mis=0,r=5,dep=0,og=1,plh=838529891,tim=11896171490091
STAT #4573469256 id=1 cnt=21 pid=0 pos=1 obj=41088 op='TABLE ACCESS FULL T1 (cr=4 pr=0 pw=0 time=107 us cost=3 size=357 card=21)'
WAIT #4573469256: nam='SQL*Net message from client' ela= 410 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=11896171490602
---session 1：从autotrace里也可以看到roundtrips=3
SYS@tstdb1-SQL> set autotrace traceonly statistics
SYS@tstdb1-SQL> select * from scott.t1
21 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1402 bytes sent via SQL*Net to client
531 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
21 rows processed
当我们Select一个大表时，往往需要等待很长时间才能得到所有记录，这之中的大部分时间都耗在了网络传输上，这期间session绝大部分情况下都处于INACTIVE状态下，等待的事件往往是"SQL*NET message from client"，就像下面这样
SYS@tstdb1-SQL> select sid,event,status from v$session where sid=266;
SID EVENT STATUS
---------- ---------------------------------------- --------
266 SQL*Net message from client INACTIVE
有同学可能会问，既然结果记录还在返回过程中，为何不是ACTIVE状态，我们之前的测试清楚的回答了这个问题：全量的结果集是按批次返回的，每当一个批次的结果返回至客户端，客户端发起给服务器端的确认响应时，才会触发一次"SQL*Net message from client"等待事件，因为这个响应的瞬间极短，所以我们几乎无法观察到针对SQL*Net message from client的ACTIVE等待。