网站首页  汉语字词  英语词汇  考试资料  写作素材  旧版资料

请输入您要查询的考试资料：

　

标题 Oracle数据库提高命中率及相关优化
内容
    本文是关于Oracle数据库调试与优化方面的文章，主要介绍Oracle数据库中命中率相关的问题，包括不同的算法之间性能的比对。51CTO编辑向您推荐《Oracle数据库调试与性能优化》
    关于Oracle中各个命中率的计算以及相关的调优
    1)Library Cache的命中率:
    .计算公式:Library Cache Hit Ratio = sum(pinhits) / sum(pins)
    SQL>SELECT SUM(pinhits)/sum(pins)
    FROM V$LIBRARYCACHE;
    通常在98%以上，否则，需要要考虑加大共享池，绑定变量，修改cursor_sharing等参数。
    2)计算共享池内存使用率:
    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%间，太小浪费内存，太大则内存不足。
    查询空闲的共享池内存:
    SQL>SELECT * FROM V$SGASTAT
    WHERE NAME = 'free memory' AND POOL = 'shared pool';
    3)db buffer cache命中率:
    计算公式:Hit ratio = 1 - [physical reads/(block gets + consistent gets)]
    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官方文档<<数据库性能优化>>):
    命中率的计算公式为:
    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的命中率
    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)数据缓冲区命中率：
    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';
    这里命中率的计算应该是
    令 x = physical reads direct + physical reads direct (lob)
    命中率 =100 - ( physical reads - x) / (consistent gets + db block gets - x)*100
    通常如果发现命中率低于90%,则应该调整应用可可以考虑是否增大数据缓冲区
    5)共享池的命中率：
    SQL> select sum(pinhits-reloads)/sum(pins)*100 "hit radio" from v$librarycache;
    假如共享池的命中率低于95%,就要考虑调整应用（通常是没使用bind var ）或者增加内存
    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;
    -----------------------------------------------------------------------------
    几个常用的检查语句
    1. 查找排序最多的SQL:
    SQL>SELECT HASH_VALUE, SQL_TEXT, SORTS, EXECUTIONS
    FROM V$SQLAREA
    ORDER BY SORTS DESC;
    2.查找磁盘读写最多的SQL:
    SQL>SELECT * FROM
    (SELECT sql_text,disk_reads "total disk" , executions "total exec",disk_reads/executions "disk/exec" FROM v$sql
    WHERE executions>0 and is_obsolete='N' ORDER BY 4 desc)
    WHERE ROWNUM<11 ;
    3.查找工作量最大的SQL(实际上也是按磁盘读写来排序的):
    SQL>select substr(to_char(s.pct, '99.00'), 2) || '%' load,s.executions executes,p.sql_text
    from(select address,disk_reads,executions,pct,rank() over (order by disk_reads desc) ranking from
    (select address,disk_reads,executions,100 * ratio_to_report(disk_reads) over () pct
    from sys.v_$sql
    where command_type != 47)
    where disk_reads > 50 * executions) s,sys.v_$sqltext p
    where s.ranking <= 5 and p.address = s.address
    order by 1, s.address, p.piece;
    4. 用下列SQL工具找出低效SQL:
    SQL>select executions,disk_reads,buffer_gets,round((buffer_gets-disk_reads)/buffer_gets,2) Hit_radio,round(disk_reads/executions,2) reads_per_run,sql_text
    From v$sqlarea
    Where executions>0 and buffer_gets >0 and (buffer_gets-disk_reads)/buffer_gets<0.8
    Order by 4 desc;
    5、根据sid查看对应连接正在运行的sql
    SQL>select /*+ push_subq */command_type,sql_text,sharable_mem,persistent_mem,
    runtime_mem,sorts,version_count,loaded_versions,open_versions,users_opening,executions,
    users_executing,loads,first_load_time,invalidations,parse_calls,disk_reads,buffer_gets,
    rows_processed,sysdate start_time,sysdate finish_time,’>’||address sql_address,’N’status
    From v$sqlarea
    Where address=(select sql_address from v$session where sid=&sid);
    ***************Oracle 缓冲区命中率低的分析及解决办法******************
    首先确定下面的查询结果:
    1,缓冲区命中率的查询(是否低于90%):
    select round((1 - sum(decode(name,'physical reads',value,0)) /
    (sum(decode(name,'db block gets',value,0)) + sum(decode(name,'consistent gets',value,0))) ),4) *100 || '%' chitrati
    from v$sysstat;
    2,使用率的查询(有无free状态的数据快.):
    select count(*), status from v$bh group by status ;
    3,相关等待事件的查询(是否有相关等待事件)
    select event,total_waits from v$system_event where event in ('free buffer waits');
    4,当前大小(是否已经很大)
    select value/1024/1024 cache_size from v$parameter where name='db_cache_size'
    5,top等待事件分析(Db file scatered read的比率是否大)
    select event ,total_waits,suml
    from
    (select event,total_waits,round(total_waits/sumt*100,2)||'%' suml
    from
    (select event,total_waits from v$system_event ),
    (select sum(total_waits) sumt from v$system_event)
    order by total_waits desc)
    where rownum<6
    and event not like 'rdbms%'
    and event not like 'pmon%'
    and event not like 'SQL*Net%'
    and event not like 'smon%';
    6,db_cache_advice建议值（9i后的新特性,可以根据他更好的调整cache_size）
    select block_size,size_for_estimate,size_factor,estd_physical_reads from v$db_cache_advice;
    说明分析:
    缓冲区命中率(低于90的命中率就算比较低的).
    没有free不一定说明需要增加,还要结合当前cache_size的大小(我们是否还可以再增大,是否有需要增加硬件,增加开销),
    空闲缓冲区等待说明进程找不到空闲缓冲区，并通过写出灰缓冲区，来加速数据库写入器生成空闲缓冲区，当DBWn将块写入磁盘后，灰数据缓冲区将被释放，以便重新使用.产生这种原因主要是:
随便看

　

在线学习网考试资料包含高考、自考、专升本考试、人事考试、公务员考试、大学生村官考试、特岗教师招聘考试、事业单位招聘考试、企业人才招聘、银行招聘、教师招聘、农村信用社招聘、各类资格证书考试等各类考试资料。

　

Copyright © 2002-2024 cuapp.net All Rights Reserved
更新时间：2025/5/20 23:15:04