根據(jù)statspack報(bào)表優(yōu)化oracle數(shù)據(jù)庫(kù)實(shí)例之“DB file sequential read”

根據(jù)statspack報(bào)表優(yōu)化oracle數(shù)據(jù)庫(kù)實(shí)例之“DB file sequential read”

oracle的等待事件是衡量oracle運(yùn)行狀況的重要依據(jù)及指標(biāo)。

等待事件的概念是在oracle7.0.1.2中引入的，大致有100個(gè)等待事件。在oracle8.0中這個(gè)數(shù)目增加到了大約150個(gè)，在oracle8i中大約有200個(gè)事件，到oracle9i時(shí)，等待事件增加到360個(gè)。

Oracle的等待事件主要有兩種類(lèi)型，即空閑（idle）等待事件和非空閑（non-idle）等待事件?？臻e事件指oracle正在等待某種工作，在診斷和優(yōu)化數(shù)據(jù)庫(kù)的時(shí)候，我們不用過(guò)多注意這部分事件。

非空閑等待事件專(zhuān)門(mén)針對(duì)oracle的活動(dòng)，指數(shù)據(jù)庫(kù)任務(wù)或應(yīng)用運(yùn)行過(guò)程中發(fā)生的等待，這些等待事件是我們?cè)谡{(diào)整數(shù)據(jù)庫(kù)的時(shí)候應(yīng)該關(guān)注與研究的。

常見(jiàn)的非空閑等待事件有：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的產(chǎn)生

本文主要解釋了db file sequence read文件分散讀取等待事件產(chǎn)生的原因與優(yōu)化的方法。

db file sequential read等待時(shí)間是由于執(zhí)行對(duì)索引，回滾（undo）段，和表（當(dāng)借助rowid來(lái)訪問(wèn)），控制文件和數(shù)據(jù)文件頭的單塊讀操作SQL語(yǔ)句（用戶和遞歸）引起的。

對(duì)于這些對(duì)象的物理I/O請(qǐng)求是很正常的，因此db file sequential read等待的存在不是一定意味庫(kù)或應(yīng)用出錯(cuò)了。如果會(huì)話在這事件上花了好長(zhǎng)事件，它可能也不是一個(gè)糟糕的事情。相反，如果會(huì)話花了大量時(shí)間在equeue或latch free上，那么一定是有問(wèn)題。這兒?jiǎn)螇K讀變的復(fù)雜了。

如果這個(gè)等待事件比較顯著，可能表示在多表連接中，表的連接順序存在問(wèn)題，可能沒(méi)有正確的使用驅(qū)動(dòng)表；或者可能說(shuō)明不加選擇地進(jìn)行索引。

??? 在大多數(shù)情況下我們說(shuō)，通過(guò)索引可以更為快速的獲取記錄，所以對(duì)于一個(gè)編碼規(guī)范、調(diào)整良好的數(shù)據(jù)庫(kù)，這個(gè)等待很大是正常的。

??? 但在很多情況下，使用索引并不總是最佳選擇，比如讀取較大表中大量的數(shù)據(jù)，全表掃描可能會(huì)明顯快于索引掃描，所以在開(kāi)發(fā)中我們就應(yīng)該注意，對(duì)于這樣的查詢應(yīng)該進(jìn)行避免使用索引掃描。

對(duì)于db file sequential read中,p1指數(shù)據(jù)文件ID,p2指block#號(hào),p3指讀取的block數(shù)量,這個(gè)事件一般不可避免，大多由于ＳＱＬ使用索引不當(dāng)，造成從磁盤(pán)上讀取連續(xù)的數(shù)據(jù)，接近于全表掃描.可以通過(guò)db_file_multiblock_read_count參數(shù)來(lái)調(diào)整每次讀取的block數(shù),減少I(mǎi)O.

Statspack的報(bào)表

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等待時(shí)間占總等待時(shí)間的比例

??? 動(dòng)態(tài)性能視圖v$session_event中存儲(chǔ)了系統(tǒng)庫(kù)緩沖池中存儲(chǔ)的sql語(yǔ)句的所有等待事件的時(shí)間。關(guān)聯(lián)v$session可以獲得當(dāng)前連接的等待時(shí)間和該時(shí)間所占總等待時(shí)間的比例。

??? 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

??????????????????? '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

―――――――――――――――――――――――――――――

獲取等待事件單塊讀等待的對(duì)象名和分區(qū)名

??? 動(dòng)態(tài)性能視圖v$SESSION_WAIT中的p1，和p2兩個(gè)參數(shù)得到對(duì)象名和分區(qū)名。使用v$bh的缺點(diǎn)是你必須等待塊被讀入到buffer cache中，否則x$bh視圖在buffer中沒(méi)有p1，p2參數(shù)所指的信息。Dba_objects視圖也不包含P1和P2所指的rollback和undo段對(duì)象。

?? 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的語(yǔ)句

??? 我們可以通過(guò)如下兩種方式來(lái)最小化db file sequential read事件：降低physical和logical read；降低平均等待時(shí)間。

??? 由于當(dāng)前正在運(yùn)行的sql可能也會(huì)導(dǎo)致wait，所以，沒(méi)有歷史數(shù)據(jù)的交互式診斷經(jīng)常無(wú)法找出準(zhǔn)確的等待事件和sql語(yǔ)句。DBA可以查詢v$sql視圖來(lái)查找有高平均disk_reads的語(yǔ)句。

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

針對(duì)索引的sequential read解決方案

??? 使用上面的方式得到disk read較多的語(yǔ)句后，如果該語(yǔ)句的執(zhí)行計(jì)劃是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語(yǔ)句的輸出結(jié)果中，如果dba_indexes.clustering_factor接近表中塊的數(shù)量，那么表中大多數(shù)行是排序的。這是期望的，然而，如果clustering factor接近表中行的數(shù)量，它意味著表中的行是隨機(jī)排列，這種情況對(duì)于同樣葉塊中的索引塊來(lái)說(shuō)，指向同樣的數(shù)據(jù)塊中的行是不可能的，因此它會(huì)導(dǎo)致更多的I/O來(lái)完成操作。你可以采取rebuilding表來(lái)改善索引clustering factor，為了行根據(jù)索引鍵來(lái)排序，其后重建索引。

?

?

?

?

?

?