db file sequential read 事件的优化（一）

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中,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，为了行根据索引键来排序，其后重建索引。

posted @ 2013-05-03 00:16 wilson.han 阅读(3536) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

wilson.han

db file sequential read 事件的优化（一）

公告