Oracle数据库提高命中率及相关优化(1)(2)

6)计算在内存中排序的比率:

SQL>SELECT * FROM v$sysstat t WHERE NAME='sorts (memory)';—查询内存排序数 SQL>SELECT * FROM v$sysstat t WHERE NAME='sorts (disk)';—查询磁盘排序数 --caculate sort in memory ratio SQL>SELECT round(&sort_in_memory/(&sort_in_memory+&sort_in_disk),4)*100||'%' FROM dual;

此比率越大越好,太小整要考虑调整,加大PGA

7)PGA的命中率:

计算公式:BP x 100 / (BP + EBP)

BP: bytes processed

EBP: extra bytes read/written

SQL>SELECT * FROM V$PGASTAT WHERE NAME='cache hit percentage';

或者从OEM的图形界面中查看

我们可以查看一个视图以获取Oracle的建议值:

SQL>SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb, ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc, ESTD_OVERALLOC_COUNT FROM V$PGA_TARGET_ADVICE; The output of this query might look like the following: TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT ---------- -------------- -------------------- 63 23 367 125 24 30 250 30 3 375 39 0 500 58 0 600 59 0 700 59 0 800 60 0 900 60 0

在此例中:PGA至少要分配375M

我个人认为PGA命中率不应该低于50%

以下的SQL统计sql语句执行在三种模式的次数: optimal memory size, one-pass memory size, multi-pass memory size:

SQL>SELECT name profile, cnt, decode(total, 0, 0, round(cnt*100/total,4)) percentage FROM (SELECT name, value cnt, (sum(value) over ()) total FROM V$SYSSTAT WHERE name like 'workarea exec%');

8)共享区字典缓存区命中率

计算公式：SUM(gets - getmisses - usage -fixed) / SUM(gets)

命中率应大于0.85

SQL>select sum(gets-getmisses-usage-fixed)/sum(gets) from v$rowcache;

9)数据高速缓存区命中率

计算公式：1-(physical reads / (db block gets + consistent gets))

命中率应大于0.90最好

SQL>select name,value from v$sysstat where name in ('physical reads','db block gets','consistent gets');

10)共享区库缓存区命中率

计算公式：SUM(pins - reloads) / SUM(pins)

命中率应大于0.99

SQL>select sum(pins-reloads)/sum(pins) from v$librarycache;

11)检测回滚段的争用

SUM(waits)值应小于SUM(gets)值的1%

SQL>select sum(gets),sum(waits),sum(waits)/sum(gets) from v$rollstat;

12)检测回滚段收缩次数

SQL>select name,shrinks from v$rollstat, v$rollname where v$rollstat.usn = v$rollname.usn; -----------------------------------------------------------------------------