查看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 on
set pages 50000
set lines 132
set tab off
set feedback off

clear 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 specified


column 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;


prompt
prompt
prompt instances IN this workload repository SCHEMA
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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       host
FROM   dba_hist_database_instance wr,
       v$database cd,
       v$instance ci;


prompt
prompt USING &&dbid FOR DATABASE id
-- 
--  Set up the binds for dbid and instance_number
variable dbid NUMBER;
BEGIN
    :dbid := &dbid;
END;
/
--  Error reporting
whenever 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 display
set 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';
prompt
prompt
prompt specify the NUMBER OF days OF snapshots TO cho

查看scnheadroom变化趋势的几种方法(一)