查看scn headroom变化趋势的几种方法 scn headroom问题，本文不做解释。 本文为自己的总结，脚本来自于oracle sr技术工程师。 转载请注明出处http://blog.csdn.net/msdnchina/article/details/38404501 第一个方法：查询smon_scn_time表获得。conn / as sysdb
查看scn headroom变化趋势的几种方法
scn headroom问题，本文不做解释。本文为自己的总结，脚本来自于oracle sr技术工程师。
转载请注明出处http://blog.csdn.net/msdnchina/article/details/38404501
第一个方法：查询smon_scn_time表获得。conn / as sysdba
set numwidth 17
set pages 1000
alter session set nls_date_format='dd/mon/yyyy hh24:mi:ss';
with t1 as(
select time_dp , 24*60*60*(time_dp - lag(time_dp) over (order by time_dp)) timediff,
scn - lag(scn) over(order by time_dp) scndiff
from smon_scn_time
)
select time_dp , timediff, scndiff,
trunc(scndiff/timediff) rate_per_sec
from t1
order by 1;
第二个方法：查询awr报告的信息：
1. 通过how to extract the historical values of a statistic from awr repository (doc id 948272.1)
将script 部分复制到您生成数据库两个实例本地，命名为 例如 awr.sql
该脚本为：set trimspool onset pages 50000set lines 132set tab offset feedback offclear break compute;repfooter off;ttitle off;btitle off;set timing off veri off space 1 flush on pause off termout on numwidth 10;set echo off feedback off pagesize 50000 linesize 1000 newpage 1 recsep off;set trimspool on trimout on;-- -- request the db id and instance number, if they are not specifiedcolumn instt_num heading inst num format 99999;column instt_name heading instance format a12;column dbb_name heading db name format a12;column dbbid heading db id format a12 just c;column host heading host format a20;promptpromptprompt instances in this workload repository schemaprompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~select distinct ( case when cd.dbid = wr.dbid and cd.name = wr.db_name and ci.instance_number = wr.instance_number and ci.instance_name = wr.instance_name then '* ' else ' ' end ) || wr.dbid dbbid, wr.instance_number instt_num, wr.db_name dbb_name, wr.instance_name inst_name, wr.host_name hostfrom dba_hist_database_instance wr, v$database cd, v$instance ci;promptprompt using &&dbid for database id-- -- set up the binds for dbid and instance_numbervariable dbid number;begin :dbid := &dbid;end;/-- error reportingwhenever sqlerror exit;variable max_snap_time char(10);declare cursor cidnum is select 'x' from dba_hist_database_instance where dbid = :dbid; cursor csnapid is select to_char(max(end_interval_time), 'dd/mm/yyyy') from dba_hist_snapshot where dbid = :dbid; vx char(1);begin -- check database id/instance number is a valid pair open cidnum; fetch cidnum into vx; if cidnum%notfound then raise_application_error(-20200, 'database/instance ' || :dbid || '/' || ' does not exist in dba_hist_database_instance'); end if; close cidnum; -- check snapshots exist for database id/instance number open csnapid; fetch csnapid into :max_snap_time; if csnapid%notfound then raise_application_error(-20200, 'no snapshots exist for database/instance ' ||:dbid ||'/'); end if; close csnapid;end;/whenever sqlerror continue;-- -- ask how many days of snapshots to displayset termout on;column instart_fmt noprint;column inst_name format a12 heading 'instance';column db_name format a12 heading 'db name';column snap_id format 99999990 heading 'snap id';column snapdat format a18 heading 'snap started' just c;column lvl format 99 heading 'snap|level';promptpromptprompt specify the number of days of snapshots to choose fromprompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~prompt entering the number of days (n) will result in the most recentprompt (n) days of snapshots being listed. pressing withoutprompt specifying a number lists all completed snapshots.promptpromptset heading off;column num_days new_value num_days noprint;select 'listing ' || decode(nvl('&&num_days', 3.14), 0, 'no snapshots', 3.14, 'all completed snapshots', 1, 'the last day''s completed snapshots', 'the last &num_days days of completed snapshots' ), nvl('&&num_days', 3.14) num_daysfrom sys.dual;set heading on;-- -- list available snapshotsbreak on inst_name on db_name on host on instart_fmt skip 1;ttitle off;select to_char(s.startup_time, 'dd mon at hh24:mi:ss') instart_fmt, di.instance_name inst_name, di.db_name db_name, s.snap_id snap_id, to_char(s.end_interval_time, 'dd mon yyyy hh24:mi') snapdat, s.snap_level lvlfrom dba_hist_snapshot s, dba_hist_database_instance diwhere s.dbid = :dbid and di.dbid = :dbid and di.dbid = s.dbid and di.instance_number = s.instance_number and di.startup_time = s.startup_time and s.end_interval_time >= decode(&num_days, 0, to_date('31-jan-9999', 'dd-mon-yyyy' ), 3.14, s.end_interval_time, to_date(:max_snap_time, 'dd/mm/yyyy') - ( &num_days - 1 ))order by db_name, instance_name, snap_id;clear break;ttitle off;-- -- ask for the snapshots id's which are to be comparedpromptpromptprompt specify the begin and end snapshot idsprompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~prompt begin snapshot id specified: &&begin_snappromptprompt end snapshot id specified: &&end_snapprompt-- -- set up the snapshot-related binds-- variable bid number;variable eid number;begin :bid := &begin_snap; :eid := &end_snap;end;/prompt-- -- ask for statistics name filter-- promptpromptprompt search statisticprompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~prompt search by statistics name. pressing withoutprompt specifying anything show all statistics.set heading off;column stat_search new_value stat_search noprint;select 'statistic name filter: ' || nvl('&&stat_search', '%'), nvl('&&stat_search', '%') stat_searchfrom sys.dual;set heading on;column stat_id heading statistic id format 9999999999999;column name heading statistic name format a64;column class_name heading statistic class format a10;select stat_id, ( case when class = 1 then 'user' when class = 2 then 'redo' when class = 4 then 'enqueue' when class = 8 then 'cache' when class = 16 then 'os' when class = 32 then 'rac' when class = 40 then 'rac-cache' when class = 64 then 'sql' when class = 72 then 'sql-cache' when class = 128 then 'debug' else to_char(class) end ) class_name, namefrom v$sysstatwhere upper(name) like trim(upper('%&stat_search%'))order by class, name/-- -- ask for the statisticsvariable stat_filter_id numbervariable stat_filter_name varchar2(64)promptpromptprompt specify the statisticsprompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~prompt enter statistics id or statistics name.promptbegin select to_number('&&stat_input') into :stat_filter_id from dual;exception when invalid_number then :stat_filter_name := '&stat_input';end;/prompt statistics specified : &&stat_inputcolumn end_interval_time heading 'snap started' format a18 just c;column dbid heading 'db id' format a12 just c;column instance_number heading 'inst|num' format 99999;column elapsed heading 'elapsed' format 999999;column stat_value heading 'stat value' format 999999999999column stat_name heading 'stat name' format a64 just l;select snap_id, to_char(dbid) dbid, instance_number, elapsed, to_char(end_interval_time, 'dd mon yyyy hh24:mi') end_interval_time, --stat_name, ( case when stat_value > 0 then stat_value else 0 end ) stat_valuefrom (select snap_id, dbid, instance_number, elapsed, end_interval_time, stat_name, ( stat_value - lag (stat_value, 1, stat_value) over ( partition by dbid, instance_number order by snap_id) ) as stat_value from (select snap_id, dbid, instance_number, elapsed, end_interval_time, stat_name, sum(stat_value) as stat_value from (select x.snap_id, x.dbid, x.instance_number, trunc(sn.end_interval_time, 'mi') end_interval_time, x.stat_name, trunc(( cast(sn.end_interval_time as date) - cast(sn.begin_interval_time as date) ) * 86400) elapsed, ( case when ( x.stat_name = :stat_filter_name or x.stat_id = :stat_filter_id ) then x.value else 0 end ) as stat_value from dba_hist_sysstat x, dba_hist_snapshot sn, (select instance_number, min(startup_time) startup_time from dba_hist_snapshot where snap_id between :bid and :eid group by instance_number) ms where x.snap_id = sn.snap_id and x.dbid = sn.dbid and x.dbid = :dbid and x.snap_id between :bid and :eid and sn.startup_time = ms.startup_time and sn.instance_number = ms.instance_number and x.instance_number = sn.instance_number and ( x.stat_name = :stat_filter_name or x.stat_id = :stat_filter_id )) group by snap_id, dbid, instance_number, elapsed, end_interval_time, stat_name));undefine dbidundefine num_daysundefine begin_snapundefine end_snapundefine stat_idundefine stat_searchundefine stat_filter_nameundefine stat_filter_idundefine stat_input ---该脚本结束。2. 在sqlplus中运行该脚本，并根据您系统实际情况输入instances in this workload repository schema~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~db id inst num db name inst_name host------------ -------- ------------ ---------------- --------------------* 1163866261 1 rbig5 rbig5 xxxmenter value for dbid: 1163866261 《=====输入实例idusing 1163866261 for database idspecify the number of days of snapshots to choose from~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~entering the number of days (n) will result in the most recent(n) days of snapshots being listed. pressing withoutspecifying a number lists all completed snapshots.enter value for num_days: 2 first_time) ) ) order by 1,2 ;