[Oracle]效能最佳化調整(四)–調整磁碟I/O

 

    一.雖然通過調整SGA組件可以最大限度地減少物理I/O的發生，但是我們在某些活動中還是不可避免的將會產生磁碟I/O。需要注意的是在記憶體中的I/O活動是邏輯的，在磁碟上的I/O活動是物理的。邏輯I/O比物理I/O快數千倍。        二.產生磁碟I/O的來源： 1.Database Writer(DBWO)--寫Database Buffer Cache中的緩衝區到資料庫中的資料檔案。 2.Database Writer(DBWO)--寫資料到回退段以維護讀一致性 3.使用者Server Process--讀取資料區塊到Database Buffer Cache中 4.Log Writer(LGWR)--從Redo Log Buffer中寫事務恢複資訊到Online Redo Log上 5.Archiver(ARC0)--讀取重做日誌的內容，並寫到存檔目標上 6.臨時的大型排序寫盤操作        三.調整物理I/O的2個目標： 1.適當的SGA大小 2.儘可能快地執行物理I/O    四.提高磁碟I/O需要調整的資料庫物理結構 1.資料檔案 2.DBWO 3.單個段資料區塊 4.排序操作和臨時段 5.回退段     五.調整資料表空間與資料檔案select s.FILE#||'D' "FILE#+TYPE" , d.NAME "DATAFILE",s.PHYRDS,s.PHYWRTS,s.AVGIOTIM,s.MINIOTIM,s.MAXIOWTM,s.MAXIORTMfrom v$filestat s,v$datafile dwhere s.FILE#=d.FILE#unionselect s.FILE#||'T' "FILE#+TYPE" , t.NAME "DATAFILE",s.PHYRDS,s.PHYWRTS,s.AVGIOTIM,s.MINIOTIM,s.MAXIOWTM,s.MAXIORTMfrom v$filestat s,v$tempfile twhere s.FILE#=t.FILE#order by 1PHYRDS--該資料檔案上發生的物理讀次數PHYWRTS--該資料檔案上發生的物理寫次數AVGIOTIM--平均花費的I/O時間MINIOTIM--花費的最短時間MAXIOWTM--寫操作花費的最長時間MAXIORTM--讀操作花費的最長時間 我們能夠做的工作就是(1)不要將非Oracle檔案放在與Oracle檔案相同的磁碟上，避免引起對磁碟資源的潛在爭用!(2)建立局部管理資料表空間(LMT Locally Managed Tablespace)(3)均衡資料檔案I/O--不要把資料庫的段集中放在某一個資料表空間，如:system.另外如果可能的話，將高頻率的I/O資料檔案放在不同的磁碟或者控制器上面(4)調整init.ora參數的DB_FILE_MULTIBLOCK_READ_COUNT--指定一個使用者Server Process在執行全表掃描的時候，讀取的最多資料區塊個數，預設是8 select name,value from v$sysstat where name='table scans (long tables)'--如果統計資料很大，說明經常執行全表掃描，這樣的話增大這個參數是有收穫的     六.調整DBW0的效能DBW0是負責寫Database Buffer Cache中的緩衝區到資料庫中的資料檔案。由於資料檔案可能駐留在一個速度比較慢的裝置上或者DBW0寫髒緩衝區的效率不高，早成了等待事件。   select event,total_waits,average_wait from v$system_event where event in('buffer busy waits','db file parallel write' ,'free buffer waits','write complete waits' )--如果發現等待次數過多，可能就需要調整(1)參數DBWR_IO_SLAVES --啟動Database Writer從屬進程 預設0(2)參數DB_WRITER_PROCESSES --啟動額外Database Writer進程 與DBWR_IO_SLAVES相比可以DBWO的全部功能 預設1，最大10    七.調整段I/OORACLE儲存體繫結構--資料庫包含一個或多個資料表空間--資料表空間使用資料檔案來儲存段--段由一個或多個範圍組成--範圍由連續的ORACLE塊組成--ORACLE塊由連續的作業系統塊組成 (1)塊SYSTEM和TEMP資料表空間使用的是主塊,由DB_BLOCK_SIZE參數設定其他資料表空間可以使用BLOCKSIZE關鍵字指定塊的大小，不指定就預設為主塊的大小(2)範圍create table col_cust( cust_id varchar2(20), cust_name varchar2(20))PCTFREE 20 PCTUSED 30INITTRANS 5STORAGE (initial 250K next 250K pctincrease 0 freelists 1)TABLESPACE aap1_data;--上面那段SQL中,範圍=32塊*8KB/塊=250KPCTFREE 20--每個塊預留20%的空間用於儲存行更新資訊(3)不要將應用程式的表和索引放在system資料表空間中。(4)設定合理的範圍大小 通過參數DB_FILE_MULTIBLOCK_READ_COUNT大範圍的缺點是資料表空間的空間可能被浪費，但是讀取I/O的次數就可能會減少(5)設定合理的塊大小  八.調整排序I/O會導致排序的操作--ORDER BY--GROUP BY--SELECT DISTINCT--UNION--INTERSECT--MINUS--ANALYZE--CREATE INDEX(1)盡量在記憶體中完成排序SORT_AREA_SIZE(不推薦更改預設值)SORT_AREA_RETAINED_SIZEPGA_AGGREGRATE_TAGETWORKAREA_SIZE_POLICYselect mem.NAME,mem.VALUE /(disk.VALUE+mem.VALUE) "In-memory Sort Ratio"from v$sysstat mem,v$sysstat diskwhere mem.NAME='sorts (memory)'and disk.NAME='sorts (disk)'建議95%以上都在記憶體中排序(2)減少或者避免磁碟排序(a)UNION ALL取代UNION 避免INTERSECT/MINUS/DISTINCT(b)對ORDER BY和GROUP BY所引用的列盡量使用index(c)CREATE INDEX時增加NOSORT選項(3)使用暫存資料表空間使用下面這條語句可以查詢一個資料表空間是臨時還是永久的select tablespace_name,contents from dba_tablespaces把一個暫存資料表空間指定為所有使用者的預設暫存資料表空間alter database default temporary tablespace temp;使用者預設的暫存資料表空間是SYSTEMselect sess.USERNAME,sql.SQL_TEXT,sort.BLOCKSfrom v$session sess,v$sqltext sql,v$sort_usage sortwhere sess.SERIAL#=sort.SESSION_NUMand sort.SQLADDR=sql.ADDRESSand sort.SQLHASH=sql.HASH_VALUEand sort.BLOCKS>200;上述語句可以查詢引起大排序的SQL語句，然後可以通過建立索引或者重寫語句來消除大排序 九.調整回退段I/O當使用者啟動一個DML的事務時，以修改資料的像前版本被緩衝在SGA的Database Buffer Cache中。這些緩衝區的副本也被寫到一個回退段上。ORACLE利用回退段(rollback segment)來儲存應用程式使用者執行DML操作所產生的以修改資料的像前版本。該回退段被儲存在一個資料表空間中。回退段也叫做撤銷段(undo segment).像前版本的目的：1.ROLLBACK時恢複未經處理資料2.COMMIT以前，提供其他使用者訪問原資料，提供以修改資料的讀一致性3.當執行個體發生故障時，撤銷還未提交的事務