詳解 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