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）