系统缓冲区的合理设置对系统的性能至关重要。下列语句可以快速查询缓冲区使用率:
1、实例启动以来的平均使用率
select name,rat_hit
from v$bufferpool
where name='NORMAL' order by 2;
2、最近(上次快照以来)的使用率
declare
LOGIC_READS_SAMPLE number;
PHY_READS_SAMPLE number;
LOGIC_READS_BEGIN number;
PHY_READS_BEGIN number;
LOGIC_READS_END number;
PHY_READS_END number;
MAX_SNAP_ID int;
begin
select max(snap_id) into MAX_SNAP_ID from sys.wrh$_bufferpool;
select sum(N_LOGIC_READS),sum(N_PHY_READS) into LOGIC_READS_BEGIN,PHY_READS_BEGIN
from sys.wrh$_bufferpool
where name='NORMAL' and snap_id=MAX_SNAP_ID;
select sum(N_LOGIC_READS),sum(N_PHY_READS) into LOGIC_READS_END,PHY_READS_END
from v$bufferpool where name='NORMAL' ;
LOGIC_READS_SAMPLE = LOGIC_READS_END - LOGIC_READS_BEGIN;
PHY_READS_SAMPLE = PHY_READS_END - PHY_READS_BEGIN;
print ' ';
print '当前系统缓冲区命中率:' || round(LOGIC_READS_SAMPLE*100/(LOGIC_READS_SAMPLE + PHY_READS_SAMPLE),2) || '%';
end;
注:查询最近使用率需要打开AWR快照收集功能
检查是否启用AWR:
SELECT SF_CHECK_AWR_SYS;
说明:0:未启用;1:已启用
初始化AWR系统包:
SP_INIT_AWR_SYS(1);
设置时间间隔(60分钟):
CALL DBMS_WORKLOAD_REPOSITORY.AWR_SET_INTERVAL(60);
手动收集快照:
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
3、DB TIME与ELAPSED之比
下面再介绍一个小脚本查看当前DB TIME和elapsed time之比,即使没有启用过AWR也可以运行。
select round(to_number(count(*)*100)/3600,2) || '%' dbtime_VS_elapsed
from v$active_session_history
where sample_time between dateadd(HH,-1,sysdate) and sysdate;