Oracle 檢查命中率的SQL

在資料庫啟動2小時後，可以通過以下SQL來測試資料庫效能

 

1.  緩衝區命中率:

緩衝區命中率表示在不需要進行磁碟訪問的情況下在記憶體結構中找到常用資料區塊的頻率

 

select (1-(sum(decode(name, 'physical reads',value,0))/(sum(decode(name, 'db block gets',value,0))
         +sum(decode(name,'consistent gets',value,0))))) * 100 "Hit Ratio"
         from v$sysstat;

大於98%為最佳

 

2.資料字典快取命中率：

資料字典快取命中率顯示了對資料字典和其他對象的記憶體讀操作所佔的百分比。

 

select (1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio" from v$rowcache;

大於98%為最佳

3.庫快取命中率：

庫快取命中率顯示了對實際語句和PL/SQL對象的記憶體讀操作所佔的百分比。注意，很高的命中率並不總是一件好事。

 

select Sum(Pins)/(Sum(Pins) + Sum(Reloads)) * 100 "Hit Ratio" from V$LibraryCache;

大於98%為最佳

 

4.PGA記憶體排序命中率

自動PGA記憶體管理簡化了分配PGA記憶體的方法。Oracle動態調整工作區PGA記憶體的大小(以SGA記憶體大小的20%為基礎)。在自動PGA記憶體管理員模式下運行時，所有會話的工作區大小都是自動的。執行個體中活動工作區可用的PGA記憶體總量自動由SORT_AREA_SIZE或PGA _ AGGREGATE_ TARGET(首選)初始化參數匯出。PGA記憶體排序率的值應該大於98%。依據初始化參數PGA_AGGREGATE_TARGET(或者用於向後相容的SORT _AREA _ SIZE)的值，使用者排序可能在記憶體或者在指定的暫存資料表空間中的磁碟上完成，如果這個初始化參數不是太高的話。

 

select a.value "Disk Sorts", b.value "Memory Sorts",round((100*b.value)/decode((a.value+b.value),0,1,(a.value+b.value)),2)"Pct Memory Sorts" from v$sysstat a, v$sysstat b where   a.name = 'sorts (disk)'and b.name = 'sorts (memory)';

 

5. 閒置資料緩衝區的比例

 

從您初次開機Oracle資料庫的那一天開始，使用者們的查詢就開始使用記憶體。閒置記錄數除以X$BH表中的記錄總數(即所分配的資料區塊緩衝區的總數)就得到這個百分比。同時請注意，您必須以SYS的許可權來運行該查詢。此外，擁有眾多的空閑緩衝區並不一定是就最佳環境。5%-10% 為最佳。當空閑比例高於25%時，資料緩衝區設定得太大了，可能會浪費資源。

 

select decode(state,0, 'FREE',1,decode(lrba_seq,0,'AVAILABLE','BEING USED'),3, 'BEING USED', state) "BLOCK STATUS",count(*) from x$bh group by decode(state,0,'FREE',1,decode(lrba_seq,0,'AVAILABLE','BEING USED'),3, 'BEING USED', state);

 

6. 最浪費記憶體的前10個語句占所有語句的比例

在沒有調整的情況下，大多數系統中10個最常使用的SQL語句的訪問量佔了整個系統中記憶體讀操作的50%以上。本節測量了最影響效能的代碼對整個系統所造成危害的嚴重性，以百分比表示。

 

select sum(pct_bufgets) "Percent" from (select rank() over ( order by buffer_gets desc ) as rank_bufgets,to_char(100 * ratio_to_report(buffer_gets) over (),'999.99') pct_bufgets from v$sqlarea ) where rank_bufgets < 11;

小於5%為最佳。

 

7.調整濫用磁碟讀操作的主要語句

我發現在沒有作調整的情況下，在絕大多數的系統中，訪問量占前25位的語句的磁碟讀操作將佔用整個系統所有磁碟和/或記憶體讀操作的75%。

 

select disk_reads, substr(sql_text,1,4000) from v$sqlarea  order by disk_reads desc;

8.表和與它們相關聯的索引應當放置在不同的物理磁碟上，以便減少檔案I/O。

以上測試也可以通過AWR和STATSPACK來查看. 在分析結果中，我們首先要看的十項內容：
1. 首要的5個等待時間（定時事件）
2. 負載簡檔（Load profile）
3. 執行個體效率點擊率（Instance efficiency hit ratios）
4. 等待時間（Wait events）
5. 閂鎖等候（Latch waits）
6. 首要的SQL（Top SQL）
7. 執行個體活動（Instance activity）
8. 檔案I/0和段統計資料（File I/0 and segement statistics）
9. 記憶體配置（Memory allocation）
10.緩衝區等待（Buffer waits）