Oracle性能优化之命中率下

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

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

　　命中率应大于0.85

　　SQL>select sum(gets-getmisses-usage-fixed)/sum(gets)

　　from v$rowcache;

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

　　计算公式：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');

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

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

　　命中率应大于0.99

　　SQL>select sum(pins-reloads)/sum(pins)

　　from v$librarycache;

　　4)检测回滚段的争用

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

　　SQL>select sum(gets)，sum(waits)，sum(waits)/sum(gets)

　　from v$rollstat;

　　5)检测回滚段收缩次数

　　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;