db file scattered read

db file scattered read(本文由thomaswoo_dba翻译,转载请注明出处)

db file scattered read 包含三个参数:file#,first block# ,block count
在oracle10g里,本等待事件是归集于User I/O wait class的,记住下面处理db file scatted read的思想:
1)oracle session要求等待多个相邻的数据块被从disk读到SGA(不好译,下面是英文原文)
The Oracle session has requested and is waiting for multiple contiguous database blocks
(up to DB_FILE_MULTIBLOCK_READ_COUNT) to be read into the SGA from disk.
2)多块I/O需要是与full table scans 和index fast full scan操作相关联的.
(注:与db file sequential read 相关联的是index unique scan 与index range scan)
3)两个重要的参数TIME_WAITED,AVERAGE_TIME是以session为单位的
4)明显的db file scattered read等待事件通常是应用程序出了问题

Common Causes,Diagnosis,and Actions

db file scattered read 等待事件非常像db file sequential read事件,不同之外是db file sequential read
是single-block read而db file scattered read是multiblock read(可以看出两个对I/O的需要差别很大)

db file scattered read 等待事件被SQL语句初始化(包括用户与递归),然后对tables and indexes进行全扫描.
与某些说教相反,db file scattered read 也并不总是坏事(一般对全表数据获取超过其30%时,用
db file scattered read要快一些,还有对超小表访问也没有问题).

下面有些比较有用的诊断方法
Session-Level Diagnosis

multiblock 读什么时候可以视为问题,相对于db file sequential read,你必须也为系统建立一个基准
进程花在db file scattered read的时间的总和是比较好的指引,同样要记住的时LOGON_TIME 与
明显的wait time是与其它非空闲事件相关联的.

除了V$SESSION_EVENT视图之外,V$SESSTAT也为现有session提供全表扫描的统计数据.
然而,V$SESSION_EVENT比较全面,例子如下:

select a.sid, b.name, a.value
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and a.value <> 0
and b.name = 'table scan blocks gotten'
order by 3,1;


SID NAME VALUE
---- -------------------------- ----------
111 table scan blocks gotten 59,535
8 table scan blocks gotten 567,454
164 table scan blocks gotten 5,978,579
158 table scan blocks gotten 14,798,247

同db file sequential read一样,减少db file scattered read等待时间可以从两个方面下手
1)优化SQL语句可以减少大部分的等待,这个方面的目标也是减少物理与逻辑读
2)Reduce the average wait time.

与我们前面谈到db file sequential read时一样,你必须找到产生等待事件的SQL语句,这是一件比较难的事,
如果你没有收集到进程从开始到结束的所有的SQL的话.The Active Session History在oracle 10g中能自动收集SQL执行信息,通过OEM能轻松的找到.找到SQL后,可能测试它的执行计划.这时可以看看以下几个问题:
1)这条SQL必须要进行全扫描吗?(包括full table scan 与index fast full scan)
2)如果改用index range scan 或者unique scan是不是更有效?
3)是不是用对了驱动表
4)SQL的条件谓词是不是适合hash或merge join
5)如果full scan是合适的,能不能用并行查询来改进响应时间

当一个应用程序运行了很长一段时间,突然产生大量的db file scattered read等待事件并且没有作任何的源码改变,这时你可以看看有没有相关的索引被删除和变为unusable,这时可以通过DBA_OBJECTS视图的LAST_DDL_TIME去查看最近有没有索引被建立或删除.

ALTER TABLE MOVE语句会标记所有的索引为unusable,这时要求rebuild所有的索引.
(这里有个要注意的问题,当索引出现坏块时,alter index rebuild是会失改的,因为这只是对索引进行扫描并重组,一旦重组到坏块部分时,就会失败,这时就必要用到alter index rebuild online,对全表扫描并重组),继续上面的问题,对分区表进行DDL操作时也对将相应的索引标记为unusable,例如增加新的分区,删除分区,合并分区,移动分区,拆分分区,truncate分区等操作.

有几个初始化参数,它的值越大,优化器越倾向化于全扫描.
DB_FILE_MULTIBLOCK_READ_COUNT(MBRC)(metalink 上说,这人参数只用于 Full Table Scans 和 Index Fast Full Scan)
HASH_AREA_SIZE
OPTIMIZER_INDEX_COST_ADJ
要合适的调整好这几个参数,以使它们对应用程序产生负面影响.

另一个能对执行计行的质量产生负面影响,并产生额外I/O的因素就是inaccurate statistics(不正确的统计数据).当优化器通过统计数据看到一个实际非常大的表只有少量数据,它可能会选择全表扫描.这在表内连接时最为明显.这时我们就要检查all_tables的LAST_ANALYZED列.
DBMS_STATS.GATHER_TABLE_STATS 用GATHER STABLE选项可以上oracle自动分析，当表的monitoring 要enabled,oracle 会在表的记录改变10%时考虑自动分析(oracle 10g以上table monitoring是自动enabled)

注意:当分析表与索引时用COMPUTER参数,oracle会全表扫描，并会在V$SESSION_EVENT和V$SYSTEM_EVENT中
　　　统计db file scattered read，这时最好重启实例.

System-Level Diagnosis

查询V$SYSTEM_EVENT并对TIME_WAITED列排序,找到db file scattered read等待事件.
最有用的还是AVERAGE_WAIT与TIME_WAITED,掌握db file scattered read优化与掌握
db file sequential read 等待事件一样．其实系统级的诊断也只能从某方面说明数据库的
整体性能和瓶颈，并不能对具体的事务产生任何的影响，比如在V$SYSTEM_EVENT视图中记录的只是实例启动以来的数据，当然也可以在V$SESSION_EVENT中发现性能瓶颈，并对相应的sid进行10046事件跟踪(在此看来，oracle优化的方法论比较重要，哦，对了，什么是优化方法论看看The OWI Philosophy这章.)

Why Doesdb file sequential read eventShow Up in a Full Table Scan Operation

如果你对全表扫描进行过深入trace，你可能会发现db file sequential read像三明志一样夹在db file scattered read中,这个是不是性能问题,取决于single-block读的环境.下面是几个原因

1)Extent boundary(区临界）当最后block组在分区中只有一block时,oracle获取这块就只能是single-block读了，这个是正常的,当然如你指定很小的块表时，通常这就是问题了，所以经常重组表与索引是可以提高访问速度的．
　如table block is 8k,MBRC是8blocks,and the extent size is 72k(9blocks),这样全表扫描之后就会有db file sequential read,下面是例子：
(MBRC db_file_multiblock_read_countinteger 16)
(select file_id,extent_id,block_id,blocks
from dba_extents where EXTENT_ID=256;找到extent的大小）
WAIT #1: nam='db file scattered read' ela= 470 p1=7 p2=18 p3=8
WAIT #1: nam='db file sequential read' ela= 79 p1=7 p2=26 p3=1
WAIT #1: nam='db file scattered read' ela= 459 p1=7 p2=27 p3=8
WAIT #1: nam='db file sequential read' ela= 82 p1=7 p2=35 p3=1
WAIT #1: nam='db file scattered read' ela= 466 p1=7 p2=36 p3=8
WAIT #1: nam='db file sequential read' ela= 79 p1=7 p2=44 p3=1
WAIT #1: nam='db file scattered read' ela= 460 p1=7 p2=45 p3=8
WAIT #1: nam='db file sequential read' ela= 60 p1=7 p2=53 p3=1
WAIT #1: nam='db file scattered read' ela= 779 p1=7 p2=54 p3=8
WAIT #1: nam='db file sequential read' ela= 78 p1=7 p2=62 p3=1
2)Cached blocks
See explanation in the “Why Does a Full Scan Operation Request Fewer Blocks than the MBRC” section. This is not a problem

3)Chained or migrated rows
则chain_cnt对应的表非空，则表示有行连接。行连接表示数据在该处已经存储不下，需要在另外一个地方保存。首先读该处，然后再根据连接到另外一个地方读取。
最好要清除行链接，　
(方法：
alter table ttt move
analyze table ttt compute statistics)

3)Index entry creation
当SQL的执行计划call for a full table scan时，有时会出现对index的db file sequential read读
这时你可能会认为,oracle产生了新的bug?其实这并不一个问题.
比如TABLE_A有一条索引并产生db file sequential read，它将从索引里读出的数据到SGA,用于insert到TABLE_B,注意这里会产生大量的db file sequential read，并与db file scattered read 相对．
这时很多DBA会认为系统的瓶颈是由db file scattered read引起的，其实错了．
我们总不能天天希望有db file scattered read，然后建立相应的index完事．
并且天真的认为insert语句不会产生db file sequential read．下面的例子会让你改更观念：让你认为不止是update 和delete才会产生db file sequential read(这样看来dba也不是人干的事,没有什么靠的住)

-- SQL statement
insert into table_A
select * from table_B;

-- Explain plan
LVL OPERATION OBJECT
--- ---------------------- -------------------
1 INSERT STATEMENT
2 TABLE ACCESS FULL TABLE_B

-- Wait event statistics
SID EVENT TIME_WAITED
--- ------------------------------ -----------
7 SQL*Net message from client 5
7 latch free 11
7 log file switch completion 155
7 log buffer space 205
7 log file sync 467
7 db file scattered read 1,701
7db file sequential read 185,682

Why Does a Full Scan Operation Request Fewer Blocks than the MBRC?

为什么全表扫描需要的块比MBRC少.
如果你监视full table scan 通过连速不断的查询V$SESSION_WAIT,或通过trace 10046事件.
你会看到db file scattered read需求的块比MBRC定义的少．这种不规取决于下面几个原因：

1)extent里的最后block组比MBRC少，如果MBRC设为8,而extent有10块，oracle将会产生两个多块读
　一个读8块,一个读2块,因为MBRC不能跨extent

2)当一个或更多个块被读入buffer cache中,oracle就会将fetch分开为两个以上的读操作,它们可能由一个single-block 读或multiblock 读,举个例子,假设MBRC是8,并且要读取的block set中,第3与第7己经被读入buffer cache中,oracle将会有三个读操作,第一个读blocks 1和2,第二个读blocks4到6,第三个读block 8
因为第三个读操作是single-block读,所以当然会产生db file sequential read等待事件.
然而,因为第一个读操作读了两个以上blocks,所以会产生db file scattered read.因此cached blocks会产生full tables scan并且读操作比预期的要多.
(你可能认为MBRC设为8,而我只读7块,为什么产生了三次读,并且还有一次是db file sequential read
这里介绍了原因,但这个是非常难以控制的,因为很多文章介绍buffer cache越大,看来还是看实际)

Setting the
DB_FILE_ MULTIBLOCK_ READ_ COUNT (MBRC)

那么应该怎样去设定MBRC呢?
就像前面介绍的一样,高MBRC能让optimizer趋向于全扫描.
其实正确的MBRC值取决于你采用的是那种数据应用,是DSS(应用决策系统,主要用于数据仓库)
还是OLTP(在线数据处理系统,主要用于生产数据库).
多并行进程能从高MBRC中受益,因为它比让full scan操作更快的去完成它的工作.
如果你的数据库要同时处理多并行进程与OLTP,最好找一个平衡点,设其为8是比较保守的

如果全扫描是最好的方法,你想让SQL语句扫描对像的块大过你系统设定的最大值,为什么就小值呢?
你最好找一个最大值并且应用到应用程序中,让之产生全扫描.

当然MBRC是有极限的,它取块于几个方面,包括sstiomax,DB_BLOCK_SIZE,DB_BLOCK_BUFFERS
sstiomax是oracle内部限制,它限制了能被single I/O读或写操作的数据的总数(超不好译,英文原文
(Thesstiomaxis an Oracle internal limit, which limits the amount of data that can be transferred in a single I/O of a read or write operation,This parameter is fixed and cannot be tuned/changed读英文也不知道他说的是什么),sstiomax的值是内oracle内部代码设置的,在oracle8以前的版本中值是128K,从oracle8开始设为1M了(我个人估计oracle可能做了很多测试,现在的I/O系统可能只能到1M以后出现更快的磁盘系统后,可能会加大这个值)
这个值是这样的,因为sstiomax是1M.假如block是8K,那么MBRC最大只能为128.
例子:
SQL> show parameters db_file_multiblock_read_count
NAME TYPE VALUE
------------------------------------ ----------- -------
db_file_multiblock_read_count integer 16
SQL> ALTER SESSION SET db_file_multiblock_read_count =256;
Session altered.
SQL> SELECT VALUE FROM v$parameter WHERE NAME = 'db_file_multiblock_read_count';
VALUE
--------------------
128

还有MBRC还必须同时小于DB_BLOCK_BUFFERS/4
注明DB_BLOCK_BUFFERS是oracle9i以前版本的参数,9i以后用db_cache_size
10g以后如果设置了sga_target,那db_cache_size就是0.

这里要去查一下sga_target有什么作用,最好看看10g新特性,9i里db_cache_size是非0值

MBRC也受限于OS的设置,比如Solaris的maxphys 与maxconf.

当然这里有设置MBRC的shortcut,你可以设MBRC到奇高的值,高到你认为不合逻辑都可以.然后让oracle来告诉你系统可以支持到什么值,你然后简单的运行一个可以full table sacn的SQL,检查V$SESSION_WAIT视图
db file scattered read的P3值就是你的系统中的MBRC最大值.你也可以通过10046事件去达到同样目的.

WAIT #1: nam='db file scattered read' ela= 17946 p1=6 p2=56617 p3=128
WAIT #1: nam='db file scattered read' ela= 21055 p1=6 p2=56745 p3=128
WAIT #1: nam='db file scattered read' ela= 17628 p1=6 p2=56873 p3=128
WAIT #1: nam='db file scattered read' ela= 29881 p1=6 p2=57001 p3=128
WAIT #1: nam='db file scattered read' ela= 33220 p1=6 p2=57129 p3=128
WAIT #1: nam='db file scattered read' ela= 33986 p1=6 p2=57257 p3=96
WAIT #1: nam='db file scattered read' ela= 46372 p1=6 p2=65577 p3=128
WAIT #1: nam='db file scattered read' ela= 33770 p1=6 p2=65705 p3=128
WAIT #1: nam='db file scattered read' ela= 41750 p1=6 p2=65833 p3=128
WAIT #1: nam='db file scattered read' ela= 34914 p1=6 p2=65961 p3=128
WAIT #1: nam='db file scattered read' ela= 33326 p1=6 p2=66089 p3=128

(补充：
正常运行的库，MBRC 并非越大越好(除了IO效率有降低的可能，也会有可能影响CBO的运行)。
在 10gR2 上，'db_file_multiblock_read_count' 参数引入了两个相关的隐含参数_db_file_exec_read_count
_db_file_optimizer_read_count）

Why Physical I/Os Are Expensive

无论什么时候当DBA听到物理I/O的高成本时,他们第一想法就是I/O子系统太慢.
是的,I/O子系统是最慢的组件,但这只是原因的一半,另一半就是oracle数据块被读到SGA中

Whenever most DBAs hear that physical I/Os are costly, they immediately train their thoughts toward the physical disks and I/O subsystem. Yes, the storage layer is the slowest component, but that is only half of the story. The other half is about the stuff that goes on inside Oracle when blocks are being read into the SGA.

There are numerous operations that have to take place. For brevity’s sake, among them, the foreground process must first scan the free buffer list. If a free buffer is not found when the maximum scan limit is reached, the foreground process posts the DBWR process to make free buffers. Then the foreground process has to retry for the free buffer. Once it finds a free buffer, it unlinks it from the free lists chain and relinks the buffer in at the top of the LRU (Least Recently Used) or the midpoint insertion of the LRU (depending on the version). Then the pointers for the buffer header must be adjusted accordingly. There are at least two sets of pointers, and each change requires a latch get. The header structure of the block must also be initialized and updated. The bits in the block header must also be set and changed during the allocation of the buffer, the read of the block into the buffer, and the completion of a read in order to prevent other processes from using the block while it is influx.

Therefore the best way to combat thedb file sequential readanddb file scatteredread waits is to reduce the demand for both the logical and physical I/Os. This can be best achieved through application and SQL tuning. Now that you have been informed how expensive physical I/Os are, you should also know that logical I/Os are not that cheap either. We will discuss this inChapter 6.(最后一段暂不译,读完
latch free再译)