Re: SCN Jumping issue
- From: Andy Klock <andy@xxxxxxxxxxxxxxx>
- To: Oracle-l Digest Users <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 26 May 2016 17:50:19 -0400
On Thu, May 26, 2016 at 1:26 PM, Deepak Sharma <dmarc-noreply@xxxxxxxxxxxxx>
wrote:
Thanks Riyaj, that is very good info.
Couple of questions:
- Is there a way to identify connections that come over using database
links?
Jared has a useful post to assist in tracking down sessions using db links:
http://jkstill.blogspot.com/2010/03/whos-using-database-link.html
- Once all remote DB Links connecting into our DB are identified, is there
a way to find which ones may be causing the most SCN jump/increments ?
I had to do something similar in the recent past and was successful using
the method Riyaj described in (
https://orainternals.wordpress.com/2012/01/19/scn-what-why-and-how/) which
uses the statistic "calls to kcmgas" to determine databases grabbing more
SCNs then the soft limit per hour. I modified the handy script in "How to
Extract the Historical Values of a Statistic from the AWR Repository (Doc
ID 948272.1)" with the following:
select snap_id,
To_char(dbid),
END_INTERVAL_TIME,
sum(stat_value),
round(sum(stat_value)/60/60) calls_per_second,
case when round(sum(stat_value)/60/60) > (1024 * 16)
then '*'
else null
end over_soft_limit
from (
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))
) group by snap_id, To_char(dbid),END_INTERVAL_TIME;
The output looks something similar to:
SQL> @historic_stats_summary
Snap Id TO_CHAR(DBID) Snap Started
SUM(STAT_VALUE) CALLS_PER_SECOND O
37551 1234567890 03 Dec 2015 01:00
60514323 16810 *
37552 1234567890 03 Dec 2015 02:00
63081488 17523 *
37553 1234567890 03 Dec 2015 03:00
58223503 16173
37554 1234567890 03 Dec 2015 04:00
60728626 16869 *
37555 1234567890 03 Dec 2015 05:00
59353861 16487 *
37556 1234567890 03 Dec 2015 06:00
60174274 16715 *
37557 1234567890 03 Dec 2015 07:00
62291810 17303 *
37558 1234567890 03 Dec 2015 08:00
63698373 17694 *
37559 1234567890 03 Dec 2015 09:00
62976679 17494 *
37560 1234567890 03 Dec 2015 10:00
60878969 16911 *
37561 1234567890 03 Dec 2015 11:00
57025885 15841
37562 1234567890 03 Dec 2015 12:00
56923476 15812
37563 1234567890 03 Dec 2015 13:00
56880725 15800
37564 1234567890 03 Dec 2015 14:00
57226022 15896
...
* shows snapshots that have more calls than the 1024*16 soft limit...Note
Riyaj's original warning that this is just an estimate and forgive any bugs
I introduced :)
Good luck. Decreasing SCN headroom is scary.
Andy K
Other related posts:
