软件开发培训班 >> 编程开发 >> Oracle


Oracle数据库调试和优化详解

　　本文标签：Oracle数据库

　　此文主要是关于Oracle数据库调试和优化方面的相关内容的介绍，还有涉及到Oracle数据库中命里率的相关问题的解答，其中包括不同的算法之间性能的比对  。以下就是相关内容的介绍  。

　　关于Oracle中各个命中率的计算以及相关的调优

　　1)Library Cache的命中率:

　　计算公式:

  1. Library Cache Hit Ratio = sum(pinhits) / sum(pins)  
  2. SQL>SELECT SUM(pinhits)/sum(pins) FROM V$LIBRARYCACHE;  

　　

　　通常在98%以上，否则，需要要考虑加大共享池，绑定变量，修改cursor_sharing等参数  。

　　2)计算共享池内存使用率:

　　

  1. SQL>SELECT (1 - ROUND(BYTES / 
    (&TSP_IN_M * 1024 * 1024), 2)) * 100 
    || % FROM V$SGASTAT WHERE     NAME = 
    free memory AND POOL = shared pool; 

　　其中: &TSP_IN_M是你的总的共享池的SIZE(M)，共享池内存使用率，应该稳定在75%-90%间，太小浪费内存，太大则内存不足  。

　　查询空闲的共享池内存:

　　

  1. SQL>SELECT * FROM V$SGASTAT WHERE NAME = 
    free memory AND POOL = shared pool; 

　　3)db buffer cache命中率:

　　计算公式:

  1. Hit ratio = 1 - [physical reads/(block gets + consistent gets)]  
  2. SQL>SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS, 
    1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) 
    "Hit Ratio" FROM V$BUFFER_POOL_STATISTICS WHERE     NAME=DEFAULT;  

　　

　　通常应在90%以上，否则，需要调整,加大DB_CACHE_SIZE，另外一种计算命中率的方法(摘自ORACLE官方文档<>):命中率的计算公式为:

　　

  1. Hit Ratio = 1 - ((physical reads - physical reads 
    direct - physical reads direct (lob)) / 
    (db block gets + consistent gets - physical 
    reads direct - physical reads direct (lob)) 

　　分别代入上一查询中的结果值,就得出了Buffer cache的命中率

　　

  1. SQL>SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME 
    IN(session logical reads, physical reads, 
    physical reads direct, physical reads direct 
    (lob), db block gets, consistent gets); 

　　4)数据缓冲区命中率：

　　

  1. SQL> select value from v$sysstat where name =
    physical reads; SQL> select value from v$sysstat where name 
    =    physical reads direct; SQL> select value from v$sysstat 
    where     name =physical reads direct (lob); SQL> 
    select value from v$sysstat where     name =consistent gets; 
    SQL    > select value from v$sysstat where name = db block gets; 

　　这里命中率的计算应该是令

  1. x = physical reads direct + physical reads direct (lob) 

　　命中率

  1. =100 - ( physical reads - x) / (consistent gets + db block gets - x)*100 

　　通常如果发现命中率低于90%,则应该调整应用可可以考虑是否增大数据缓冲区

　　5)共享池的命中率：

　　

  1. SQL> select sum(pinhits-reloads)/sum(pins)*100 "hit radio" from v$librarycache; 

　　假如共享池的命中率低于95%,就要考虑调整应用(通常是没使用bind var )或者增加内存

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

　　

  1. SQL>SELECT * FROM v$sysstat t WHERE NAME=sorts (memory); 

　　—查询内存排序数

  1. SQL>SELECT * FROM v$sysstat t WHERE NAME=sorts (disk); 

　　—查询磁盘排序数

  1. --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

　　

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

　　或者从OEM的图形界面中查看，我们可以查看一个视图以获取Oracle的建议值:

  1. 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语句执行在三种模式的次数:

  1. optimal memory size, one-pass memory size, multi-pass memory size:  
  2. 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%);