Methods for viewing the variation trend of SCN headroom

Source: Internet
Author: User
Methods for viewing the variation trend of SCN headroom

This article does not explain the SCN headroom problem.

This article summarizes the script from the Oracle Sr technical engineer.

Reprinted please indicate the source

The first method is to query the smon_scn_time table.

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; 

Method 2: Query AWR report information:

1. how to extract the historical values of a statistic from AWR repository (Doc ID 948272.1)
Copy the script part to the local machine of the two instances in the generated database and name it AWR. SQL

The script is:
Set trimspool onset pages 50000 set lines 132 Set 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 da Tabase 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: 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, S. 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: & amp; end snapshot ID specified: & amp; 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; col Umn 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 9 9999; 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_valuefrom (select snap_id, DBI D, 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 nation X, dba_hist_snapshot Sn, (select instance_number, min (startup_time) startup_time from dba_hist_snapshot where snap_id between: bid and: Eid grou 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 --- the script ends.

2. Run the script in sqlplus and enter

Instances in this workload repository Schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~

Db id inst num dB name inst_name host
* 1163866261 1 rbig5 rbig5 xxx

Enter value for dbid: 1163866261 <=== input instance 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
Specifying a number lists all completed snapshots.

Enter value for num_days: 2 <== enter AWR Sampling days

Specify the begin and end snapshot IDS
Enter value for begin_snap: 1605
Begin snapshot ID specified: 1605 <== enter the starting snapshot number based on the Dialog

Enter value for end_snap: 1639
End snapshot ID specified: 1639 <== enter the end snapshot number based on the dialog box

Search statistic
Search by statistics name. Pressing
Specifying Anything show all statistics.
Enter value for stat_search: callto kcmgas <=== enter the required statistical item: callto kcmgas

Statistic name filter: callto kcmgas

Statistic ID statistic name
4072914524 debug callto kcmgas

Specify the statistics
Enter statistics ID or statistics name.

Enter value for stat_input: 4072914524 <=== ID returned by the input statistics

Finally, a list is returned, such

Snap ID dB ID num elapsed snap started stat Value
1605 1163866261 1 3600 08 Sep 2013 0
1606 1163866261 1 3601 08 Sep 2013 170
1607 1163866261 1 3600 08 Sep 2013 164
1626 1163866261 1 3600 08 Sep 2013 155
1627 1163866261 1 3600 08 Sep 2013 165
1628 1163866261 1 3600 08 Sep 2013 2065 "=" if a similar hop occurs, it indicates that the internal transaction of the database changes dramatically, not caused by dblink.
1636 1163866261 1 3600 09 Sep 2013 145
1637 1163866261 1 3601 09 Sep 2013 174
1638 1163866261 1 3600 09 Sep 2013 156
1639 1163866261 1 3600 09 Sep 2013 142

Please provide your output results as the result of the investigation that the SCN non-External Database dblink causes a hop change.

Method 3: query the V $ archived_log view (provided that the archive mode is enabled for the database)
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 ; 

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.