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

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

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

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

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

非空閑等待事件專門針對oracle的活動，指數(shù)據(jù)庫任務(wù)或應(yīng)用運(yùn)行過程中發(fā)生的等待，這些等待事件是我們在調(diào)整數(shù)據(jù)庫的時候應(yīng)該關(guā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等待時間是由于執(zhí)行對索引，回滾（undo）段，和表（當(dāng)借助rowid來訪問），控制文件和數(shù)據(jù)文件頭的單塊讀操作SQL語句（用戶和遞歸）引起的。

對于這些對象的物理I/O請求是很正常的，因此db file sequential read等待的存在不是一定意味庫或應(yīng)用出錯了。如果會話在這事件上花了好長事件，它可能也不是一個糟糕的事情。相反，如果會話花了大量時間在equeue或latch free上，那么一定是有問題。這兒單塊讀變的復(fù)雜了。

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

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

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

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

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

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

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

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

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

??? 動態(tài)性能視圖v$SESSION_WAIT中的p1，和p2兩個參數(shù)得到對象名和分區(qū)名。使用v$bh的缺點(diǎn)是你必須等待塊被讀入到buffer cache中，否則x$bh視圖在buffer中沒有p1，p2參數(shù)所指的信息。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；降低平均等待時間。

??? 由于當(dāng)前正在運(yùn)行的sql可能也會導(dǎo)致wait，所以，沒有歷史數(shù)據(jù)的交互式診斷經(jīng)常無法找出準(zhǔn)確的等待事件和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較多的語句后，如果該語句的執(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語句的輸出結(jié)果中，如果dba_indexes.clustering_factor接近表中塊的數(shù)量，那么表中大多數(shù)行是排序的。這是期望的，然而，如果clustering factor接近表中行的數(shù)量，它意味著表中的行是隨機(jī)排列，這種情況對于同樣葉塊中的索引塊來說，指向同樣的數(shù)據(jù)塊中的行是不可能的，因此它會導(dǎo)致更多的I/O來完成操作。你可以采取rebuilding表來改善索引clustering factor，為了行根據(jù)索引鍵來排序，其后重建索引。

?

?

?

?

?

?