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