調整緩衝區快取記憶體(Buffer Cache)的效能(轉)

zhouwf0726發表於2019-03-02
快取記憶體
調整緩衝區快取記憶體(Buffer Cache)的效能　　
　　Buffer cache由資料塊組成。
　　
　　1. Buffer cache的工作原理
　　
　　LRU列表：MRU　………………. LRU。(全表掃描FTS放在LRU端。)
　　
　　緩衝區塊的狀態：Free、Pinned、Clean、Dirty。
　　
　　Dirty List或Write List(寫列表)。
　　
　　資料庫寫程式DBW0將緩衝區快取記憶體中的資料寫到資料檔案中。
　　
　　2.測量Buffer cache的效能
　　
　　測量Buffer cache的命中率：
　　SQL> select 1-((physical.value – direct.value – lobs.value)/logical.value) “Buffer Cache Hit Ratio” from V$SYSSTAT physical, V$SYSSTAT direct,
　　V$SYSSTAT lobs, V$SYSSTAT logical where physical.name = ‘physical reads’
　　And direct.name = ‘physical reads direct’ and
　　lobs.name = ‘physical reads direct (lob)’
　　And logical.name = ‘session logical reads’;
　　“Buffer Cache Hit Ratio”的值要 > 90%。
　　
　　使用STATSPACK來監視Buffer cache。
　　
　　使用REPORT.TXT來監視Buffer cache。
　　
　　非命中率指標：Free Buffer Inspected、Free Buffer Waits、Buffer Busy Waits。(V$sysstat)
　　
　　使用Performance Manager(資料庫例程)來監視Buffer Cache。
　　
　　3. 提高緩衝區快取記憶體效能的方法
　　
　　加大Buffer Cache的大小：init.ora引數DB_CACHE_SIZE(動態引數)。
　　
　　使用Buffer Cache Advisory功能決定Buffer Cache的大小：
　　
　　首先將init.ora引數DB_CACHE_ADVICE設成ON，然後查詢V$DB_CACHE_ADVICE。
　　
　　使用多個緩衝區池：
　　Keep Pool: DB_KEEP_CACHE_SIZE
　　Recycle Pool:DB_RECYCLE_CACHE_SIZE
　　Default Pool: DB_CACHE_SIZE
　　
　　在記憶體中快取表: 表的CACHE選項，對優化小表的全表掃描。
　　
　　正確建立索引。
　　
　　4.調整Large Pool和JAVA POOL
　　
　　Large Pool用於共享伺服器、RMAN、並行查詢、DBWR的從屬程式。
　　
　　Large Pool的大小通過init.ora引數Large_pool_size設定。預設為8M。
　　
　　從V$sgastat中監視free memory的值：
　　
　　SQL>SELECT name,bytes FROM V$sgastat WHERE pool = ‘large pool’;
　　
　　JAVA_POOL池的預設大小為32M。對於大型Java應用程式，JAVA_POOL池的大小應大於50M。
　　
　　init.ora引數java_pool_size
　　
　　從V$sgastat中監視free memory的值。
　　
　　SQL>SELECT name,bytes FROM V$sgastat WHERE pool = ‘java pool’;
　　
　　調整重做有關的效能
　　
　　Oracle重做有關的元件包括：Redo Log Buffer、Online Redo Log、LGWR、Archive Log、Checkpoint、Arch0。
　　
　　1. 監視Redo Log Buffer的效能
　　
　　Redo Log Buffer不採用LRU(Least Recently Used)演算法管理。
　　
　　當下列事件發生時，Redo Log Buffer的內容存檔：
　　
　　Commit時、每3秒、空間使用1/3、達到1M、檢查點。
　　
　　如果寫入Redo Log Buffer的速度超過LGWR存檔的速度，就會因等待而降低效能。
　　
　　監視Redo Log Buffer的重試率(<1%)。
　　Select retries.value/entries.value “Redo Log Buffer Retry Ratio”
　　From V$sysstat retries, V$sysstat entries
　　Where retries.name =　‘redo buffer allocation retries’
　　And entries.name =　‘redo entries’;
　　“Redo Log Buffer Retry Ratio”的值要 < 1%。
　　
　　Select name,value from V$sysstat where name=’redo log space requests’;
　　
　　如果該值大，需要增加Redo Log Buffer。
　　
　　2. 提高Redo Log Buffer的效能
　　
　　增加Redo Log Buffer的大小：init.ora引數log_buffer。
　　
　　減小重做日誌的生成量。(如果設定表的NOLOGGING屬性，下列操作不記錄在Online Redo Log中：用SQL* Loader的直接路徑載入。
　　
　　NOLOGGING屬性還可用於下列SQL語句：CREATE TABLE AS SELECT、CREATE INDEX、
　　ALTER INDEX REBUILD、CREATE TABELSPACE)。
　　
　　3. 調整檢查點程式的效能
　　
　　測量檢查點程式的效能：沒有完成的檢查點程式的次數。
　　select * from V$system_event;
　　
　　兩個事件：checkpoint completed、log file switch(checkpoint incomplete)。
　　
　　Select * from V$sysstat。
　　background checkpoints started和background checkpoints completed。
　　
　　使用Alert日誌來記錄檢查點程式：init.ora引數log_checkpoint_to_alert。
　　
　　使用Performance Manager來測量檢查點程式的效能：I/O中的平均灰資料佇列長度(如果為0,說明檢查點太頻繁)。
　　
　　建議調整online redo log的大小，使檢查點程式每20-30分鐘執行一次。
　　
　　4. 調整聯機重做日誌檔案
　　
　　使用V$system_event來監視聯機重做日誌檔案的效能：
　　log file parallel write、log file switch completed。
　　
　　調整聯機重做日誌檔案的方法：與資料檔案、控制檔案、歸檔日誌檔案分開，放在原始裝置上。
　　
　　5. 調整歸檔效能
　　
　　檢查歸檔程式的效能：通過V$system_event中的log file switch(archiving needed)事件。
　　
　　檢查每個歸檔程式的狀態：V$archive_processes。
　　
　　建立多個歸檔程式：init.ora引數LOG_ARCHIVE_MAX_PROCESSES(預設為2)。
　　
　　調整磁碟I/O的效能
　　
　　哪些操作會導致磁碟I/O：
　　
　　將Buffer cache中的內容寫到資料檔案。
　　
　　寫回退段。
　　
　　將資料檔案的內容讀到Buffer cache中。
　　
　　將Redo log Buffer中的內容寫到online redo log中。
　　
　　將online Redo log中的內容歸檔到 archive log中。
　　
　　1. 調整表空間和資料檔案
　　
　　測量資料檔案I/O：使用V$filestat。
　　
　　使用STATSPACK來測量資料檔案I/O。
　　
　　使用REPORT.TXT來測量資料檔案I/O。
　　
　　使用Performance Manager(I/O)來測量資料檔案I/O。
　　
　　建議：
　　
　　不要在SYSTEM表空間存放使用者資料。
　　
　　將 I/O操作均分到幾個資料檔案上。(監視資料檔案的I/O運算元。)
　　
　　使用本地管理的表空間。
　　
　　將資料庫檔案與其它程式的檔案分開。
　　
　　使用分割槽表和分割槽索引。
　　
　　將大表放在單獨的表空間。
　　
　　建立單獨的回退表空間。
　　
　　建立一個或多個臨時表空間。
　　
　　不要將聯機重做日誌檔案和歸檔聯機重做日誌檔案放在同一個裝置上。
　　
　　至少將一個控制檔案放在一個單獨的裝置上。
　　
　　檢查V$sysstat中的’table scans(long tables)’。
　　
　　使用init.ora引數DB_FILE_MULTIBLOCK_READ_COUNT(預設為16)來優化表掃描。
　　
　　2. 調整DBW0效能
　　
　　監視DBWR0效能。
　　
　　使用V$system_event監視下列事件：buffer busy waits、free buffer waits、
　　
　　db file parallel write、write complete waits、
　　
　　使用init.ora引數DBWR_IO_SLAVES(優化磁碟I/O)、
　　
　　DB_WRITER_PROCESSES(預設為1，優化Buffer Cache的內部管理)。
　　
　　如果DBWR_IO_SLAVES設為非0值，DB_WRITER_PROCESSES的值無效。
　　
　　3．調整段I/O
　　
　　避免動態空間分配。
　　
　　表的有關儲存特性
　　
　　空閒百分比(PCTFREE)：每個物件資料塊中為今後更新該物件而保留的空間的百分比。可以輸入0到99之間的值。預設值為10%。
　　
　　已用百分比(PCTUSED)：Oracle資料庫為該物件的每個資料塊保留的已用空間的最小百分比。當一個塊的已用空間低於“已用百分比”的值時，則該塊將成為插入行的目標。可以輸入1到99之間的值。預設值為40%。
　　
　　最小數量：建立段時已分配的總區數。預設值為1。可以輸入1或大於1的值。
　　
　　SQL> alter table emp allocate extent ;
　　
　　事務處理數量
　　
　　初始值：在分配給該物件的每個資料塊內分配給事務處理條目的初始數量。可以輸入1或2（對於簇和索引）到255之間的值。
　　
　　最大值：可同時更新分配給物件的資料塊的並行事務處理的最大數量。可以輸入1到255之間的值。
　　
　　自由表
　　
　　列表：表、簇或索引的每個自由表組的自由表數量。可以輸入1或大於1的值。預設值為1。
　　
　　組：表、簇或索引的自由表組的數量。可以輸入1或大於1的值。預設值為1。
　　
　　緩衝池。
　　
　　行轉移(更新行時超過塊的可用空間)和行連結(行的大小超過塊的大小)的概念。
　　
　　使用V$sysstat來監視行轉移和行連結：table fetch continued row。
　　
　　SQL>analyze table emp compute statistics;
　　
　　使用DBA_TABLES來查詢統計資訊。
　　
　　SQL> alter table emp deallocate unused;
　　SQL> alter table scott.emp move tablespace users;
　　
　　表的高水位標誌High Water Mark(HWM)。
　　
　　4．調整排序IO
　　
　　哪些SQL語句需要排序操作:order by、group by、selec distinct、union、
　　
　　intersect、minus、analyze、create index、聯接。
　　
　　V$sysstat。記憶體排序和磁碟排序(臨時表空間中)。
　　
　　監視排序效能(記憶體排序比例>95%)。
　　
　　使用init.ora引數SORT_AREA_SIZE(512K)、SORT_AREA_RETAINED_SIZE、
　　pga_aggregate_target、WORKAREA_SIZE_POLOCY。
　　
　　使用Performance Manager(資料庫例程)來監視排序。
　　
　　如何避免排序:SQL語法、正確索引、建立索引、ANALYZE。
　　v$sort_segment、v$sort_usage。
　　
　　使用Tablespace Map。
　　
　　使用 Reorg Wizard。
　　
　　5． 優化回退段
　　
　　一個回退段的區間可以分配給多個事務，回退段的一個資料塊只能分配給一個事務。
　　
　　測量回退段事務表的爭用
　　select * from V$system_event where event like ‘%undo%’;
　　
　　回退段事務表的等待時間應接近於0。
　　
　　select * from V$waitstat;
　　V$rollstat
　　
　　回退段事務表訪問的成功率應>95%。
　　
　　回退段區間爭用
　　V$waitstat、V$sysstat。
　　
　　回退段事務環繞(Wrap)：一個事務佔用的回退段從一個區間擴充套件到另一個區間。
　　
　　回退段的動態區間分配
　　V$system_event。
　　
　　使用V$rollstat來監視回退段的使用情況。
　　
　　使用Performance Manager(後臺程式)來測量回退段。
　　
　　提高回退段的效能
　　
　　Oracle9i中的撤消表空間。
　　
　　建議：每四個事務使用一個回退段，最多不超過20個回退段。
　　
　　會退段的區間大小512k，最小區間數20。
　　
　　明確分配回退段給事務。
　　SQL> set transaction use rollback segment rbs01;
　　
　　最小化回退段活動:EXPORT、IMPORT、SQL* Loader時加commit=y引數。
　　
　　Oracle9i中的撤消表空間。
　　
　　調整閂(latch)和鎖定(lock)
　　
　　1.　 調整閂(latch)
　　
　　閂可以作為記憶體效能的另一個指標。
　　
　　1.閂:等待閂和立即閂(V$lacth。共239個)。
　　
　　資料庫中是否存在閂爭用V$system_event("latch free")。
　　
　　幾個重要的閂：shared pool、library cache、cache buffers lru chain、
　　
　　cache buffers chains、redo allocation、redo copy。
　　
　　select * from V$latch where misses!=0;
　　
　　2.自由列表：V$system_event("buffer busy waits")。
　　
　　V$waitstat。
　　
　　測量哪些段存在自由列表爭用：dba_segments、V$session_wait。
　　
　　alter table scott.emp storage (freelists 5);
　　
　　自動段空間管理的表空間。
　　
　　2.　 調整鎖定
　　
　　DML鎖(TM)和DDL鎖(TX)
　　
　　鎖定模式：
　　
　　RX：對錶UPDATE、INSERT、DELETE時獲得。
　　
　　RS：對錶SELECT … FOR UPDATE時獲得。
　　
　　S: LOCK TABLE EMP IN SHARE MODE; 可以是多個使用者獲得。
　　
　　SRX: LOCK TABLE EMP IN SHARE ROW EXCLUSIVE MODE; 只能是一個使用者獲得。
　　
　　外來鍵約束時的鎖定。死鎖。
　　
　　用Lock Monitor監視鎖定。
　　
　　調整作業系統
　　
　　1. 調整作業系統
　　
　　觀察記憶體和CPU利用率(<90%)。
　　
　　2. 使用Resource Manager
　　
　　資源使用者組。一個使用者可以是多個資源使用者組的成員，但一次只有一個組是活動的。
　　
　　確定使用者的CPU利用率。(v$sesstat和v$sysstat)
　　
　　資源計劃:由資源計劃指令組成。一次只能有一個資源計劃是活動的(V$rsrc_plan)。
　　alter system set resource_manager_plan=system_plan;
　　select username,RESOURCE_CONSUMER_GROUP from V$session;
　　
　　子計劃。
　　
　　資源計劃排程。
　　
　　使用Expert進行優化
　　
　　第一步：建立一個優化會話(tuning session)。
　　
　　第二步：確定優化範圍(scope)。
　　
　　第三步：收集資料。
　　
　　第四步：複查(review)已收集到的資料。
　　
　　第五步：生成建議案。建議報告。
　　
　　第六步：建立實現建議所需的SQL指令碼。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-242310/，如需轉載，請註明出處，否則將追究法律責任。

相關文章