測量磁盤I/O

我怎樣分離Oracle I/O 來使得性能最高？
　
　　我是否應該分離數據文件和索引文件？
　
　　我是否應該分離redo日志？
　
　　這些問題（並且還有更多的）看起來似乎是對於數據庫管理員來說的它們在一般程度上很容易解答但是在實際操作中可能是非常難以解決的除非我們檢查一下我們的磁盤子系統到底是怎樣執行的
　
　　我們中的許多人可能立即就不讀這篇文章了對自己說這些問題和詳細程度只是對於我們的系統管理員或那些控制磁盤管理的人來說的我經常看到兩種不同的方式來配置Oracle存儲第一種是當架構師要在一個存儲數組中分離Oracle對象類型（數據索引重做檔案文件等等）時使用Oracle靈活架構（OFAOracles Flexible Architecture）方法第二種方法是建立一個JBOD（Just a Bunch Of Disks簡單磁盤捆綁）配置並將所有東西都放到上面這兩種方法都缺乏能夠最終形成協調的數據庫系統的計劃和配置它們只是碰運氣罷了希望因為遵循了一個預先確定的方法從而一切進行順利但是這些方法可能在你的環境裡不起作用不要依靠於你的系統管理員因為他們自己可能都不知道怎麼獲取信息而當他們這麼做的時候通常是在更高的級別因為他們也不能將這些信息與Oracle堆棧關聯起來
　
　　所以數據庫管理員必須開始從一個純粹的I/O透視圖來了解應用程序將這個信息傳遞給存儲管理員然後一起開發一個配置或改變存儲子系統的計劃這將用於應用程序混合對於存儲OLTP環境的一個重要性能指示器是基於每秒I/O操作次數（IOPS）和延遲（I/O來回的時間）OLAP數據庫是你的數據倉庫或報表系統並由移動大量的主要做只讀的數據來分類對於存儲數組一個OLAP環境的性能是基於兆比特位每秒（MBPS）的一個數據庫工作負載經常描述為它的應用程序混合了解和將一個應用程序混合轉化為一個數據庫工作負載對於優化存儲系統來說是非常重要的一個OLTP數據庫的工作負載是由小的隨機I/O來分類的而OLAP是由大型的連續或隨機I/O來分類的
　
　　對於數據庫管理員來說現在該看看你的數據庫的內部了你必須想辦法獲取某種形式的統計信息使你可以在數據庫級別分類SQL類型和I/O請求例如Oracle只有很少的表允許提取這個信息舉例來說我們可以查詢gv$sysstat視圖（物理讀取總的IO請求——物理讀取總的多塊請求）來獲得系統中小量讀取的次數在一段時間內做這個操作用結束時的值減去開始時的值你就很快地獲得了那段時間的小量讀取的IOPS必須對每一個可用的統計進行這個操作以了解你的數據庫所請求的總的IOPS和MBPS這些是你需要獲得的相關統計信息基本上大量讀寫是用來計算MBPS的而小量讀寫是用來進行IOPS計算的
　
　　        gv$sysstat (name value)
　　Total Reads :physical read total IO requests
　　Total Writes:physical write total IO requests
　　Large Reads :physical read total multi block requests
　　Large Writes:physical write total multi block requests
　　Total Bytes Read :physical read total bytes
　　Total Bytes Written :physical write total bytes
　　To calculate small reads:
　　Small Reads = Total Reads  Large Reads
　　Small Writes = Total Writes  Large Writes

　　使用這些數據使我們可以考慮到我們目前的數據庫性能和存儲請求從而作出明智的決策數據庫可能正在被討論而實際的I/O請求可能比最優情形下的要低所以可能需要進行調整但是將這些IOPS和MBPS轉給存儲管理員去評估存儲解決方案是否是正確的和是否需要重新配置是數據庫管理員的責任這些重新配置可能需要移動數據文件來使用更多的磁盤添加更多的磁盤用於更高的吞吐量或者降低應用程序的IOPS和MBPS是的我們再次回到了應用程序我知道你了解要做什麼
　
　　下面兩個腳本使你可以開始了我花費了很多時間來使它們變得容易使用並提供給你信息使你能夠快速了解你的磁盤子系統在過去的時間裡執行的怎麼樣

　　

　　        set echo off
　　set feedback off
　　set heading off
　　set linesize 
　　set pagesize 
　　set verify off
　　set termout off
　　column rpt new_value rpt
　　select instance_name||_||to_char(sysdateYYYYMMDDHHMISS)||_vsysstat_ioworkloadLST rpt from v$instance;
　　set termout on
　　prompt
　　prompt
　　prompt ^^^^^^^^^^^^^
　　prompt Report Name : /LST/&&rpt
　　prompt ^^^^^^^^^^^^^
　　spool /LST/&&rpt
　　column sr new_value sr
　　column sw new_value sw
　　column lr new_value lr
　　column lw new_value lw
　　column tbr new_value tbr
　　column tbw new_value tbw
　　set termout off
　　SELECT
　　sum(decode(namephysical read total IO requestsvalue) decode(namephysical read total multi block requestsvalue)) sr
　　sum(decode(namephysical write total IO requestsvalue) decode(namephysical write total multi block requestsvalue)) sw
　　sum(decode(namephysical read total multi block requestsvalue)) lr
　　sum(decode(namephysical write total multi block requestsvalue)) lw
　　sum(decode(namephysical read total bytesvalue)) tbr
　　sum(decode(namephysical write total bytesvalue)) tbw
　　FROM v$sysstat;
　　set termout on
　　prompt
　　prompt
　　prompt ^^^^^^^^^^^^
　　prompt First Sample
　　prompt ^^^^^^^^^^^^
　　prompt Number of Small Reads : &&sr
　　prompt Number of Small Writes: &&sw
　　prompt Number of Large Reads : &&lr
　　prompt Number of Large Writes: &&lw
　　prompt Total Bytes Read : &&tbr
　　prompt Total Bytes Written : &&tbw
　　prompt
　　prompt
　　prompt Enter the amount of time (in seconds) you would like this process to sleep for sampling data
　　prompt ^^^^^^^^^^^^^^^^^^
　　prompt Sleep Time (secs): &&sleeptime
　　prompt ^^^^^^^^^^^^^^^^^^
　　exec DBMS_LOCKSLEEP (&&sleeptime);
　　column sr new_value sr
　　column sw new_value sw
　　column lr new_value lr
　　column lw new_value lw
　　column tbr new_value tbr
　　column tbw new_value tbw
　　set termout off
　　SELECT
　　sum(decode(namephysical read total IO requestsvalue) decode(namephysical read total multi block requestsvalue)) sr
　　sum(decode(namephysical write total IO requestsvalue) decode(namephysical write total multi block requestsvalue)) sw
　　sum(decode(namephysical read total multi block requestsvalue)) lr
　　sum(decode(namephysical write total multi block requestsvalue)) lw
　　sum(decode(namephysical read total bytesvalue)) tbr
　　sum(decode(namephysical write total bytesvalue)) tbw
　　FROM v$sysstat;
　　set termout on
　　prompt
　　prompt
　　prompt ^^^^^^^^^^^^^
　　prompt Second Sample
　　prompt ^^^^^^^^^^^^^
　　prompt Number of Small Reads : &&sr
　　prompt Number of Small Writes: &&sw
　　prompt Number of Large Reads : &&lr
　　prompt Number of Large Writes: &&lw
　　prompt Total Bytes Read : &&tbr
　　prompt Total Bytes Written : &&tbw
　　prompt
　　prompt
　　prompt ^^^^^^^^^
　　prompt Results :
　　prompt ^^^^^^^^^
　　column sri new_value sri
　　column swi new_value swi
　　column tsi new_value tsi
　　column srp new_value srp
　　column swp new_value swp
　　column lri new_value lri
　　column lwi new_value lwi
　　column tli new_value tli
　　column lrp new_value lrp
　　column lwp new_value lwp
　　column tr new_value tr
　　column tw new_value tw
　　column tm new_value tm
　　SELECT
　　ROUND((&&sr&&sr)/&&sleeptime) sri
　　ROUND((&&sw&&sw)/&&sleeptime) swi
　　ROUND(((&&sr&&sr)+(&&sw&&sw))/&&sleeptime) tsi
　　ROUND((&&sr&&sr)/DECODE(((&&sr&&sr)+(&&sw&&sw))((&&sr&&sr)+(&&sw&&sw)))*) srp
　　ROUND((&&sw&&sw)/DECODE(((&&sr&&sr)+(&&sw&&sw))((&&sr&&sr)+(&&sw&&sw)))*) swp
　　ROUND((&&lr&&lr)/&&sleeptime) lri
　　ROUND((&&lw&&lw)/&&sleeptime) lwi
　　ROUND(((&&lr&&lr)+(&&lw&&lw))/&&sleeptime) tli
　　ROUND((&&lr&&lr)/DECODE(((&&lr&&lr)+(&&lw&&lw))((&&lr&&lr)+(&&lw&&lw)))*) lrp
　　ROUND((&&lw&&lw)/DECODE(((&&lr&&lr)+(&&lw&&lw))((&&lr&&lr)+(&&lw&&lw)))*) lwp
　　ROUND(((&&tbr&&tbr)/&&sleeptime)/) tr
　　ROUND(((&&tbw&&tbw)/&&sleeptime)/) tw
　　ROUND((((&&tbr&&tbr)+(&&tbw&&tbw))/&&sleeptime)/) tm
　　FROM dual;
　　SELECT
　　Small Read IOPS = ||ROUND((&&sr&&sr)/&&sleeptime)|| IOPS
　　Small Write IOPS = ||ROUND((&&sw&&sw)/&&sleeptime)|| IOPS
　　Total Small IOPS = ||ROUND(((&&sr&&sr)+(&&sw&&sw))/&&sleeptime)|| IOPS
　　Small Read I/O % = ||ROUND((&&sr&&sr)/DECODE(((&&sr&&sr)+(&&sw&&sw))((&&sr&&sr)+(&&sw&&sw)))*)|| %
　　Small Write I/O % = ||ROUND((&&sw&&sw)/DECODE(((&&sr&&sr)+(&&sw&&sw))((&&sr&&sr)+(&&sw&&sw)))*)|| %
　　Large Read IOPS = ||ROUND((&&lr&&lr)/&&sleeptime)|| IOPS
　　Large Write IOPS = ||ROUND((&&lw&&lw)/&&sleeptime)|| IOPS
　　Total Large IOPS = ||ROUND(((&&lr&&lr)+(&&lw&&lw))/&&sleeptime)|| IOPS
　　Large Read I/O % = ||ROUND((&&lr&&lr)/DECODE(((&&lr&&lr)+(&&lw&&lw))((&&lr&&lr)+(&&lw&&lw)))*)|| %
　　Large Write I/O % = ||ROUND((&&lw&&lw)/DECODE(((&&lr&&lr)+(&&lw&&lw))((&&lr&&lr)+(&&lw&&lw)))*)|| %
　　Total Read = ||ROUND(((&&tbr&&tbr)/&&sleeptime)/)|| MBPS
　　Total Written = ||ROUND(((&&tbw&&tbw)/&&sleeptime)/)|| MBPS
　　Total MBPS = ||ROUND((((&&tbr&&tbr)+(&&tbw&&tbw))/&&sleeptime)/)|| MBPS
　　FROM dual
　　;
　　prompt Small Read IOPS = &&sri IOPS
　　prompt Small Write IOPS = &&swi IOPS
　　prompt Total Small IOPS = &&tsi IOPS
　　prompt Small Read I/O % = &&srp %
　　prompt Small Write I/O % = &&swp %
　　prompt Large Read IOPS = &&lri IOPS
　　prompt Large Write IOPS = &&lwi IOPS
　　prompt Total Large IOPS = &&tli IOPS
　　prompt Large Read I/O % = &&lrp %
　　prompt Large Write I/O % = &&lwp %
　　prompt Total Read = &&tr MBPS
　　prompt Total Written = &&tw MBPS
　　prompt Total MBPS = &&tm MBPS
　　spool off
　　undefine sleeptime

　　從工作負載庫歷史記錄裡獲得一個完整的IOPS和MBPS的歷史記錄並將它繪制出來用於管理這使得你可以看到總的數據庫磁盤活動將它與你的磁盤容量相比較記住這些數據是所有磁盤的你通過除以服務數據庫請求中的總的磁盤數可以獲得平均IOPS/MBPS這是很有用的信息一旦你將結果繪制成圖表你就可以看到你的I/O和應用程序的執行在這一天裡是怎麼樣的或者至少確定了峰值時間段在哪
　
　　  set echo off
　　set feedback off
　　set linesize 
　　set pagesize 
　　set verify off
　　set termout off
　　column rpt new_value rpt
　　select instance_name||_wrh_sysstat_ioworkload_||LST rpt from v$instance;
　　set termout on
　　prompt
　　prompt
　　prompt ^^^^^^^^^^^^^
　　prompt Report Name : /LST/&&rpt
　　prompt ^^^^^^^^^^^^^
　　spool /LST/&&rpt
　　column sri head Small|Read|IOPS
　　column swi head Small|Write|IOPS
　　column tsi head Total|Small|IOPS
　　column srp head Small|Read|I/O%
　　column swp head Small|Write|I/O%
　　column lri head Large|Read|IOPS
　　column lwi head Large|Write|IOPS
　　column tli head Total|Large|IOPS
　　column lrp head Large|Read|I/O%
　　column lwp head Large|Write|I/O%
　　column tr head Total|Read|MBPS
　　column tw head Total|Written|MBPS
　　column tm head Total|MBPS
　　column begin_time for a
　　column end_time for a
　　SELECT end_time
　　ROUND(sr/inttime) sri
　　ROUND(sw/inttime) swi
　　ROUND((sr+sw)/inttime) tsi
　　ROUND(sr/DECODE((sr+sw)(sr+sw))*) srp
　　ROUND(sw/DECODE((sr+sw)(sr+sw))*) swp
　　ROUND(lr/inttime) lri
　　ROUND(lw/inttime) lwi
　　ROUND((lr+lw)/inttime) tli
　　ROUND(lr/DECODE((lr+lw)(lr+lw))*) lrp
　　ROUND(lw/DECODE((lr+lw)(lr+lw))*) lwp
　　ROUND((tbr/inttime)/) tr
　　ROUND((tbw/inttime)/) tw
　　ROUND(((tbr+tbw)/inttime)/) tm
　　FROM (
　　SELECT begsnap_id beg_id endsnap_id end_id
　　begbegin_interval_time begend_interval_time
　　endbegin_interval_time begin_time endend_interval_time end_time
　　(extract(day from (endend_interval_time  endbegin_interval_time))*)+
　　(extract(hour from (endend_interval_time  endbegin_interval_time))*)+
　　(extract(minute from (endend_interval_time  endbegin_interval_time))*)+
　　(extract(second from (endend_interval_time  endbegin_interval_time))*) inttime
　　decode(endstartup_timeendbegin_interval_timeendsr(endsrbegsr)) sr
　　decode(endstartup_timeendbegin_interval_timeendsw(endswbegsw)) sw
　　decode(endstartup_timeendbegin_interval_timeendlr(endlrbeglr)) lr
　　decode(endstartup_timeendbegin_interval_timeendlw(endlwbeglw)) lw
　　decode(endstartup_timeendbegin_interval_timeendtbr(endtbrbegtbr)) tbr
　　decode(endstartup_timeendbegin_interval_timeendtbw(endtbwbegtbw)) tbw
　　FROM
　　(SELECT dba_hist_snapshotsnap_id startup_time begin_interval_time end_interval_time
　　sum(decode(stat_namephysical read total IO requestsvalue)
　　decode(stat_namephysical read total multi block requestsvalue)) sr
　　sum(decode(stat_namephysical write total IO requestsvalue)
　　decode(stat_namephysical write total multi block requestsvalue)) sw
　　sum(decode(stat_namephysical read total multi block requestsvalue)) lr
　　sum(decode(stat_namephysical write total multi block requestsvalue)) lw
　　sum(decode(stat_namephysical read total bytesvalue)) tbr
　　sum(decode(stat_namephysical write total bytesvalue)) tbw
　　FROM wrh$_sysstat wrh$_stat_name dba_hist_snapshot
　　WHERE wrh$_sysstatstat_id = wrh$_stat_namestat_id
　　AND wrh$_sysstatsnap_id = dba_hist_snapshotsnap_id
　　group by dba_hist_snapshotsnap_id startup_time begin_interval_time end_interval_time) beg
　　(SELECT dba_hist_snapshotsnap_id startup_time begin_interval_time end_interval_time
　　sum(decode(stat_namephysical read total IO requestsvalue)
　　decode(stat_namephysical read total multi block requestsvalue)) sr
　　sum(decode(stat_namephysical write total IO requestsvalue)
　　decode(stat_namephysical write total multi block requestsvalue)) sw
　　sum(decode(stat_namephysical read total multi block requestsvalue)) lr
　　sum(decode(stat_namephysical write total multi block requestsvalue)) lw
　　sum(decode(stat_namephysical read total bytesvalue)) tbr
　　sum(decode(stat_namephysical write total bytesvalue)) tbw
　　FROM wrh$_sysstat wrh$_stat_name dba_hist_snapshot
　　WHERE wrh$_sysstatstat_id = wrh$_stat_namestat_id
　　AND wrh$_sysstatsnap_id = dba_hist_snapshotsnap_id
　　group by dba_hist_snapshotsnap_id startup_time begin_interval_time end_interval_time) end
　　WHERE begsnap_id +  = endsnap_id
　　)
　　order by 
　　/
　　spool off
　
　　從一個純粹的I/O透視圖了解一個應用程序是配置存儲的一個關鍵方面Oracle 具有許多I/O類型需要進行匹配取樣和關聯到存儲在Oracle 中有服務器進程產生的I/O這些服務器進程是由用戶多數據庫寫入檢查點活動日志記錄工具（它不只是記錄更新完成了還要從在線日志讀取並通過存檔文件過程寫到存檔文件日志中）產生的還有一些內部的決定規模和I/O頻率的I/OOracle是一個非常復雜的包含很多進程的系統不了解你的磁盤I/O形式就幾乎不可能做到恰當的配置
　

From:http://tw.wingwit.com/Article/program/Oracle/201311/17361.html