DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------ ------------------------------------
827 6
SQL> select count(*) from scott.t12;
COUNT(*)
----------
199
exec dbms_stats.gather_table_stats('scott','t12');
SQL> select table_name,avg_row_len from dba_tables where table_name='T12';
TABLE_NAME AVG_ROW_LEN
------------------------------ -----------
T12 24
select p.spid,s.sid,s.serial# from v$session s,v$process p where s.sid=sys_context('userenv','sid') and s.paddr=p.addr;
SPID SID SERIAL#
------------------------ ---------- ----------
15859916 730 1333
set arraysize 199 <---设置每次返回199行，因为AVG_ROW_LEN=24，所以总的字节数为4776bytes
---session 2:
col event format a40
set linesize 120
select sid,event,total_waits,time_waited,average_wait from v$session_event where sid=730 and event like 'SQL*Net%';
SID EVENT TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
---------- ---------------------------------------- ----------- ----------- ------------
730 SQL*Net message to client 27 0 0
730 SQL*Net message from client 26 61386 2361
---session 1:
set arraysize 199
select * from scott.t12;
---session 2: session 1的执行后，没有出现SQL*NET more data to client等待事件，这是因为sdu_size默认值为8192bytes > 4776bytes，所以能在一个网络包里传送过去
SYS@tstdb1-SQL> select sid,event,total_waits,time_waited,average_wait from v$session_event where sid=730 and event like 'SQL*Net%';
SID EVENT TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
---------- ---------------------------------------- ----------- ----------- ------------
730 SQL*Net message to client 29 0 0
730 SQL*Net message from client 28 72039 2572.82
接下来我们人工将客户端的sdu_size调整为3072bytes，调整的方法有多种，下面有列举：
---session 1:
select p.spid,s.sid,s.serial# from v$session s,v$process p where s.sid=sys_context('userenv','sid') and s.paddr=p.addr;
SPID SID SERIAL#
------------------------ ---------- ----------
6357954 467 6611
---session 2:
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 12 0 0
467 SQL*Net message from client 11 468 42.52
---session 1:
set arraysize 199
select * from scott.t12;