調(diào)整緩沖區(qū)高速緩存(Buffer Cache)的性能
2024-07-21 02:38:09
供稿：網(wǎng)友
調(diào)整緩沖區(qū)高速緩存(Buffer Cache)的性能
Buffer cache由數(shù)據(jù)塊組成。
1. Buffer cache的工作原理
LRU列表：MRU ………………. LRU。(全表掃描FTS放在LRU端。)
緩沖區(qū)塊的狀態(tài)：Free、Pinned、Clean、Dirty。
Dirty List或Write List(寫列表)。
數(shù)據(jù)庫寫進程DBW0將緩沖區(qū)高速緩存中的數(shù)據(jù)寫到數(shù)據(jù)文件中。
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來監(jiān)視Buffer cache。
使用REPORT.TXT來監(jiān)視Buffer cache。
非命中率指標(biāo)：Free Buffer Inspected、Free Buffer Waits、Buffer Busy Waits。(V$sysstat)
使用Performance Manager(數(shù)據(jù)庫例程)來監(jiān)視Buffer Cache。
3. 提高緩沖區(qū)高速緩存性能的方法
加大Buffer Cache的大小：init.ora參數(shù)DB_CACHE_SIZE(動態(tài)參數(shù))。
使用Buffer Cache Advisory功能決定Buffer Cache的大小：
首先將init.ora參數(shù)DB_CACHE_ADVICE設(shè)成ON，然后查詢V$DB_CACHE_ADVICE。
使用多個緩沖區(qū)池：
Keep Pool: DB_KEEP_CACHE_SIZE
Recycle Pool:DB_RECYCLE_CACHE_SIZE
Default Pool: DB_CACHE_SIZE
在內(nèi)存中緩存表: 表的CACHE選項，對優(yōu)化小表的全表掃描。
正確創(chuàng)建索引。
4.調(diào)整Large Pool和java POOL
Large Pool用于共享服務(wù)器、RMAN、并行查詢、DBWR的從屬進程。
Large Pool的大小通過init.ora參數(shù)Large_pool_size設(shè)置。默認為8M。
從V$sgastat中監(jiān)視free memory的值：
SQL>SELECT name,bytes FROM V$sgastat WHERE pool = ‘large pool’;
JAVA_POOL池的默認大小為32M。對于大型Java應(yīng)用程序，JAVA_POOL池的大小應(yīng)大于50M。
init.ora參數(shù)java_pool_size
從V$sgastat中監(jiān)視free memory的值。
SQL>SELECT name,bytes FROM V$sgastat WHERE pool = ‘java pool’;
調(diào)整重做有關(guān)的性能
Oracle重做有關(guān)的組件包括：Redo Log Buffer、Online Redo Log、LGWR、Archive Log、Checkpoint、Arch0。
1. 監(jiān)視Redo Log Buffer的性能
Redo Log Buffer不采用LRU(Least Recently Used)算法治理。
當(dāng)下列事件發(fā)生時，Redo Log Buffer的內(nèi)容存盤：
Commit時、每3秒、空間使用1/3、達到1M、檢查點。
假如寫入Redo Log Buffer的速度超過LGWR存盤的速度，就會因等待而降低性能。
監(jiān)視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參數(shù)log_buffer。
減小重做日志的生成量。(假如設(shè)置表的NOLOGGING屬性，下列操作不記錄在Online Redo Log中：用SQL* Loader的直接路徑加載。
NOLOGGING屬性還可用于下列SQL語句：CREATE TABLE AS SELECT、CREATE INDEX、
ALTER INDEX REBUILD、CREATE TABELSPACE)。
3. 調(diào)整檢查點進程的性能
測量檢查點進程的性能：沒有完成的檢查點進程的次數(shù)。
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參數(shù)log_checkpoint_to_alert。
使用Performance Manager來測量檢查點進程的性能：I/O中的平均灰數(shù)據(jù)隊列長度(假如為0,說明檢查點太頻繁)。
建議調(diào)整online redo log的大小，使檢查點進程每20-30分鐘執(zhí)行一次。
4. 調(diào)整聯(lián)機重做日志文件
使用V$system_event來監(jiān)視聯(lián)機重做日志文件的性能：
log file parallel write、log file switch completed。
調(diào)整聯(lián)機重做日志文件的方法：與數(shù)據(jù)文件、控制文件、歸檔日志文件分開，放在原始設(shè)備上。
5. 調(diào)整歸檔性能
檢查歸檔進程的性能：通過V$system_event中的log file switch(archiving needed)事件。
檢查每個歸檔進程的狀態(tài)：V$archive_PRocesses。
創(chuàng)建多個歸檔進程：init.ora參數(shù)LOG_ARCHIVE_MAX_PROCESSES(默認為2)。
調(diào)整磁盤I/O的性能
哪些操作會導(dǎo)致磁盤I/O：
將Buffer cache中的內(nèi)容寫到數(shù)據(jù)文件。
寫回退段。
將數(shù)據(jù)文件的內(nèi)容讀到Buffer cache中。
將Redo log Buffer中的內(nèi)容寫到online redo log中。
將online Redo log中的內(nèi)容歸檔到 archive log中。
1. 調(diào)整表空間和數(shù)據(jù)文件
測量數(shù)據(jù)文件I/O：使用V$filestat。
使用STATSPACK來測量數(shù)據(jù)文件I/O。
使用REPORT.TXT來測量數(shù)據(jù)文件I/O。
使用Performance Manager(I/O)來測量數(shù)據(jù)文件I/O。
建議：
不要在SYSTEM表空間存放用戶數(shù)據(jù)。
將 I/O操作均分到幾個數(shù)據(jù)文件上。(監(jiān)視數(shù)據(jù)文件的I/O操作數(shù)。)
使用本地治理的表空間。
將數(shù)據(jù)庫文件與其它程序的文件分開。
使用分區(qū)表和分區(qū)索引。
將大表放在單獨的表空間。
創(chuàng)建單獨的回退表空間。
創(chuàng)建一個或多個臨時表空間。
不要將聯(lián)機重做日志文件和歸檔聯(lián)機重做日志文件放在同一個設(shè)備上。
至少將一個控制文件放在一個單獨的設(shè)備上。
檢查V$sysstat中的’table scans(long tables)’。
使用init.ora參數(shù)DB_FILE_MULTIBLOCK_READ_COUNT(默認為16)來優(yōu)化表掃描。
2. 調(diào)整DBW0性能
監(jiān)視DBWR0性能。
使用V$system_event監(jiān)視下列事件：buffer busy waits、free buffer waits、
db file parallel write、write complete waits、
使用init.ora參數(shù)DBWR_IO_SLAVES(優(yōu)化磁盤I/O)、
DB_WRITER_PROCESSES(默認為1，優(yōu)化Buffer Cache的內(nèi)部治理)。
假如DBWR_IO_SLAVES設(shè)為非0值，DB_WRITER_PROCESSES的值無效。
3．調(diào)整段I/O
避免動態(tài)空間分配。
表的有關(guān)存儲特性
空閑百分比(PCTFREE)：每個對象數(shù)據(jù)塊中為今后更新該對象而保留的空間的百分比。可以輸入0到99之間的值。
默認值為10%。
已用百分比(PCTUSED)：Oracle數(shù)據(jù)庫為該對象的每個數(shù)據(jù)塊保留的已用空間的最小百分比。當(dāng)一個塊的已用空間低于“已用百分比”的值時，則該塊將成為插入行的目標(biāo)。可以輸入1到99之間的值。默認值為40%。
最小數(shù)量：創(chuàng)建段時已分配的總區(qū)數(shù)。默認值為1。可以輸入1或大于1的值。
SQL> alter table emp allocate extent ;
事務(wù)處理數(shù)量
初始值：在分配給該對象的每個數(shù)據(jù)塊內(nèi)分配給事務(wù)處理條目的初始數(shù)量。可以輸入1或2（對于簇和索引）到255之間的值。
最大值：可同時更新分配給對象的數(shù)據(jù)塊的并行事務(wù)處理的最大數(shù)量。可以輸入1到255之間的值。
自由表
列表：表、簇或索引的每個自由表組的自由表數(shù)量。可以輸入1或大于1的值。默認值為1。
組：表、簇或索引的自由表組的數(shù)量。可以輸入1或大于1的值。默認值為1。
緩沖池。
行轉(zhuǎn)移(更新行時超過塊的可用空間)和行鏈接(行的大小超過塊的大小)的概念。
使用V$sysstat來監(jiān)視行轉(zhuǎn)移和行鏈接：table fetch continued row。
SQL>analyze table emp compute statistics;
使用DBA_TABLES來查詢統(tǒng)計信息。
SQL> alter table emp deallocate unused;
SQL> alter table scott.emp move tablespace users;
表的高水位標(biāo)志High Water Mark(HWM)。
4．調(diào)整排序IO
哪些SQL語句需要排序操作:order by、group by、selec distinct、union、
intersect、minus、analyze、create index、聯(lián)接。
V$sysstat。內(nèi)存排序和磁盤排序(臨時表空間中)。
監(jiān)視排序性能(內(nèi)存排序比例>95%)。
使用init.ora參數(shù)SORT_AREA_SIZE(512K)、SORT_AREA_RETAINED_SIZE、
pga_aggregate_target、WORKAREA_SIZE_POLOCY。
使用Performance Manager(數(shù)據(jù)庫例程)來監(jiān)視排序。
如何避免排序:SQL語法、正確索引、創(chuàng)建索引、ANALYZE。
v$sort_segment、v$sort_usage。
使用Tablespace Map。
使用 Reorg Wizard。
5． 優(yōu)化回退段
一個回退段的區(qū)間可以分配給多個事務(wù)，回退段的一個數(shù)據(jù)塊只能分配給一個事務(wù)。
測量回退段事務(wù)表的爭用
select * from V$system_event where event like ‘%undo%’;
回退段事務(wù)表的等待時間應(yīng)接近于0。
select * from V$waitstat;
V$rollstat
回退段事務(wù)表訪問的成功率應(yīng)>95%。
回退段區(qū)間爭用
V$waitstat、V$sysstat。
回退段事務(wù)環(huán)繞(Wrap)：一個事務(wù)占用的回退段從一個區(qū)間擴展到另一個區(qū)間。
回退段的動態(tài)區(qū)間分配
V$system_event。
使用V$rollstat來監(jiān)視回退段的使用情況。
使用Performance Manager(后臺進程)來測量回退段。
提高回退段的性能
Oracle9i中的撤消表空間。
建議：每四個事務(wù)使用一個回退段，最多不超過20個回退段。
會退段的區(qū)間大小512k，最小區(qū)間數(shù)20。
明確分配回退段給事務(wù)。
SQL> set transaction use rollback segment rbs01;
最小化回退段活動:EXPORT、IMPORT、SQL* Loader時加commit=y參數(shù)。
Oracle9i中的撤消表空間。
調(diào)整閂(latch)和鎖定(lock)
1. 調(diào)整閂(latch)
閂可以作為內(nèi)存性能的另一個指標(biāo)。
1.閂:等待閂和立即閂(V$lacth。共239個)。
數(shù)據(jù)庫中是否存在閂爭用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. 調(diào)整鎖定
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監(jiān)視鎖定。
調(diào)整操作系統(tǒng)
1. 調(diào)整操作系統(tǒng)
觀察內(nèi)存和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;
子計劃。
資源計劃調(diào)度。
使用Expert進行優(yōu)化
第一步：創(chuàng)建一個優(yōu)化會話(tuning session)。
第二步：確定優(yōu)化范圍(scope)。
第三步：收集數(shù)據(jù)。
第四步：復(fù)查(review)已收集到的數(shù)據(jù)。
第五步：生成建議案。建議報告。
第六步：創(chuàng)建實現(xiàn)建議所需的SQL腳本。
