oracle db file sequential read,詳解 db file sequential read 等待事件

db file sequential read (本文由翻譯,轉載請注明出處)

db file sequential read 事件有三個參數:file#,first block#, block count, 在oracle 10g裡,此等待事件在歸于 User I/O wait class 下面的. 處理db file sequential read 事件要牢牢把握下面三個主要思想:

1)oracle 程序需要通路的block不能從SGA 中擷取,是以oracle 程序會等待block從I/O讀到SGA

2)兩個重要參數TIME_WAITED,AVERAGE_WAIT,是以單個session擷取的

3)影響較大的db file sequential read 一般很像應用程式問題

Common Causes, Diagnosis, and Actions

db file sequential read 等待事件被SQL 語句初始化,主要從index,rollback(or undo) segments, tables(通過rowid通路表),control files 和data file headers中進行single-block read.

通路資料對象(table,index)總是會産生Physical I/o需求,當出現db file sequential read等待事件時,并不意味着資料庫産生系統問題,基至它大量出現都不是一件壞事.真正要引起注意的是像enqueue 和latch free等待事件,它們總是引起系統性題的根源.并且它們使single-block(單塊讀取)變得因難了.

那麼什麼情況下, 當出現db file sequential read等待事件,才可以視為性能問題呢?

什麼情況下,db file sequential read可以視為系統的超額負擔,并且基準線應該怎樣去定義?

這是一個比較複雜的問題.在沒有工業标準指引的情況下.我們要依據資料庫運作環境來制定标準線.

比如,我們定義超過多少時間的db file sequential read等待事件,可以視為性能問題,還可以用最原始的方法,那就是等待使用者抱怨.

在V$SESSION_EVENT視圖中,db file sequential read的高TIME_WAITED是較為容易發現的,當時因為V$SESSION_EVENT是記錄從執行個體啟動以來的資料,是以我們同以前的TIME_WAITED進行比較,當然跟同一個session,同一個LOGON_TIME的非空閑事件進行比較是可以的,也是比較準确的.當執行個體不間斷運作很長一段時間(數天或數星期)之後,TIME_WAITED的累計值就會很高,這當然不能說是性能問題.

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;

(本條語句來源于OWI 材料,但是本SQL語句計算的結果是不精确的,因為session sid是時時改變的)

減少db file sequential read 等待事件,我們可以從兩方面入手:

1)第一條當然是優化SQL語句,以減少實體讀和邏輯讀

2)第二條是從統計上減少平均等待時間(比如優化最高wait_time的等待事件)

備注:特别是給客戶看結果時效果最明顯,因為圖形給人的感觀是比較明顯的

相對每一條來說,除非用你10046事件或自己做一個不間斷等待事件程式,不然是非常難以鎖定哪一條SQL引起長時間的wait_time.退一步講,目前的SQL也不一定就是引起wait_time的原因.是以我們發現要解決等待事件的問題沒有曆史資料是很困難的.

你也可以通過查詢V$SQL視圖擷取平均DISK_READS,當然我們不能就認為此SQL就屬于某個SESSION,是以下次對session進行trace,一般可以定位SQL,然後優化SQL以減少實體讀與邏輯讀.

備注:除了DISK_READS之外,oracle 10g為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是可能的,但V$SQL和V$SQLAREA兩個視圖的通路速度是較慢的.

另外可以減少db file sequential read等待事件影響的方法是減少AVERAGE_WAIT ,AVERAGE_WAIT列是一個session等待single block被從硬碟擷取的平均等待時間(英文好讀,中文有點扭,主要我的水準不夠)

This is the average time a session has to wait for a single block fetch from disk(英文原句).AVERAGE_TIME是V$SESSION_EVENT視圖中的列.在高速的存儲系統中,平均的single-block讀不能夠超過10ms(milliseconds,千分之一秒) 或1cs(centiseconds,百分之一秒).一般的情況下,SAN(storage area network,網絡存儲)的AVERAGE_TIME平均等待事間在4至8ms之間,因為SAN的cache都較大.

AVERAGE_TIME的值越大,single-block讀的系統資源開耗也随之增大,也即程序的響應時間會受到影響.

從另外一個方面來講,較低的AVERAGE_TIME值反應程序等待single-block讀的時間會較短.當然

AVERAGE_TIME調優的優先級遠沒有SQL優化的優先級高,因為優化一個占用大量資源的SQL的效果是非常明顯和有效的.

需要注意的db file sequential read 并不總是對index對像進行資源占用,有時也會對table/partition對像進行資源占用.是以我們需要将P1/P2參數的值進行轉換,在此我們會用到視圖DBA_EXTENTS以擷取對像名.

但是DBA_EXTENTS是一個複雜的,響應極慢的視圖.要想用快一點的方法,X$和DBA_OBJECTS将是一個更好的選擇.因為X$BH不占用BUFFER_CACHE是以,通路X$BH會有I/O産生,還有就是DBA-OBJECTS視圖不包括rollback 和undo 段,是以如果db file sequential read通路這兩個對象,也是不能被解析的.

查詢的例子:

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

像本例中的object_type,如果是table,要進SQL進行相應的優化.

Sequential Reads Against Indexes

db file sequential read 主要的問題不是對index的通路,而且超額的對錯誤index的通路.當系統的

通路路徑發生更改時,可能對效能慢的index進行通路,進而産生等待.當然如果一個SQL執行了大量的index讀

這也可能是一個性能問題.是以分析SQL的執行計劃是一個比較好的方法,當要用FULL TABLE SCAN時,用index

就會産生性能問題.還有就是FIRST_ROWS 和ALL_ROWS的問題,當然從大的方面講OLTP與DSS的混用也會産生不

合時适的db file sequential read.還有關于驅動表(driving table)的問題.不對的驅動表,性能也不會好.

記住,所有的努力的目的應該是一樣的,那就是降低logical and physical I/Os

下面有個種方法:

1)分析SQL,弄清SQL的邏輯,看看SQL到底想擷取什麼,然後優化,甚至重寫

2)将index放在快磁盤上,尤其不要放在RAID-5上,因為慢磁盤導緻高average time,然而I/O優化的優先級

不可以高于SQL CODE的優化.因為SQL有問題再快的磁盤的也不行,最好用OUTLINE穩固執計計劃,尤其是第三方軟體

3)關于index表,最好将資料進行排列,以減少I/O.可以通過DBA_INDEXS.CLUSTERING_FACTOR來檢視index有沒有達到

表的所有塊的數量,如有是,說明大部份列是排列的,如是不是,表時表是随機排列的.這時可以通過重組表以解決問題.

4)看看表最近沒有沒建立新的index,使SQL的執行計劃發生改變.(下面的語句可以檢視到)

看看有沒有invalid的index.

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;

5)OPTIMIZER_INDEX_COST_ADJ 和OPTIMIZER_INDEX_CACHING

(來源于網上)其次，由于測試環境的不同，Tom的測試結果是在預設值(100)的環境下，

就已經和上面取值500時一樣了，即對T2全表掃描而T1使用索引。Tom試驗中，減小取值直至0，

通路路徑就變成使用兩個索引，而并不會出現均不使用索引的情況。除去系統的不同

(可能導緻取預設值時通路路徑是否一緻)，隻看變化趨勢，顯然10g中靈活性更高

，1-10000的取值使得CBO可以覆寫所有的通路路徑。另一方面，正如Tom的結論所說，

OPTIMIZER_INDEX_COST_ADJ的取值越大，優化器越傾向于使用全表掃描，取值越小，

優化器越傾向于使用索引。

再次，我們對比相同通路路徑下的不同點。在取值從1變化到200(1-50-100-200)

的過程中，優化器計算出的代價是持續增長的，而從1000到10000則是不變的。

這說明這個參數與索引I/O的代價有關，而和全表掃描并無關系，這與Tom所說的并不沖突，

不過顯然更精确一點。

最後我們其實應該看到，雖然有如上所說的代價變化問題，

同一通路路徑下實際的運作性能并無差別，由于資料量比較小，上面的例子也許不能很好的說明這一點，

不過想想Oracle用相同的路徑去執行，也沒有理由不同性能吧。

OPTIMIZER_INDEX_CACHING值為0,值越大,系統越tendence去用nested loops .

Find out what values the sessions are running with. Up to Oracle9i Database,

this information could only be obtained by tracing the sessions with the trace

event 10053 at level 1 and examining the trace files. In Oracle Database 10g,

this is as simple as querying the V$SES_OPTIMIZER_ENV view. 可以通過10053事件檢視SESSION相應的OPTIMIZER_INDEX_COST_ADJ 和OPTIMIZER_INDEX_CACHING值是多少,

在10g中省不了事,直接查V$SES_OPTIMIZER_ENV視圖就可以了,下面的是例子:

select * FROM V$SES_OPTIMIZER_ENV WHERE NAME=LOWER('OPTIMIZER_INDEX_COST_ADJ') or

name=lower('OPTIMIZER_INDEX_CACHING');

SID ID NAME ISDEFAULT VALUE

--------------------------------------------------------

144 67 optimizer_index_caching YES 0

145 66 optimizer_index_cost_adj YES 100

145 67 optimizer_index_caching YES 0

因為oracle的optimizer依賴于表與索引的statistics,是以要確定現在的statistics能夠代表現有資料,

不正确的statistics會讓optimizer 産生低效的執行計劃,當然statistics也不必天天更新,因為這樣的話,

執行計劃就也會天天更新,這對性能問題的分析會産生幹擾

System-Level Diagnosis

V$SYSTEM_EVENT視圖為系統級别的診斷提供資料,基中AVERAGE_TIME和TIME_WAITED與I/O相關事件關聯

記住TIME_WAITED隻是記錄自執行個體啟動以來的記錄,當執行個體運作比較長的一段時間後,db file sequential read

通常較高.當然,經常查詢V$SYSTEM_EVENT并且以TIME_WAITED排序,能夠通過互相比較而找到比較明顯的等待事件.

當db file sequential read 不位于top five時,不要擔心,因為可能有更大的問題要去發現

當db file sequential read 位于top five時,也總能說明資料庫進行了大量的single-block讀.

這裡可以看系統級别的診斷能力是非常受限的.但事件總是兩面情,這裡卻可以看系統硬體上瓶頸

這在v$session_wait事件裡可是看不到的.當你想更新系統,可是你的直接上司要求你提供系統瓶頸報告時,

下面就是那個好辦法:

select a.event,

a.total_waits,

a.time_waited,

a.time_waited/a.total_waits average_wait, 這裡的average_wait是很用的

sysdate – b.startup_time days_old

from v$system_event a, v$instance b

order by a.time_waited;

當average single-block讀超過你所定的閥門的時候,你要看看I/O子系統是不是得到優化了.

當然用作業系統的I/O控制指令(iostat,vmstat)去監控硬碟,可以發現I/O的瓶頸,

可以去評估各I/O子系統之間是不是平衡.

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn

dev8-0 3.93 17.03 34.66 54592552 111099454

dev8-1 12.08 56.68 99.93 181659920 320286944

dev8-2 23.38 194.11 189.93 622154550 608747464

dev8-3 16.00 230.43 128.04 738570544 410383416

dev8-4 4.73 59.89 80.98 191965458 259557752

通過上例,可以看到dev8-2,dev8-3的塊讀寫是遠遠超過其它的,是以可以考慮平衡一下I/O

另外,除了從V$SYSTEM_EVENT視圖中進行系統級别的db file sequential read average wait之外,

oracle也提供了另外一個視圖v$filestat來擷取single-block讀的統計資料.

select a.file#,

b.file_name,

a.singleblkrds,

a.singleblkrdtim,

a.singleblkrdtim/a.singleblkrds average_wait

from v$filestat a, dba_data_files b

where a.file# = b.file_id

and a.singleblkrds > 0

order by average_wait;

FILE# FILE_NAME SINGLEBLKRDS SINGLEBLKRDTIM AVERAGE_WAIT

----- ----------------------------- ------------ -------------- ------------

367 /dev/vgEMCp113/rPOM1P_4G_039 5578 427 .076550735

368 /dev/vgEMCp113/rPOM1P_4G_040 5025 416 .08278607

369 /dev/vgEMCp113/rPOM1P_4G_041 13793 1313 .095193214

370 /dev/vgEMCp113/rPOM1P_4G_042 6232 625 .100288832

371 /dev/vgEMCp113/rPOM1P_4G_043 4663 482 .103366931

372 /dev/vgEMCp108/rPOM1P_8G_011 164828 102798 .623668309

373 /dev/vgEMCp108/rPOM1P_8G_012 193071 125573 .65039804

374 /dev/vgEMCp108/rPOM1P_8G_013 184799 126720 .685717996

375 /dev/vgEMCp108/rPOM1P_8G_014 175565 125969 .717506337