HEADER_FILE HEADER_BLOCK
----------- ------------
5 130 <---segment header
SELECT distinct DBMS_ROWID.rowid_block_number(rowid),DBMS_ROWID.rowid_relative_fno(rowid) from scott.t1 ;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------ ------------------------------------
131 5 <---真正存放数据的块仅一个
---session 2: 获取session基本信息，确认所有行的长度和<SDU_SIZE，SDU_SIZE默认情况下是8192字节
select sum(length(user_id)+length(username)+length(created)) from scott.t1 ; <---确保所有记录总长<默认的SDU值8192字节，否则会出现SQL*NET more data to client事件，后面会有专针对于SQL*NET more data to client的讨论
SUM(LENGTH(USER_ID)+LENGTH(USERNAME)+LENGTH(CREATED))
-----------------------------------------------------
550
SQL> 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#
------------------------ ---------- ----------
17105026 266 1433
---session 3：记录session 2的执行前的SQL*NET等待事件和logical reads次数
set linesize 120
col event format a40
select sid,event,total_waits,time_waited from v$session_event where sid=266 and event in ('SQL*Net message to client','SQL*Net message from client','SQL*Net more data to client');
SID EVENT TOTAL_WAITS TIME_WAITED
---------- ---------------------------------------- ----------- -----------
266 SQL*Net message to client 139 0
266 SQL*Net message from client 138 188288
col name format a26
select n.name,s.sid,s.VALUE from v$sesstat s,v$statname n where n.name='session logical reads' and sid=266 and n.statistic#=s.statistic#;
NAME SID VALUE
-------------------------- ---------- ----------
session logical reads 266 4537
跟踪session 2的sid，tracefile里可以记录逻辑读访问哪些块，前提是必须保证这些块不在buffer cache里，可以先flush buffer_cache
exec DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id=>266,serial_num=>1433,waits=>TRUE,plan_stat=>NULL);
---session 2: 执行select * from scott.t1 ;
set arraysize 21
select /*+ dynamic_sampling(0) */ * from scott.t1 ;
---session 3：记录session 2的执行后的SQL*NET等待事件，session logical reads次数
set linesize 120
col event format a40
select sid,event,total_waits,time_waited from v$session_event where sid=266 and event in ('SQL*Net message to client','SQL*Net message from client','SQL*Net more data to client');
SID EVENT TOTAL_WAITS TIME_WAITED
---------- ---------------------------------------- ----------- -----------
266 SQL*Net message to client 142 0
266 SQL*Net message from client 141 199871