查看scn headroom變化趨勢的幾種方法，scnheadroom

查看scn headroom變化趨勢的幾種方法，scnheadroom
查看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 xxx 
m 


Enter value for dbid: 1163866261 《=====輸入執行個體ID 
USING 1163866261 FOR DATABASE id 




specify 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 without 
specifying a NUMBER LISTS ALL completed snapshots. 




Enter value for num_days: 2 <===輸入AWR採樣天數 




specify the BEGIN AND END SNAPSHOT ids 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
Enter value for begin_snap: 1605 
BEGIN SNAPSHOT id specified: 1605 《===根據對話輸入起始snapshot 序號 


Enter value for end_snap: 1639 
END SNAPSHOT id specified: 1639《===根據對話輸入結束snapshot 序號 








search statistic 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
search BY STATISTICS name. pressing without 
specifying anything show ALL STATISTICS. 
Enter value for stat_search: calls to kcmgas 《======輸入需要顯示的統計項： calls to kcmgas 


Statistic Name Filter: calls to kcmgas 


Statistic ID Statistic Statistic Name 
-------------- ---------- ---------------------------------------------------------------- 
4072914524 DEBUG calls to kcmgas 




specify the STATISTICS 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
enter STATISTICS id OR STATISTICS name. 


Enter value for stat_input: 4072914524 《======輸入統計項返回的ID 






最後，將返回一個列表，例如 


Inst 
Snap Id DB Id Num Elapsed Snap Started Stat Value 
--------- ------------ ------ ------- ------------------ ------------- 
1605 1163866261 1 3600 08 Sep 2013 00:00 0 
1606 1163866261 1 3601 08 Sep 2013 01:00 170 
1607 1163866261 1 3600 08 Sep 2013 02:00 164 
。。。 
1626 1163866261 1 3600 08 Sep 2013 21:00 155 
1627 1163866261 1 3600 08 Sep 2013 22:00 165 
1628 1163866261 1 3600 08 Sep 2013 23:00 2065《===如果有類似跳變發生,則表示資料庫內部交易產生的劇烈變化，非DBLINK造成。 
。。。 
1636 1163866261 1 3600 09 Sep 2013 07:00 145 
1637 1163866261 1 3601 09 Sep 2013 08:00 174 
1638 1163866261 1 3600 09 Sep 2013 09:00 156 
1639 1163866261 1 3600 09 Sep 2013 10:00 142 

請提供您的輸出結果來作為SCN 非外部資料庫DBLINK造成跳變的調查結果。 



第三個方法：查詢v$archived_log視圖（前提是資料庫開啟歸檔模式）

set numwidth 17 set pages 1000 alter session set nls_date_format='DD/Mon/YYYY HH24:MI:SS'; SELECT tim, gscn, round(rate), round((chk16kscn - gscn)/24/3600/16/1024,1) "Headroom" FROM ( select tim, gscn, rate, (( ((to_number(to_char(tim,'YYYY'))-1988)*12*31*24*60*60) + ((to_number(to_char(tim,'MM'))-1)*31*24*60*60) + (((to_number(to_char(tim,'DD'))-1))*24*60*60) + (to_number(to_char(tim,'HH24'))*60*60) + (to_number(to_char(tim,'MI'))*60) + (to_number(to_char(tim,'SS'))) ) * (16*1024)) chk16kscn from ( select FIRST_TIME tim , FIRST_CHANGE# gscn, ((NEXT_CHANGE#-FIRST_CHANGE#)/ ((NEXT_TIME-FIRST_TIME)*24*60*60)) rate from v$archived_log where (next_time > first_time) ) ) order by 1,2 ;