首页 > 代码库 > 查看scn headroom变化趋势的几种方法
查看scn headroom变化趋势的几种方法
查看scn headroom变化趋势的几种方法
scn headroom问题,本文不做解释。
第一个方法:查询smon_scn_time表获得。
第二个方法:查询awr报告的信息:
1. 通过How to extract the historical values of a statistic from AWR Repository (Doc ID 948272.1)
将Script 部分复制到您生成数据库两个实例本地,命名为 例如 awr.sql
该脚本为:
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视图(前提是数据库开启归档模式)
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 choose FROM prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ prompt entering the NUMBER OF days (n) will result IN the most recent prompt (n) days OF snapshots being listed. pressing without prompt specifying a NUMBER LISTS ALL completed snapshots. prompt prompt set 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_days FROM sys.dual; set heading ON; -- -- List available snapshots break 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 lvl FROM dba_hist_snapshot s, dba_hist_database_instance di WHERE 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 compared prompt prompt prompt specify the BEGIN AND END SNAPSHOT ids prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ prompt BEGIN SNAPSHOT id specified: &&begin_snap prompt prompt END SNAPSHOT id specified: &&end_snap prompt -- -- 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 -- prompt prompt prompt search statistic prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ prompt search BY STATISTICS name. pressing without prompt 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_search FROM 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, name FROM v$sysstat WHERE Upper(name) LIKE Trim(Upper('%&stat_search%')) ORDER BY class, name / -- -- Ask for the statistics variable stat_filter_id NUMBER variable stat_filter_name VARCHAR2(64) prompt prompt prompt specify the STATISTICS prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ prompt enter STATISTICS id OR STATISTICS name. prompt BEGIN 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_input column 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 999999999999 column 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_VALUE FROM (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 dbid undefine num_days undefine begin_snap undefine end_snap undefine stat_id undefine stat_search undefine stat_filter_name undefine stat_filter_id undefine 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 ;
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。