---session 2: 观察到多了一次SQL*Net more data to client等待，这时因为4776bytes要分为3072bytes->1704bytes两批次进行传送
select sid,event,total_waits,time_waited,average_wait from v$session_event where sid=467 and event like 'SQL*Net%';
SID EVENT TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
---------- ---------------------------------------- ----------- ----------- ------------
467 SQL*Net message to client 14 0 0
467 SQL*Net more data to client 1 0 0
467 SQL*Net message from client 13 3342 257.11
附：SDU可以通过以下三种方式进行定义：
服务器端的sqlnet.ora定义、客户端的sqlnet.ora定义：
default_sdu_size=3072
服务器端的listener.ora定义
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SDU=3072) <---
(GLOBAL_DBNAME = manaux1)
(Oracle_HOME = /oracle/app/oracle/product/11.2.0/db_1)
(SID_NAME = manaux)
)
)
客户端的tnsnames.ora里定义:
tstdb1 =
(DESCRIPTION =
(sdu=3072) <---
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.141.209)(PORT = 1521))
)
(CONNECT_DATA =
(Service_name = tstdb1)
(SERVER = DEDICATED)
)
)
在上节利用"SQL*Net message from client"事件跟踪服务器->客户端传输效率的例子中，如果要使ad.ca_mms_file表的全表扫描更快的返回结果，我们可以调大sdu_size，调整方法如下：
select owner,table_name,avg_row_len from dba_tables where table_name='CA_MMS_FILE';
OWNER TABLE_NAME AVG_ROW_LEN
------------------------------ ------------------------------ -----------
AD CA_MMS_FILE 181
array=100的情况下，Sdu_size至少应修改成> 18100 bytes (181*100)，把sdu_size设为20000的情况下，得到如下统计结果
SQL*Net message from client sid: 10 total_waits:94 time_waited:3771
SQL*Net message from client sid: 10 total_waits:96 time_waited:3864
SQL*Net message from client sid: 10 total_waits:99 time_waited:4003
SQL*Net message from client sid: 10 total_waits:101 time_waited:4096
SQL*Net message from client sid: 10 total_waits:103 time_waited:4189
SQL*Net message from client sid: 10 total_waits:106 time_waited:4328
SQL*Net message from client sid: 10 total_waits:108 time_waited:4421
SQL*Net message from client sid: 10 total_waits:111 time_waited:4561
SQL*Net message from client sid: 10 total_waits:114 time_waited:4699
SQL*Net message from client sid: 10 total_waits:116 time_waited:4792
SQL*Net message from client sid: 10 total_waits:119 time_waited:4931
平均下来每传送100条记录耗时约为464ms，比sdu_size采用默认值的情况下降低了111ms，提速近20%，SQL*Net more data to client等待事件大大减少，
所以合理调大sdu size设置值也能显著提高服务器端与客户端传输效率
---------< Part 2 把SQL*Net 三类事件串联起来连同session logical reads分析一下它们之间的联系 >---------
(1) arraysize<=SDU size, CTAS方式创建测试表
---session 1: 创建测试表
create tablespace ts0111 datafile '/oradata06/testaaaaa/ts0111.dbf' size 32m segment space management auto;
create table scott.t1 tablespace ts0111 as select * from all_users;
select count(*) from scott.t1 ;
COUNT(*)
----------
21
SQL> select header_file,header_block from dba_segments where segment_name='T1' and owner='SCOTT';