Re: Question on NEXT_CHANGE#

Thanks for the points you mentioned on kcmgas and behaviour of it, What are the possible reasons for the increments in SCN which may not recorded and not visible to us.
I also viewed MOS document 1388639.1,

AWR data -
*The statistic "calls to kcmgas" gives an indication of how often this instance has incremented the database SCN itself, as opposed to an SCN increment triggered by some other action such as communication over a database link.*

Unfortunately, my instance was down between 5PM to 6PM on 27th Dec, Hence i cant take AWR report. But still am going to test comparing the "calls to kcmgas" with select queries with AWR reports and queries to look closely.

SQL> select sequence#,first_change#,first_time,next_change#,next_time from v$archived_log where sequence# > 30 and dest_id=1;  SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME

---------- ------------- -------------------- ------------
--------------------
        31       2432612 27-DEC-2013 17:25:34      2432676 27-DEC-2013
17:25:51
        32       2432676 27-DEC-2013 17:25:51      2435036 27-DEC-2013
18:20:12
        33       2435036 27-DEC-2013 18:20:12      2455760 27-DEC-2013

*Thread 0001, Seq# 0000000031, SCN 0x000000251e64-0x000000251ea4(2432612 - 2432676)*

SQL> select min(scn),max(scn) from v$logmnr_contents;   MIN(SCN) MAX(SCN)
---------- ----------

   2432612 2432672
SQL>
*For Sequence 31: High_scn shows 2432676 but the max_scn is 2432672 - 4 SCN vary*

*Thread 0001, Seq# 0000000032, SCN 0x000000251ea4-0x0000002527dc(2432676 - 2435036)*
SQL> select min(scn),max(scn) from v$logmnr_contents;   MIN(SCN) MAX(SCN)
---------- ----------

   2432679 2435029
SQL> *For sequence 32: high_scn shows to be 2435036 but the max_scn shows from logminer is 2435029 - 7 SCN vary. *

Bottom line, For every sequence the gap of SCN/Increment SCN can occur any where of sequence.. it can be beginning or middle or even at the ending and we can't track them any where even in views/x$ about the missing SCN where exactly it is spending.

I'm pending with tests on "calls to kcmggas" further.

Welcome if someone wants to add your views in case. Thank you all for valuable information.

• Nassyam Basha

On Tue, Dec 31, 2013 at 1:26 AM, Riyaj Shamsudeen < riyaj.shamsudeen_at_gmail.com> wrote:

> AFAIK, there are numerous actions that can allocate new SCNs, SCNs can be
> allocated with no redo records as well, and multiple redo records can be
> created at an SCN. Majority of those actions (that do not have redo
> associated) centered around control file accesses (as Jonathan pointed
> out). As a test, watch 'calls to kcmgas' statistics of a session and
> execute 'select * from v$database' from that watched session. You will see
> that every execution will increase that statistics.
>
> BTW, kcmgas is the internal function to allocate new SCN. Another test
> would be compare the increase in the 'calls to kcmgas' statistic and number
> of unique SCNs in the archivelog file. In RAC, SCN allocation rate is
> higher to maintain cluster coherency.
>
> SCN allocation rate increases sharply during hot backup too.
>
> Cheers
>
> Riyaj Shamsudeen
> Principal DBA,
> Ora!nternals - http://www.orainternals.com - Specialists in Performance,
> RAC and EBS
> Blog: http://orainternals.wordpress.com/
> Oracle ACE Director and OakTable member <http://www.oaktable.com/>
>
> Co-author of the books: Expert Oracle Practices<http://tinyurl.com/book-expert-oracle-practices/>
> , Pro Oracle SQL, <http://tinyurl.com/ahpvms8><http://tinyurl.com/ahpvms8>Expert
> RAC Practices 12c. <http://tinyurl.com/expert-rac-12c> Expert PL/SQL
> practices <http://tinyurl.com/book-expert-plsql-practices>
>
> <http://tinyurl.com/book-expert-plsql-practices>
>
>
>
> On Mon, Dec 30, 2013 at 9:00 AM, Phil Jones <phil_at_phillip.im> wrote:
>
>> All,
>>
>> I think this all boils down to the usual question we're all asked when
>> designing software/DB schemas.
>>
>> Do you want the numbers to be unique (with possible gaps) or truly
>> sequential?
>>
>> The database is working correctly, probably using the missing scn numbers
>> internally - it doesn't matter, but it would be interesting to know why.
>>
>> Cheers,
>>
>> Phil
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>

-- 
Thanks & Regards,
Nassyam Basha.
www.oracle-ckpt.com

--
http://www.freelists.org/webpage/oracle-l