讀書筆記-高階owi與oracle效能調整-io

db file scattered read
oracle在執行全表掃描或全索引掃描時,為保障效能,儘量一次性讀取多個塊,這稱為multi block i/o.每次執行multi block i/o,都會等待物理i/o結束,此時等待db file scattered read事件.
解決辦法:
(1)需要篩選出主要發生db file scattered read等待的sql語句.如果不必要地執行fts或index full scan,修改sql語句或建立更合理的索引就可以解決.
(2)如果高速緩衝區過小,就會反覆需要物理i/o,相應地db file scattered read等待也會增加.這時free buffer waits等待事件一同出現的機率較高.也可以使用多重緩衝池解決.
(3)需要檢查,透過合理執行partitioning能否減少fts範圍.
(4)如果利用sql的最佳化或高速緩衝區的最佳化也不能解決問題,就應該懷疑i/o系統本身的效能.

db file sequential read
每次發生single block i/o時,就會發生一次db file sequential read事件的等待.一般在索引掃描,透過rowid的表掃描,讀取控制
檔案和檔案頭時發生.
db file sequential read等待使用效能出現問題,這些效能問題大多數發生在低效的索引掃描,行遷移,行連結引發附加的i/o過程中.
解決辦法:
(1)使用選擇性較差的索引是發生db file sequential read等待的主要原因.使用不當的索引不僅引發i/o爭用,而且還可能引發高速緩衝區的爭用.只需將sql語句最佳化,有效使用索引,就能防患於未然.總 是擁有最新的統計資訊,也是相當重要的,利用dbms_stats程式包,可以對資料庫內的所有物件以最優的方式更新統計資訊.
(2)如果高速緩衝區過小,就會反覆發生物理i/o,因此可能增加db file sequential read等待,這時同時發生free buffer waits等待的機率較高.即使恰當建立了索引,db file sequential read等待依然比期待時間長,就需要考慮clustering factor是否過高?行遷移或行連結是否過多發生?
消除行遷移的方法:a.先匯出再匯入b.執行alter table xxx move...c.利用執行analyze table xxx list chained rows into yyy
篩選發生遷移的行,對於該行執行刪除後插入.
(3)若sql最佳化或高速緩衝區最佳化,重建表也不能解決問題,就應該懷疑i/o系統本身的效能.

direct path read
direct path read事件的等待是在執行parallel query時,slave session所執行的direct path i/o引發的.
在如下方面尋找調優點:
(1)提高parallel query本身效能.執行parallel query過程中的direct path read等待是必然的,調優這個等待事件本身是不可能的,而透過對sql進行調優,改善parallel query本身效能是恰當的解決方式.
(2)提高i/o系統本身的效能.

direct path write
direct path write等待意味著發生了direct load工作(ctas,insert /*+append*/等).這些工作請求時,oracle將不經過sga
在資料檔案上執行直接寫入工作.
特點:
(1)不經過sga,在資料檔案上執行直接寫入
(2)在hwm這後新增塊,即,不使用空閒列或點陣圖塊上管理空閒塊.
(3)對於新增的資料不建立回滾段(只是ctas時,建立對於資料字典修改的撤銷)
(4)表裡有nologging選項時,不生成重做記錄
(5)對於表以exclusive模式獲得tm鎖,因此不允許其它會話上的dml
如果合理使用direct load操作,就可以快速建立大量資料.能過並行執行direct模式和parallel模式,可將效能進一步最最佳化.如果是direct模式,資料將被 直接寫入到表的段,但是與parallel模式並行時,暫時直接寫入到表所屬的永久表空間內的臨時段後,在所有的工作成結束之後再合併到表段上.

direct path read temp/direct path write temp
為了排序工作在臨時區域讀寫時,等待direct path read temp/direct path write temp事件.這個等待事件是從oracle 10g起被分類的,oracle 9i為止是透過direct path read,direct path write等待觀察的.排序段上的direct path i/o是在需要排序的資料比為排序所分配的pga記憶體區大時發生的.因此在排序工作時若大量發生direct path read temp,direct path wirte tmep等待,就可以透過追加記憶體區域而避免等待.
解決辦法:
(1)檢查需要排序的sql語句是否已經最最佳化.不必要的排序操作會導致cpu浪費,pga區域浪費,磁碟i/o浪費.從union和union all的效能差異上可以得知,只靠減少不必要的排序操作,也能解決許多問題.
(2)pga擁有為執行排序,hash join,點陣圖運算等的記憶體區域,這稱為工作區(work area).從v$sesstat檢視檢視session pga memory max值,可以坦到實際使用的最大記憶體區域.適當設定pga_aggregate_target值時,direct path i/o將消失,因此direct
path read temp,direct path write temp等待現象完全消失.

direct path read(lob)/direct path write(lob)
如果建立lob列時使用nocache選項,讀寫lob段的工作就使用direct path i/o,這時等待direct path read(lob),direct path write
(lob)事件.使用cache選項建立lob列時因為經過高速緩衝區,所以應用與一般資料相同的機制.
建立lob時,在cache和nocache中使用哪個選項,是根據系統和資料的特點決定的.如果sga沒有剩餘空間或lob資料較大,則使用nocache比較有利.如果lob資料不大,而且經常訪問的範圍固定,則透過使用cache選項可得到效能改善的效果.
建立lob時應該注意如下事項:
(1)使用enable storage in row選項時,比4000 bytes小的lob資料與行一起儲存在相同的塊,因此引發行連結的可能性高.使用比4000 bytes大的lob資料與使用disable storage in
row選項時具有相同結果.考慮到lob資料的大小,如果被判斷為發生行連結的可能性高,最好使用disable storage inrow選項.儲存在與行相同塊裡的lob資料稱為in-line lob,儲存在Lob段時稱為out-of-line lob.
(2)使用disable storage in row時,lob資料存於另外的Lob段,行內只儲存20 bytes大小的lob地址資訊.這時回滾資料只對lob定位建立.lob資料的實際撤銷資訊不是存於撤銷表空間上,而是存於lob段內.lob段的回滾 段被pctversion選項所控制.例如pctversion為50,lob段的空間中50%用於儲存撤銷資訊.pctversion過小,則發生錯誤 ora-01555的機率高,pctversion過大,則浪費空間加深.
(3)cache/nocache,logging/nologging:如果lob資料大,且不經常被訪問或隨機被訪問,就應該使用nocache選 項.使用nocache選項時,可以指定建立重做與否.如果不是必須恢復的資料,就可以使用nologging選項,因此能獲得效能改善的效果.使用 cache選項時必須要擁有logging屬性.
(4)chunk的大小ut-of-line lob時,就以chunk為單位儲存lob資料.使用較大的chunk問題在於發生空間浪費的可能性高.如果chunk原來是8k,插入1k大小的lob 資料,則餘下7k空間可能無法使用.所以決定chunk大小時,應該考慮lob資料的大小後再作決定.chunk大小的單位基本上塊大小的整數倍.

db file parallel write
經過高速緩衝區的所有資料是透過dbwr寫入到磁碟上的.dbwr請求寫入髒塊的i/o後,在此工作結束期間等待db file parallel write
事件.
發生的原因和解決辦法:
(1)i/o系統的效能緩慢時.如果dbwr程式上db file paralle write等待時間表現得過長,就可以判斷為i/o系統上有問題.如果dbwr上的db file paralle write等待時間延長,伺服器程式就會接連經歷free buffer waits事件或write complete waits事件的等待.
透過改善i/o系統解決,改善i/o效能的方法如下:
a.組合使用裸裝置和非同步i/o是目前為止蝗最好方法.
b.os級上使用direct i/o.若cpu數量充足,可以調整db_writer_processes引數值,將dbwr數量增加.多個dbwr具有模擬異常的效果.
oracle推薦的dbwr程式是cpu_count/8.利用v$filestat檢視,可以間接推斷是哪些檔案上主要發生效能問題的.
(2)i/o工作過多時.頻繁發生檢查點時,dbwr的活動量過多,可能導致dbwr的效能降低.fast_start_mttr_target引數值設定過小時,
將頻繁發生增量檢查點工作.日誌檔案過小時,日誌檔案切換頻繁,查檢點工作將增加.因parallel query發生direct path read時,在truncate,drop,host backup時也發生檢查點.
(3)不能有效使用高速緩衝區時.間接改善dbwr效能的另一種方法是合理使用多重緩衝池.

db file parallel read
在進行資料庫恢復操作期間,當作為恢復的一部份而需要更改的資料庫塊從資料檔案中並行讀取時產生該事件.當一個程式從一個或多個資料檔案讀取多個非連續的單獨資料塊時也產生該事件.

control file parallel write
控制檔案具有如下資訊:
the database name
the timestamp of database createion
the names and locations of associated datafiles and redo logfiles
tablespace information
datfile offline ranges
the log history
archived log information
backup set and backup piece information
backup datafile and redo log information
datafile copy information
the current log sequence number
checkpoint information
以下情況可能發生與控制檔案相關的爭用:
(1)日誌檔案切換經常發生時.每當發生日誌檔案切換時,需要對控制檔案進行更新.
(2)檢查點經常發生時,mttr設定得過亂咬或頻繁發生人為的檢查點時.
(3)nologging引起頻繁的資料檔案修改時,為了修改unrecoverable scn需要更新控制檔案.對於利用nologging選項建立的lob資料進行修改是代表性的例子.
(4)i/o系統的效能緩慢時.最好是將控制檔案位於獨產的磁碟空間上,使用裸裝置或direct i/o.若控制檔案的數量過多時,最好是隻保管恢復所需的最少量的控制檔案.