db file sequential read等待事件有3个参数：file＃，first block＃，和block数量。这个等待事件有3个参数P1，P2，P3，其中P1代表Oracle要读取的文件的绝对文件号，P2代表Oracle从这个文件中开始读取的起始数据块号，P3代表读取的BLOCK数量，通常这个值为1，表明是道单个BLOCK被读取。 SQL> select SESSION_ID,NAME,P1,P2,P3,WAIT_TIME,CURRENT_OBJ#,CURRENT_FILE#,CURRENT_BLOCK# from v$active_session_history ash, v$event_name enm where ash.event#=enm.event# and SESSION_ID=39; 2 3 4 SESSION_ID NAME P1 P2 P3 WAIT_TIME CURRENT_OBJ# CURRENT_FILE# CURRENT_BLOCK#---------- ------------------------------ ---------- ---------- ---------- ---------- ------------ ------------- --------------39 db file sequential read 4 531505 1 0 77088 4 53150539 db file sequential read 4 528344 1 0 77088 4 52834439 db file sequential read 4 520464 1 0 77088 4 52046439 db file sequential read 4 517219 1 0 77088 4 51721939 db file sequential read 4 502844 1 0 77088 4 50284439 db file sequential read 4 489070 1 0 77088 4 48907039 db file sequential read 4 480568 1 0 77088 4 48056839 db file sequential read 4 478611 1 0 77088 4 47861139 db file sequential read 4 473972 1 0 77088 4 47397239 db file sequential read 4 473407 1 0 77088 4 47340739 db file sequential read 4 458534 1 0 77088 4 458534SESSION_ID NAME P1 P2 P3 WAIT_TIME CURRENT_OBJ# CURRENT_FILE# CURRENT_BLOCK#---------- ------------------------------ ---------- ---------- ---------- ---------- ------------ ------------- --------------39 db file sequential read 4 458186 1 0 77088 4 45818639 db file sequential read 4 457625 1 0 77088 4 45762539 db file sequential read 4 457150 1 0 77088 4 45715039 db file sequential read 4 452548 1 0 77088 4 45254839 direct path read 4 440464 8 0 78043 4 44042439 direct path read 4 430640 8 0 78043 4 43063239 direct path read 4 425312 8 0 78043 4 42530439 direct path read 4 421408 8 0 78043 4 42140039 direct path read 4 416384 8 0 78043 4 41637639 direct path read 4 410928 8 0 78043 4 41092039 direct path read 4 406240 8 0 78043 4 406232SESSION_ID NAME P1 P2 P3 WAIT_TIME CURRENT_OBJ# CURRENT_FILE# CURRENT_BLOCK#---------- ------------------------------ ---------- ---------- ---------- ---------- ------------ ------------- --------------39 direct path read 4 404944 8 0 78043 4 40493639 direct path read 4 399648 8 0 78043 4 39964039 direct path read 4 394544 8 0 78043 4 39453639 direct path read 4 389344 8 0 78043 4 38933639 direct path read 4 383936 8 0 78043 4 38392839 direct path read 4 379280 8 0 78043 4 37927239 direct path read 4 374288 8 0 78043 4 37428039 direct path read 4 369504 8 0 78043 4 36949639 direct path read 4 364144 8 0 78043 4 36413639 direct path read 4 359120 8 0 78043 4 35911239 direct path read 4 354192 8 0 78043 4 354184SESSION_ID NAME P1 P2 P3 WAIT_TIME CURRENT_OBJ# CURRENT_FILE# CURRENT_BLOCK#---------- ------------------------------ ---------- ---------- ---------- ---------- ------------ ------------- --------------39 direct path read 4 350192 8 0 78043 4 35018439 direct path read 4 346832 8 0 78043 4 34682439 direct path read 4 341936 8 0 78043 4 34192839 direct path read 4 337088 8 0 78043 4 33708039 direct path read 4 331632 8 0 78043 4 33162439 direct path read 4 327056 8 0 78043 4 32704839 direct path read 4 321984 8 0 78043 4 32197639 direct path read 4 316784 8 0 78043 4 31677639 direct path read 4 311680 8 0 78043 4 31167239 direct path read 4 306448 8 0 78043 4 30644039 direct path read 4 301200 8 0 78043 4 301192SESSION_ID NAME P1 P2 P3 WAIT_TIME CURRENT_OBJ# CURRENT_FILE# CURRENT_BLOCK#---------- ------------------------------ ---------- ---------- ---------- ---------- ------------ ------------- --------------39 direct path read 4 295688 8 0 78043 4 29568039 direct path read 4 290600 8 0 78043 4 29059239 direct path read 4 286744 8 0 78043 4 28673639 direct path read 4 281464 8 0 78043 4 28145639 direct path read 4 276136 8 0 78043 4 27612839 direct path read 4 271064 8 0 78043 4 27105639 direct path read 4 266136 8 0 78043 4 26612839 direct path read 4 261160 8 0 78043 4 26115239 direct path read 4 256200 8 0 78043 4 25619239 direct path read 4 255000 8 0 78043 4 25499239 direct path read 4 254824 8 0 78043 4 254816发现很多都是 db file sequential read等待事件 SQL> select owner,object_name,object_type from dba_objects where object_id=77088;OWNER OBJECT_NAM OBJECT_TYPE---------- ---------- -------------------SCOTT T1 TABLE BLOCK_ID: extent的起始块BLOCKS:extent块的数量SQL> select owner,segment_name,segment_type from dba_extents where file_id = 4 and 531505 between block_id and block_id+blocks-1; 2 OWNER SEGMENT_NA SEGMENT_TYPE---------- ---------- ------------------SCOTT T1 TABLE这里是回表造成的单块读模拟下索引上的单块读,模拟index full scanSQL_ID 0r8t4zj3urrnp, child number 0-------------------------------------select id from t1 where t1.id is not null order by id Plan hash value: 2463307338 ----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 42364 (100)| ||* 1 | INDEX FULL SCAN | ID_IDX1 | 20M| 248M| 42364 (1)| 00:08:29 |---------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("T1"."ID" IS NOT NULL) Note----- - dynamic sampling used for this statement (level=2) SQL> col name format a30SQL> select SESSION_ID,NAME,P1,P2,P3,WAIT_TIME,CURRENT_OBJ#,CURRENT_FILE#,CURRENT_BLOCK# from v$active_session_history ash, v$event_name enm where ash.event#=enm.event# and SESSION_ID=24; 2 3 4 SESSION_ID NAME P1 P2 P3 WAIT_TIME CURRENT_OBJ# CURRENT_FILE# CURRENT_BLOCK#---------- ------------------------------ ---------- ---------- ---------- ---------- ------------ ------------- --------------24 db file sequential read 4 589800 1 0 78139 4 58980024 db file sequential read 4 483506 1 0 77088 4 48350624 db file sequential read 4 447172 1 0 77088 4 44717224 db file sequential read 4 469775 1 0 77088 4 46977524 db file sequential read 4 458597 1 0 77088 4 45859724 db file scattered read 4 446041 7 0 77088 4 44604424 db file scattered read 4 525865 8 0 77088 4 871424 db file sequential read 4 8714 1 0-1 4 871424 db file sequential read 1 64155 1 040 1 6415524 db file sequential read 1 11540 1 040 1 1154024 db file sequential read 1 19442 1 062 1 19442SESSION_ID NAME P1 P2 P3 WAIT_TIME CURRENT_OBJ# CURRENT_FILE# CURRENT_BLOCK#---------- ------------------------------ ---------- ---------- ---------- ---------- ------------ ------------- --------------24 db file scattered read 1 46040 8 068 1 4604024 db file sequential read 1 172 1 0-1 0 024 db file sequential read 1 21910 1 0-1 0 024 db file scattered read 1 83984 8 0-1 0 024 db file sequential read 1 64354 1 0-1 0 024 db file sequential read 1 64335 1 0-1 0 024 db file scattered read 1 70152 3 0-1 0 024 db file scattered read 1 55196 5 0-1 0 024 db file scattered read 1 5376 8 0-1 0 024 db file sequential read 1 2867 1 0-1 0 021 rows selected.select owner,segment_name,segment_type from dba_extents where file_id = 4 and 589800 between block_id and block_id+blocks-1; SQL> 2 OWNER SEGMENT_NAME SEGMENT_TYPE------------------------------ --------------------------------------------------------------------------------- ------------------SCOTT ID_IDX1 INDEX
0 0
