今天看到http://www.itpub.net/viewthread.php?tid=936915&extra=&page=1,觉得相当不错,摘录如下:
--------
请教:关于db file sequential read 等待的问题
现在我创建了一个表,该表无索引,当对这个表做全表扫描的时候确一直是db file sequential read 这种等待事件,想请教大家这个会是什么原因?
---------
有时db file scatter read中会有db file sequential read事件,如正好读某个extent中
只有一个块未读入。你做个10046跟踪,看看。
---------
这个等待事件只说明单块读,和索引扫描和全表扫描没直接的关系,只索引扫描都是单块读,全表扫描和全索引扫描允许多块读,但也有可能是单块读的
--------
It can happen when it reads the extent boundary, or when that block happens to have only 1 data block (i.e. not including "overhead" blocks, such as segment header, and in case of ASSM, level 1 and 2 BMB blocks etc).
Yong Huang
-----
遇到以下的情况都可能造成全表扫描过程中的单块读:
db_file_multiblock_read_count参数值为1
extent边界,在一个extent中要读的块只有1个
在buffer cache中已经有部分块。
其他的块,如段头等。
---------
请教下: 对空表进行select 为何不对段头产生'db file sequential read'
环境 window xp +oracle 9.2.0.7.0
创建一个新表 create table ow (i int); 所在表空间 EXTENT_MANAGEMENT LOCAL;SEGMENT_SPACE_MANAGEMENT MANUAL
不插入记录;
重启数据库
alter session set events='10046 trace name context forever, level 12';
select * from ow;
alter session set events='10046 trace name context off';
结果为:
PARSING IN CURSOR #1 len=16 dep=0 uid=118 ct=3 lid=118 tim=158931687719 hv=3898871978 ad='20cd4f5c'
select * from ow
END OF STMT
PARSE #1:c=0,e=508009,p=5,cr=19,cu=0,mis=1,r=0,dep=0,og=4,tim=158931687713
BINDS #1:
EXEC #1:c=0,e=2690,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=158931704154
WAIT #1: nam='SQL*Net message to client' ela= 4 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=64,p=0,cr=3,cu=0,mis=0,r=0,dep=0,og=4,tim=158931710145
WAIT #1: nam='SQL*Net message from client' ela= 4914426 p1=1413697536 p2=1 p3=0
---------
Did you do anything else immediately after you restarted the database? Try flushing buffer cache (alter system flush buffer_cache) before you enable SQL trace and select from the table.
Yong Huang
-------
是本机的测试库,
重启数据库就是为干净的清除buffer_cache 中的数据;重启后,没有其他连接做操作; alter system flush buffer_cache 是10g 后才可以使用的吧
--------
In 9i, you can
ALTER SYSTEM SET EVENTS 'immediate trace name flush_cache';
My test shows that once you flush buffer cache, you'll see the wait on reading the segment header.
Yong Huang
--------
--------
请教:关于db file sequential read 等待的问题
现在我创建了一个表,该表无索引,当对这个表做全表扫描的时候确一直是db file sequential read 这种等待事件,想请教大家这个会是什么原因?
---------
有时db file scatter read中会有db file sequential read事件,如正好读某个extent中
只有一个块未读入。你做个10046跟踪,看看。
---------
这个等待事件只说明单块读,和索引扫描和全表扫描没直接的关系,只索引扫描都是单块读,全表扫描和全索引扫描允许多块读,但也有可能是单块读的
--------
It can happen when it reads the extent boundary, or when that block happens to have only 1 data block (i.e. not including "overhead" blocks, such as segment header, and in case of ASSM, level 1 and 2 BMB blocks etc).
Yong Huang
-----
遇到以下的情况都可能造成全表扫描过程中的单块读:
db_file_multiblock_read_count参数值为1
extent边界,在一个extent中要读的块只有1个
在buffer cache中已经有部分块。
其他的块,如段头等。
---------
请教下: 对空表进行select 为何不对段头产生'db file sequential read'
环境 window xp +oracle 9.2.0.7.0
创建一个新表 create table ow (i int); 所在表空间 EXTENT_MANAGEMENT LOCAL;SEGMENT_SPACE_MANAGEMENT MANUAL
不插入记录;
重启数据库
alter session set events='10046 trace name context forever, level 12';
select * from ow;
alter session set events='10046 trace name context off';
结果为:
PARSING IN CURSOR #1 len=16 dep=0 uid=118 ct=3 lid=118 tim=158931687719 hv=3898871978 ad='20cd4f5c'
select * from ow
END OF STMT
PARSE #1:c=0,e=508009,p=5,cr=19,cu=0,mis=1,r=0,dep=0,og=4,tim=158931687713
BINDS #1:
EXEC #1:c=0,e=2690,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=158931704154
WAIT #1: nam='SQL*Net message to client' ela= 4 p1=1413697536 p2=1 p3=0
FETCH #1:c=0,e=64,p=0,cr=3,cu=0,mis=0,r=0,dep=0,og=4,tim=158931710145
WAIT #1: nam='SQL*Net message from client' ela= 4914426 p1=1413697536 p2=1 p3=0
---------
Did you do anything else immediately after you restarted the database? Try flushing buffer cache (alter system flush buffer_cache) before you enable SQL trace and select from the table.
Yong Huang
-------
是本机的测试库,
重启数据库就是为干净的清除buffer_cache 中的数据;重启后,没有其他连接做操作; alter system flush buffer_cache 是10g 后才可以使用的吧
--------
In 9i, you can
ALTER SYSTEM SET EVENTS 'immediate trace name flush_cache';
My test shows that once you flush buffer cache, you'll see the wait on reading the segment header.
Yong Huang
--------
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7928987/viewspace-364654/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7928987/viewspace-364654/