db file sequential read 事件的优化（一）
db file sequential read等待事件有3个参数：file＃，first block＃，和block数量。在10g中，这等待事件受到用户I/O等待级别的影响。当处理db file sequential read等待事件的时候，牢记以下关键想法。
l Oracle进程需要一个当前不在SGA中的块，等待数据库块从磁盘读入到SGA中
l 要看的两个重要的数字是单独会话的TIME_WAITED和AVERAGE_WAIT。
l 重要db file sequential read等待时间最可能是一个应用问题。
db file sequential read等待时间是由于执行对索引，回滚（undo）段，和表（当借助rowid来访问），控制文件和数据文件头的单块读操作SQL语句（用户和递归）引起的。
对于这些对象的物理I/O请求是很正常的，因此db file sequential read等待的存在不是一定意味库或应用出错了。如果会话在这事件上花了好长事件，它可能也不是一个糟糕的事情。相反，如果会话花了大量时间在equeue或latch free上，那么一定是有问题。这儿单块读变的复杂了。
==========
目的：从得到各个session中db file sequential read等待事件的总的等待时间，和等待时间所占总的等待时间(各种等待事件的总和时间)的比例中分析哪一个sid更高，更重要。
==========
select a.sid,
a.event,
a.time_waited,
a.time_waited / c.sum_time_waited * 100 pct_wait_time,
round((sysdate - b.logon_time) * 24) hours_connected
from v$session_event a, v$session b,
(select sid, sum(time_waited) sum_time_waited
from v$session_event
where event not in (
'Null event',
'client message',
'KXFX: Execution Message Dequeue - Slave',
'PX Deq: Execution Msg',
'KXFQ: kxfqdeq - normal deqeue',
'PX Deq: Table Q Normal',
'Wait for credit - send blocked',
'PX Deq Credit: send blkd',
'Wait for credit - need buffer to send',
'PX Deq Credit: need buffer',
'Wait for credit - free buffer',
'PX Deq Credit: free buffer',
'parallel query dequeue wait',
'PX Deque wait',
'Parallel Query Idle Wait - Slaves',
'PX Idle Wait',
'slave wait',
'dispatcher timer',
'virtual circuit status',
'pipe get',
'rdbms ipc message',
'rdbms ipc reply',
'pmon timer',
'smon timer',
'PL/SQL lock timer',
'SQL*Net message from client',
'WMON goes to sleep')
having sum(time_waited) > 0 group by sid) c
where a.sid = b.sid
and a.sid = c.sid
and a.time_waited > 0
and a.event = 'db file sequential read'
order by hours_connected desc, pct_wait_time;
SID EVENT TIME_WAITED PCT_WAIT_TIME HOURS_CONNECTED
---- ----------------------- ----------- ------------- ---------------
186 db file sequential read 64446 77.0267848 105
284 db file sequential read 1458405 90.992838 105
194 db file sequential read 1458708 91.0204316 105
322 db file sequential read 1462557 91.1577045 105
139 db file sequential read 211325 52.6281055 11
256 db file sequential read 247236 58.0469755 11
?192 db file sequential read 243113 88.0193625 2
你能做两件事来最小化db file sequential read事件：
l 通过降低physical和logical read来优化导致大多数wait的SQL语句
l 降低平均等待时间
此外，当前正运行的SQL语句可能或不可能是导致wait的。这就是没有历史数据的交互式诊断经常是无功而返的原因。你能查询v$sql视图来查找有高平均DISK_READS的语句，但然后你怎样才能判断他们属于会话？因为这些限制，你可能必须确定和下次明确跟踪会话的SQL语句。一旦你已经找到，优化目标就将降低物理和逻辑读的数量。
注意：除了DISK_READS字段外，oracle10g中的V$SQL和V$SQLAREA视图有不错的新字段：USER_IO_WAIT_TIME，DIRECT_WRITES，APPLICATION_WAIT_TIME，CONCURRENCY_WAIT_TIME，CLUSTER_WAIT_TIME，PLSQL_EXEC_TIME和JAVA_EXEC_TIME。你能找到有最高的累计或平均的USER_IO_WAIT_TIME的sql语句。
=======
目的：根据db file sequential read中的P1,P2两个参数得到对象名和分区名（该等待事件单块读等待的对象名和分区名），使用v$bh的缺点你必须等待块被读入到buffer cache中；否则X$BH视图在buffer中没有P1,P2参数所指的信息。DBA_OBJECTS视图也不包含P1和P2所指的rollback或undo段对象：
======
select b.sid,
nvl(substr(a.object_name,1,30),
'P1='||b.p1||' P2='||b.p2||' P3='||b.p3) object_name,
a.subobject_name,
a.object_type
from dba_objects a, v$session_wait b, x$bh c
where c.obj = a.object_id(+)
and b.p1 = c.file#(+)
and b.p2 = c.dbablk(+)
and b.event = 'db file sequential read'
union
select b.sid,
nvl(substr(a.object_name,1,30),
'P1='||b.p1||' P2='||b.p2||' P3='||b.p3) object_name,
a.subobject_name,
a.object_type
from dba_objects a, v$session_wait b, x$bh c
where c.obj = a.data_object_id(+)
and b.p1 = c.file#(+)
and b.p2 = c.dbablk(+)
and b.event = 'db file sequential read'
order by 1;
SID OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE
----- ------------------------- ------------------------- -----------------
12 DVC_TRX_REPOS DVC_TRX_REPOS_PR64 TABLE PARTITION
128 DVC_TRX_REPOS DVC_TRX_REPOS_PR61 TABLE PARTITION
154 ERROR_QUEUE ERROR_QUEUE_PR1 TABLE PARTITION
192 DVC_TRX_REPOS_1IX DVC_TRX_REPOS_20040416 INDEX PARTITION
194 P1=22 P2=30801 P3=1
322 P1=274 P2=142805 P3=1
336 HOLD_Q1_LIST_PK INDEX
针对索引的sequential reads解决方案：
1.SQL调优，得到sql语句
select a.sid, a.seq#, a.event, a.p1text, a.p1, a.p1raw, a.p2text, a.p2, a.p2raw, a.p3text, a.p3, a.p3raw, a.wait_time, a.seconds_in_wait, a.state, b.serial#, b.username, b.osuser, b.paddr, b.logon_time, b.process, b.sql_hash_value, b.saddr, b.module, b.row_wait_obj#, b.row_wait_file#, b.row_wait_block#, b.row_wait_row# from v$session_wait a, v$session b where a.sid = b.sid and b.username is not null and b.type <> 'BACKGROUND' and a.event in ( 'db file sequential read', 'db file scattered read', 'latch free', 'direct path read', 'direct path write', 'enqueue', 'library cache pin', 'library cache load lock', 'buffer busy waits', 'free buffer waits');
select hash_value,address,piece,sql_text
from v$sqltext
where hash_value = <cursor hash value>
order by piece;
2.如果执行计划是table access by index rowid，检查索引的clustering factor也是值得做的。
select id.index_name,tb.table_name,id.clustering_factor,tb.num_rows,tb.blocks from dba_indexes id,dba_tables tb where id.table_name=tb.table_name and tb.table_name='&1' and tb.owner='&2';
如果DBA_INDEXES.CLUSTERING_FACTOR接近表中块的数量，那么表中大多数行是排序的。这是期望的。然而，如果clustering factor接近表中行的数量，它意味着表中行是随机排列。这种情况，对于在同样叶块中的索引块来说，指向同样数据块中行是不可能的，因此它要求更多I/Os来完成这操作。你可以采取rebuilding表来改善索引clustering fator，为了行根据索引键来被排序，其后重建索引。如果表不只有一个索引，什么会发生？好，它会下降。你仅能迎合最多使用的索引
3.也检查来看是否应用有最近已经引入一个新的索引，通过以下查询。新索引的引入可能导致优化器为访问表的SQL语句选择一个不同的执行计划。新计划可能产生一个比旧计划更好的，中性的，或糟糕的性能。
select owner,
substr(object_name,1,30) object_name,
object_type,
created
from dba_objects
where object_type in ('INDEX','INDEX PARTITION')
order by created;
OPTIMIZER_INDEX_COST_ADJ和OPTIMIZER_INDEX_CACHING初始化参数能影响优化器去采用nested loops操作和在全表扫描上选择一个索引访问路径。OPTIMIZER_INDEX_COST_ADJ参数默认是100。较低的值哄骗优化器认为索引访问路径更便宜。OPTIMIZER_INDEX_CACHING参数默认值是0。较高的值通知优化器一个更高的百分比索引块已经在buffer cache中，nested loops操作更便宜。一些第三方的应用使用这方法来改善索引使用。这些参数的不合适的使用能导致重大的I/O等待时间。查明会话正以什么值运行。直到9i数据库，这信息仅能通过跟踪以trace event 10053的级别1的会话，并检查trace文件。在oracle10g中，这可以查询v$ses_optimizer_env视图。
确保所有对象的统计数据是当前数据的典型，因为不准确的统计数据的确会导致优化器生成糟糕的不该用索引读却调用索引读的执行计划。记住，统计数据需要是有代表性的，而不必最新的，并且执行计划可能在统计数据被收集的每一次而改变。
注意：当使用一个低estimate比例值分析表或索引的时候，oracle正常情况使用单个块读，这将增加该会话的db file sequential read统计数据（v$session_event）和实例（v$system_event）。
针对表（table access by index rowid）的sequential reads
你可以看db file sequential read等待事件，通过P1,P2参数得到是表而不是索引。对于SQL语句来说通过从索引获得的rowid访问表是正常的，如下面解释计划显示，当通过rowid来读一个表的时候，oracle使用一个单独块I/O：
LVL OPERATION OBJECT
--- --------------------------------- ---------------------
1 SELECT STATEMENT
2 TABLE ACCESS BY INDEX ROWID RESOURCE_ASGN_SNP
3 INDEX RANGE SCAN RESOURCE_ASGN_SNP_4IX
db file sequential read 事件的优化（二）
根据statspack报表优化oracle数据库实例之“DB file sequential read”
oracle的等待事件是衡量oracle运行状况的重要依据及指标。
等待事件的概念是在oracle7.0.1.2中引入的，大致有100个等待事件。在oracle8.0中这个数目增加到了大约150个，在oracle8i中大约有200个事件，到oracle9i时，等待事件增加到360个。
Oracle的等待事件主要有两种类型，即空闲（idle）等待事件和非空闲（non-idle）等待事件。空闲事件指oracle正在等待某种工作，在诊断和优化数据库的时候，我们不用过多注意这部分事件。
非空闲等待事件专门针对oracle的活动，指数据库任务或应用运行过程中发生的等待，这些等待事件是我们在调整数据库的时候应该关注与研究的。
常见的非空闲等待事件有：db file scattered read; db file sequential read; buffer busy waits; free buffer waits; enqueue; latch free; log file parallel write; log file sync.
Db file sequential read的产生
本文主要解释了db file sequence read文件分散读取等待事件产生的原因与优化的方法。
db file sequential read等待时间是由于执行对索引，回滚（undo）段，和表（当借助rowid来访问），控制文件和数据文件头的单块读操作SQL语句（用户和递归）引起的。
对于这些对象的物理I/O请求是很正常的，因此db file sequential read等待的存在不是一定意味库或应用出错了。如果会话在这事件上花了好长事件，它可能也不是一个糟糕的事情。相反，如果会话花了大量时间在equeue或latch free上，那么一定是有问题。这儿单块读变的复杂了。
如果这个等待事件比较显著，可能表示在多表连接中，表的连接顺序存在问题，可能没有正确的使用驱动表；或者可能说明不加选择地进行索引。
在大多数情况下我们说，通过索引可以更为快速的获取记录，所以对于一个编码规范、调整良好的数据库，这个等待很大是正常的。
但在很多情况下，使用索引并不总是最佳选择，比如读取较大表中大量的数据，全表扫描可能会明显快于索引扫描，所以在开发中我们就应该注意，对于这样的查询应该进行避免使用索引扫描。
对于db file sequential read中,p1指数据文件ID,p2指block#号,p3指读取的block数量,这个事件一般不可避免，大多由于ＳＱＬ使用索引不当，造成从磁盘上读取连续的数据，接近于全表扫描.可以通过db_file_multiblock_read_count参数来调整每次读取的block数,减少IO.
Statspack的报表
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 246 50.79
db file sequential read 98,012 208 43.01
db file scattered read 1,001 11 2.20
direct path write 2,171 7 1.52
control file parallel write 1,404 3 .56
―――――――――
获得db file sequential read等待时间占总等待时间的比例
动态性能视图v$session_event中存储了系统库缓冲池中存储的sql语句的所有等待事件的时间。关联v$session可以获得当前连接的等待时间和该时间所占总等待时间的比例。
select a.sid,
a.event,
a.time_waited,
a.time_waited / c.sum_time_waited * 100 pct_wait_time,
round((sysdate - b.logon_time) * 24) hours_connected
from v$session_event a, v$session b,
(select sid, sum(time_waited) sum_time_waited
from v$session_event
where event not in (
'Null event',
'client message',
'KXFX: Execution Message Dequeue - Slave',
'PX Deq: Execution Msg',
'KXFQ: kxfqdeq - normal deqeue',
'PX Deq: Table Q Normal',
'Wait for credit - send blocked',
'PX Deq Credit: send blkd',
'Wait for credit - need buffer to send',
'PX Deq Credit: need buffer',
'Wait for credit - free buffer',
'PX Deq Credit: free buffer',
'parallel query dequeue wait',
'PX Deque wait',
'Parallel Query Idle Wait - Slaves',
'PX Idle Wait',
'slave wait',
'dispatcher timer',
'virtual circuit status',
'pipe get',
'rdbms ipc message',
'rdbms ipc reply',
'pmon timer',
'smon timer',
'PL/SQL lock timer',
'SQL*Net message from client',
'WMON goes to sleep')
having sum(time_waited) > 0 group by sid) c
where a.sid = b.sid
and a.sid = c.sid
and a.time_waited > 0
and a.event = 'db file sequential read'
order by hours_connected desc, pct_wait_time;
―――――――――――――――――――――――――――――
98 db file sequential read 1 100 0
94 db file sequential read 298 100 26
95 db file sequential read 1 100 0
92 db file sequential read 5 100 0
20 db file sequential read 19998 94.6695701571672 482
89 db file sequential read 26641 93.6414762741652 482
86 db file sequential read 3866 92.9997594419052 164
60 db file sequential read 21699 92.6199419498037 385
25 db file sequential read 15 88.2352941176471 381
105 db file sequential read 17630 88.2294064658192 481
39 db file sequential read 13782 84.2111694977392 482
54 db file sequential read 294095 82.978999551381 143
11 db file sequential read 11874 82.4297119055883 481
58 db file sequential read 889 81.4848762603116 24
61 db file sequential read 7436 80.7470952329243 482
48 db file sequential read 231 76.2376237623762 455
52 db file sequential read 12 75 471
47 db file sequential read 3101 70.2219202898551 385
31 db file sequential read 2749 64.5911654135338 385
66 db file sequential read 78 63.4146341463415 138
50 db file sequential read 9001 62.3079053025059 210
69 db file sequential read 12505 61.5767185345677 210
40 db file sequential read 55 60.4395604395604 138
91 db file sequential read 80 56.3380281690141 2
14 db file sequential read 199 54.2234332425068 471
99 db file sequential read 59 54.1284403669725 138
82 db file sequential read 67 53.6 138
35 db file sequential read 85 50.8982035928144 138
83 db file sequential read 192 49.6124031007752 471
59 db file sequential read 25811 47.9758364312268 457
9 db file sequential read 5858 47.5024326954265 210
43 db file sequential read 63 47.3684210526316 138
21 db file sequential read 71 46.7105263157895 138
49 db file sequential read 36 43.9024390243902 138
36 db file sequential read 98 43.1718061674009 40
8 db file sequential read 180 38.7096774193548 471
97 db file sequential read 35 35.3535353535354 138
100 db file sequential read 409 32.9307568438003 483
76 db file sequential read 348 32.6148078725398 483
22 db file sequential read 26 29.8850574712644 116
64 db file sequential read 314 28.8602941176471 483
72 db file sequential read 313 27.4561403508772 483
79 db file sequential read 270 27.1084337349398 483
75 db file sequential read 238 26.5033407572383 482
41 db file sequential read 293 26.3489208633094 483
63 db file sequential read 245 25.7082896117524 483
65 db file sequential read 351 25.0178189593728 482
30 db file sequential read 189 24.1687979539642 143
44 db file sequential read 21 24.1379310344828 116
57 db file sequential read 17 23.943661971831 2
24 db file sequential read 275 22.3395613322502 482
26 db file sequential read 308 20.0782268578879 482
62 db file sequential read 203 19.7663096397274 483
19 db file sequential read 297 19.5910290237467 482
90 db file sequential read 251 16.3517915309446 482
71 db file sequential read 397 15.8736505397841 482
55 db file sequential read 218 14.8907103825137 482
33 db file sequential read 407 14.7785039941903 482
74 db file sequential read 249 14.5359019264448 482
80 db file sequential read 265 14.3243243243243 482
77 db file sequential read 251 14.2532651902328 482
5 db file sequential read 7734 12.6872159976378 1941
56 db file sequential read 238 12.2997416020672 482
84 db file sequential read 22 11.8279569892473 2
96 db file sequential read 285 11.552492906364 482
38 db file sequential read 236 9.33544303797468 482
7 db file sequential read 65 8.38709677419355 1941
29 db file sequential read 193 7.81376518218623 482
12 db file sequential read 1 7.14285714285714 0
42 db file sequential read 229 7.03533026113671 482
37 db file sequential read 303 5.47623350804265 482
6 db file sequential read 2931 4.48796472101427 1941
87 db file sequential read 292 1.64247946900664 483
28 db file sequential read 197 1.44047967241884 482
32 db file sequential read 302 0.889674473412874 482
17 db file sequential read 1 0.606060606060606 0
45 db file sequential read 41 0.108110958759625 138
―――――――――――――――――――――――――――――
获取等待事件单块读等待的对象名和分区名
动态性能视图v$SESSION_WAIT中的p1，和p2两个参数得到对象名和分区名。使用v$bh的缺点是你必须等待块被读入到buffer cache中，否则x$bh视图在buffer中没有p1，p2参数所指的信息。Dba_objects视图也不包含P1和P2所指的rollback和undo段对象。
SELECT b.Sid,
Nvl(Substr(a.Object_Name, 1, 30),
'P1=' || b.P1 || ' P2=' || b.P2 || ' P3=' || b.P3) Object_Name,
a.Subobject_Name,
a.Object_Type
FROM Dba_Objects a,
V$session_Wait b,
Sys.X$bh c
WHERE c.Obj = a.Object_Id(+)
AND b.P1 = c.File#(+)
AND b.P2 = c.Dbablk(+)
AND b.Event = 'db file sequential read'
AND b.sid = 12
UNION
SELECT b.Sid,
Nvl(Substr(a.Object_Name, 1, 30),
'P1=' || b.P1 || ' P2=' || b.P2 || ' P3=' || b.P3) Object_Name,
a.Subobject_Name,
a.Object_Type
FROM Dba_Objects a,
V$session_Wait b,
X$bh c
WHERE c.Obj = a.Data_Object_Id(+)
AND b.P1 = c.File#(+)
AND b.P2 = c.Dbablk(+)
AND b.Event = 'db file sequential read'
AND b.sid = 12
ORDER BY 1;
查找具有高disk read的语句
我们可以通过如下两种方式来最小化db file sequential read事件：降低physical和logical read；降低平均等待时间。
由于当前正在运行的sql可能也会导致wait，所以，没有历史数据的交互式诊断经常无法找出准确的等待事件和sql语句。DBA可以查询v$sql视图来查找有高平均disk_reads的语句。
select disk_reads,HASH_VALUE
from (select HASH_VALUE,disk_reads,
dense_rank() over
(order by disk_reads desc) disk_reads_rank
from v$sql s)
where disk_reads_rank <=100
ORDER BY disk_reads_rank ;
SELECT * FROM v$sqltext sl
WHERE sl.HASH_VALUE = 384909134
ORDER BY piece
针对索引的sequential read解决方案
使用上面的方式得到disk read较多的语句后，如果该语句的执行计划是table access by index rowed,检查索引的clustering factor是非常必要的。
select id.index_name,tb.table_name,id.clustering_factor,tb.num_rows,tb.blocks from dba_indexes id,dba_tables tb where id.table_name=tb.table_name and tb.table_name='&1' and tb.owner='&2'
在上述sql语句的输出结果中，如果dba_indexes.clustering_factor接近表中块的数量，那么表中大多数行是排序的。这是期望的，然而，如果clustering factor接近表中行的数量，它意味着表中的行是随机排列，这种情况对于同样叶块中的索引块来说，指向同样的数据块中的行是不可能的，因此它会导致更多的I/O来完成操作。你可以采取rebuilding表来改善索引clustering factor，为了行根据索引键来排序，其后重建索引。