一SGA
Shared pool tunning
Shared pool的優化應該放在優先考慮因為一個cache miss在shared pool中發生比在data buffer中發生導致的成本更高由於dictionary數據一般比library cache中的數據在內存中保存的時間長所以關鍵是library cache的優化
Gets（parse）在namespace中查找對象的次數
Pins（execution）在namespace中讀取或執行對象的次數
Reloads(reparse)在執行階段library cache misses的次數導致sql需要重新解析
） 檢查v$librarycache中sql area的gethitratio是否超過％如果未超過％應該檢查應用代碼提高應用代碼的效率
Select gethitratio from v$librarycache where namespace=sql area;
) v$librarycache中reloads/pins的比率應該小於％如果大於％應該增加參數shared_pool_size的值
Select sum(pins) executionssum(reloads) cache missessum(reloads)/sum(pins) from v$librarycache;
reloads/pins>%有兩種可能一種是library cache空間不足一種是sql中引用的對象不合法
）shared pool reserved size一般是shared pool size的％不能超過％V$shared_pool_reserved中的request misses＝或沒有持續增長或者free_memory大於shared pool reserved size的%表明shared pool reserved size過大可以壓縮
）將大的匿名pl/sql代碼塊轉換成小的匿名pl/sql代碼塊調用存儲過程
）從i開始可以將execution plan與sql語句一起保存在library cache中方便進行性能診斷從v$sql_plan中可以看到execution plans
）保留大的對象在shared pool中大的對象是造成內存碎片的主要原因為了騰出空間許多小對象需要移出內存從而影響了用戶的性能因此需要將一些常用的大的對象保留在shared pool中下列對象需要保留在shared pool中
a 經常使用的存儲過程
b 經常操作的表上的已編譯的觸發器
c Sequence因為Sequence移出shared pool後可能產生號碼丟失
查找沒有保存在library cache中的大對象
Select * from v$db_object_cache where sharable_mem> and
type in (PACKAGEPROCEDUREFUNCTIONPACKAGE BODY) and kept=NO;
將這些對象保存在library cache中
Execute dbms_shared_poolkeep(package_name);
對應腳本dbmspoolsql
)查找是否存在過大的匿名pl/sql代碼塊兩種解決方案
A．轉換成小的匿名塊調用存儲過程
B．將其保留在shared pool中
查找是否存在過大的匿名pl/sql塊
Select sql_text from v$sqlarea where command_type= and length(sql_text)>;
）Dictionary cache的優化
避免出現Dictionary cache的misses或者misses的數量保持穩定只能通過調整shared_pool_size來間接調整dictionary cache的大小
Percent misses應該很低大部分應該低於％合計應該低於％
Select sum(getmisses)/sum(gets) from v$rowcache;
若超過％增加shared_pool_size的值
Buffer Cache
）granule大小的設置db_cache_size以字節為單位定義了default buffer pool的大小
如果SGA<Mgranule=M否則granule＝M即需要調整sga的時候以granule為單位增加大小並且sga的大小應該是granule的整數倍
) 根據v$db_cache_advice調整buffer cache的大小
SELECT size_for_estimatebuffers_for_estimateestd_physical_read_factorestd_physical_reads
FROM v$db_cache_advice WHERE NAME=DEFAULT AND advice_status=ON
AND block_size=(SELECT Value FROM v$parameter WHERE NAME=db_block_size);
estd_physical_read_factor<=
) 統計buffer cache的cache hit ratio>%如果低於％可以用下列方案解決
◆增加buffer cache的值
◆使用多個buffer pool
◆Cache table
◆為 sorting and parallel reads 建獨立的buffer cache
SELECT NAMEvalue FROM v$sysstat WHERE NAME IN (session logical reads
physical readsphysical reads directphysical reads direct(lob));
Cache hit ratio=(physical readsphysical reads directphysical reads direct (lob))/session logical reads;Select (phyvaluedirvaluelobvalue)/logvalue from v$sysstat log v$sysstat phy v$sysstat dir v$sysstat LOB where logname=session logical reads and phyname=physical reads and dirname=physical reads direct and lobname=physical reads direct (lob);
影響cache hit ratio的因素全表掃描應用設計大表的隨機訪問cache hits的不均衡分布
）表空間使用自動空間管理消除了自由空間列表的需求可以減少數據庫的競爭
其他SGA對象
）redo log buffer
對應的參數是log_buffer缺省值與 OS相關一般是K檢查v$session_wait中是否存在log buffer waitv$sysstat中是否存在redo buffer allocation retries
A檢查是否存在log buffer wait
Select * from v$session_wait where event=log buffer wait ;
如果出現等待一是可以增加log buffer的大小也可以通過將log 文件移到訪問速度更快的磁盤來解決
B
Select namevalue from v$sysstat where name in
(redo buffer allocation retriesredo entries)
Redo buffer allocation retries接近小於redo entries 的％如果一直在增長表明進程已經不得不等待redo buffer的空間如果Redo buffer allocation retries過大增加log_buffer的值
C檢查日志文件上是否存在磁盤IO競爭現象
Select eventtotal_waitstime_waitedaverage_wait from v$system_event
where event like log file switch completion%;
如果存在競爭可以考慮將log文件轉移到獨立的更快的存儲設備上或增大log文件
D檢查點的設置是否合理
檢查alertlog文件中是否存在checkpoint not complete
Select eventtotal_waitstime_waitedaverage_wait from v$system_event
where event like log file switch (check%;
如果存在等待調整log_checkpoint_intervallog_checkpoint_timeout的設置
E檢查log archiver的工作
Select eventtotal_waitstime_waitedaverage_wait from v$system_event
where event like log file switch (arch%;
如果存在等待檢查保存歸檔日志的存儲設備是否已滿增加日志文件組調整log_archiver_max_processes
FDB_block_checksum=true因此增加了性能負擔（為了保證數據的一致性oracle的寫數據的時候加一個checksum在block上在讀數據的時候對checksum進行驗證）
）java pool
對於大的應用java_pool_size應>=M對於一般的java存儲過程缺省的M已經夠用了
）檢查是否需要調整DBWn
Select total_waits from v$system_event where event=free buffer waits;
From:http://tw.wingwit.com/Article/program/Oracle/201311/17744.html
