读undo 导致db file sequential read

db file sequential read:

SQL> select * from v$mystat where rownum<10;

       SID STATISTIC#	   VALUE
---------- ---------- ----------
      1141	    0	       0



SQL> delete from test400;
delete from test400
            *
第 1 行出现错误:
ORA-30036: 无法按 8 扩展段 (在还原表空间 'UNDOTBS1' 中)


SQL> /

已删除2783106行。


select * from v$mystat where rownum<10;
--1719

select count(*) from test400;

select a.session_id,
       a.sql_id,
       a.machine,
       a.blocking_session,
       a.sample_time,
       a.module,
       a.PROGRAM,
       a.event,
       a.p1,
       a.P1TEXT,
       a.p2,
       a.P2TEXT,
       a.P3,
       a.P3TEXT,
       b.SQL_TEXT
  from v$active_session_history a, v$sqlarea b
 where a.sql_id = b.sql_id;



SESSION_ID	1719	
SQL_ID	5yu8jna5gqjhn	
MACHINE	WORKGROUPTLCB-PC	
BLOCKING_SESSION		
SAMPLE_TIME	10-4月 -19 07.18.39.915 下午	
MODULE	PL/SQL Developer	
PROGRAM	plsqldev.exe	
EVENT	db file sequential read	
P1	7	
P1TEXT	file#	
P2	10444	
P2TEXT	block#	
P3	1	
P3TEXT	blocks	
SQL_TEXT	select count(*) from test400 	


参数：

? ?
 P1 = file#
? ?
 P2 = block#
? ? 
 P3 = blocks
file# ? 
指的是Oracle正在读取的文件file#。Oracle8/9中file#是绝对文件号。



block# ?指的是Oracle正在读取的块号。一次只能读取一个块。

blocks ?

这个参数明确了Oracle正在从file#的block#开始读取的块数。通常是”1“，但如果P3>1，那么这就是一次多块读。

当从SORT(TEMPORARY)段读取数据时，多块的”db file sequential read“可以在更早的Oracle版本中看到。



select owner,segment_name,segment_type,partition_name from dba_extents where file_id=7 and 10444 between block_id and block_id+blocks-1;


SQL> select owner,segment_name,segment_type,partition_name from dba_extents where file_id=7 and 10444 between block_id and block_id+blocks-1;


OWNER	   SEGMENT_NAME 		  SEGMENT_TYPE			 PARTITION_NAME
---------- ------------------------------ ------------------------------ ------------------------------------------------------------------------------------------
SYS	   _SYSSMU9_1650507775$ 	  TYPE2 UNDO

SQL> SQL>