(1)和(2)的区别在于session logical reads数量增加了,SQL*NET系列等待事件数量保持一致,这是因为CTAS创建出来的表其占用的blocks数量是最少的刚好能够存放下现有的记录,而insert into创建出来的表是按照extent为基本单位扩展的,因此HWM比前者要高,进而全表扫描时经历的blocks会更多,session logical reads值也就更大了。有兴趣的TX可以使用dbms_space.space_usage,或者dump segment header观察这两种方式创建出来的表的HWM各是多少
(3) arraysize > SDU size的情况
以我们之前创建的scott.t12表为例,设置客户端的Sdu_size=3072 bytes
---session 1: 连接上数据库
select count(*) from scott.t12;
COUNT(*)
----------
199
select owner,table_name,avg_row_len from dba_tables where table_name='T12';
OWNER TABLE_NAME AVG_ROW_LEN
------------------------------ ------------------------------ -----------
SCOTT 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#
------------------------ ---------- ----------
15138938 664 3
set arraysize 199
---session 2:
select sid,event,total_waits,time_waited,average_wait from v$session_event where sid=664 and event like 'SQL*Net%';
SID EVENT TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
---------- ---------------------------------------- ----------- ----------- ------------
664 SQL*Net message to client 19 0 0
664 SQL*Net message from client 18 27997 1555.38
select n.name,s.sid,s.VALUE from v$sesstat s,v$statname n where n.name='session logical reads' and sid=664 and n.statistic#=s.statistic#;
NAME SID VALUE
---------------------------------------------------------------- ---------- ----------
session logical reads 664 776
---session 1:
select * from scott.t12;
---session 2:
select sid,event,total_waits,time_waited,average_wait from v$session_event where sid=664 and event like 'SQL*Net%';
SID EVENT TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
---------- ---------------------------------------- ----------- ----------- ------------
664 SQL*Net message to client 22 0 0
664 SQL*Net more data to client 1 0 0
664 SQL*Net message from client 21 36219 1724.73
select n.name,s.sid,s.VALUE from v$sesstat s,v$statname n where n.name='session logical reads' and sid=664 and n.statistic#=s.statistic#;
NAME SID VALUE
---------------------------------------------------------------- ---------- ----------
session logical reads 664 779