For many people working with Oracle database, SCN (System Change Number) is a topic that interests them greatly – but despite that it’s a fascinating topic, it’s confusing as well. In this article we will learn the nitty-gritty of SCN: what it is, where it is used, and how it works.
It’s worth mentioning that this is not and cannot be complete coverage of every detail about SCN. With that said, let’s start.
SCN- Why do we need it?
Assume it’s the end of the month and its payday. You are in the Payroll group and, with appropriate authority, have issued a query to pull the currently salary calculation of all the employees. Numbers are flowing across the screen, everything is going great.
Suppose, while you are looking at the output, a colleague starts a new pay run and that batch job clears the current calculation summary. Things would certainly become confusing if your output suddenly showed ‘0’ for the new rows coming out.
Indeed, for this scenario, you would probably expect the output to reflect the ‘state of the database’ – the contents of each row – the way it existed at the moment you issued your query.
One of the guarantees of the Oracle database is that “there are no dirty reads of user data”. If necessary, Oracle will rebuild each row to the stable state at instant in time when the query was issued.
Oracle does it nicely and very accurately through a “timer” of its own, known as the System Change Number, AKA SCN.
We will discuss these aspects of the SCN:
- The number itself
- The number generator
- Places where the number is stored
So let’s begin!
Building blocks for understanding SCN
In order to investigate SCN, several terms must be understood. Although these definitions are well documented, it is worth repeating them here:
From the Concepts guide of Oracle online Documentation:
“A transaction is a logical, atomic unit of work that contains one or more SQL statements. A transaction groups SQL statements so that they are either all committed, which means they are applied to the database, or all rolled back, which means they are undone from the database. Oracle Database assigns every transaction a unique identifier called a transaction ID”.
More simply: when we start a transaction we initiate a set of changes. These must be either completed in totality or must not be completed at all. As the transactions occur in the database, the users reading any area that has changed must not be affected from any side effects which can alter their results.
In order for the transactions to be working correctly in any database, four ACID rules must be followed by the database. These are:
So where does SCN fit in all of this? And the answer of that question is that using SCN only Oracle maintains data consistency. This is done to ensure that any point of time, there is no discrepancy in the results that are shown to the others when one user is changing something, and vice-versa.
In order to summarize, in Oracle, the principle of “Readers do not wait for writers and writers don’t need for readers” is completely and truly followed. In order to do so, it is of the utmost importance that the data, which is currently undergoing any sort of change, must not be available to anyone except the person who is making those changes. For transactions, this is required to maintain the integrity of the data. Three things can occur which can disrupt this integrity – Dirty Read, Fuzzy Read and Phantom Read. To ensure that there won’t be any sort of integrity issues in these transactions, different levels of Isolations are available. These are:
- Read Uncommitted
- Read Committed
- Repeatable Read
Transaction Isolation levels
From these, Oracle offers Read Committed as the default isolation level, making sure that there is no possibility of a user seeing the changes made by another user which are not yet committed. There must not be any read over that data which is marked as “dirty” and there must be a mechanism robust enough to make all this possible.
To make this possible, SCN plays a vital role.
SCN, an Introduction
System Commit Number may be understood as Oracle’s representation of time. Oracle uses SCN for controlling consistency, for performing recovery and to order the change vectors in the redo.
The SCN is found in many locations – both in memory and on the disk. Different locations are used to help the database system identify various states of the database. For example, in some locations it is used to denote the transaction’s completion status and whether it is active or committed.
Contrary to popular belief, it does not get generated only at the time of commit, though that’s what the name itself suggests. SCN is there all the time in the database, representing a time portion for the operation which is happening at that instant of time. It is not completely inaccurate to say that SCN doesn’t get generated with a commit, it does – but that’s not the sole way SCN is generated.
The SCN is a two-part number that is generated on request, in response to an event. It is somewhat like the date and time derived from a calendar and clock combination; the clock changes quickly and only when it has gone through the complete 24 hour cycle is the overflow – the calendar – changed. However, the event that changes the clock is the ‘pendulum’ and is regular, whereas the event that changes the SCN is a call to one specific internal function.
Similar to a calendar/clock timestamp, the value may be recorded in a variety of different places, each for a different use.
One real-world example similar to an SCN is an airport clock. Two people enter an airport at the same time and may note the clock time. That moment gives us ‘an entry SCN’ which happens to be the same for both people. One person gets a cart for baggage before checking in, whereas the other proceeds directly to the check-in counter. When the seats are assigned, each person may get a different ‘check-in SCN’ since the times are slightly different. A ‘boarding SCN’ may be assigned to indicate when each has boarded, but both get the same ‘take-off SCN’.
Like the combination of calendar and clock, the SCN is guaranteed to be increasing under normal operation. There is no guarantee that the number will be sequential (missing numbers).
So where is this information about SCN used in the Oracle database? Well, almost everywhere. Just like we associate Time with every activity of ours, SCN is also associated with every part of database’s functionality. For example, when you try to select data from a table, SCN is used to confirm that whether that data is consistent or not. SCN is also found in the data block’s Transaction Headers. This SCN would represent the time when the transaction started and when it was committed. Similarly, for each change performed, an entry in the redo log is maintained and for each of these entries, SCN is used to represent the time of transaction’s occurrence.
Read consistency uses SCN to decide how long it has to apply Undo over the dirty buffer so that the read consistent data request for a session can be completed. And as is well known, SCN is incremented with every commit operation.
SCN Format and Structure
SCN is a huge number with two components to it: SCN Base & SCB Wrap.
SCN is a 6 byte (48 bits) number. Out of these 48 bits, SCN_WRAP is a 16 bit (2 Bytes) number and SCN_BASE is a 32 bit (4 Bytes) number. Both BASE & WRAP are used to control the SCN’s increment and to ensure that the database won’t run out of it. SCN_WRAP is incremented by 1 when SCN_BASE reaches the value of 4 Billion and SCN_BASE becomes 0.
From Oracle Version 12c, the SCN number is an 8 byte number.
So how do we see the current SCN value? The easiest way is to query the view V$DATABASE. Have a look:
SQL> select current_scn from V$database; CURRENT_SCN ----------- 1123790
As we can see, that SCN is displayed as a number. This is good because it makes the use of SCN easy for us in our statements performing recovery, flashback, etc. If we want, we can convert SCN to a Hexadecimal value as well:
SQL> select to_char('1123790','xxxxxxxx') scn_hex from dual; SCN_HEX --------- 1125ce
Here is an example of the output from the same view accessed couple of times:
SQL> select current_scn from V$database; CURRENT_SCN ----------- 1178883 SQL> / CURRENT_SCN ----------- 1178885 SQL> / CURRENT_SCN ----------- 1178886
Interestingly, this increase in the SCN value shows an important aspect of SCN. We can see that with each execution, there is an increase in the count of the SCN. By querying the view V$DATABASE, we are actually causing the jump in the SCN number.
The easiest way to see both of these values is from an internal table owned by SYS user- SMON_SCN_TIME. The following is an output from the same (11204).
SQL> select SCN_wrp, SCN_bas, SCN from smon_SCN_time where rownum < 3; SCN_WRP SCN_BAS SCN ---------- ---------- ---------- 0 998222 998222 0 998406 998406
This table contains the entries of the SCNs generated. It stores the data in roughly 5-minute increments and holds 5 days’ worth of data. This means the table contains approximately 1440 records. The exact number of records will vary slightly since the storage increment is not exactly 5 minutes.
SQL> select count(*) from SMON_SCN_TIME; COUNT(*) ---------- 1494
In versions of Oracle prior to 10g, the time mapping of SCN with time was +/- 5 minutes but from 10g onwards, this is changed to +/- 3 seconds. As this is stores in an internal table, Oracle doesn’t allow access to the information from this table directly. To access it, APIs are provided. One such API is from the package DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER, which can be used to access the sequence number from this table. An example of this is given below (thanks to Tom Kyte for the query):
SQL> l select SCN_to_timestamp(SCN) ts, min(SCN), max(SCN) from ( select dbms_flashback.get_system_change_number()-level SCN from dual connect by level <= 100 ) group by SCN_to_timestamp(SCN) * order by SCN_to_timestamp(SCN) SQL> / TS MIN(SCN) MAX(SCN) --------------------------------------------------------------------------- ---------- ---------- 06-MAY-16 05.22.04.000000000 PM 1245323 1245323 06-MAY-16 05.22.07.000000000 PM 1245324 1245324 06-MAY-16 05.22.10.000000000 PM 1245325 1245325 06-MAY-16 05.22.13.000000000 PM 1245326 1245326 06-MAY-16 05.22.16.000000000 PM 1245327 1245327 06-MAY-16 05.22.19.000000000 PM 1245328 1245328 06-MAY-16 05.22.22.000000000 PM 1245329 1245329 06-MAY-16 05.22.25.000000000 PM 1245330 1245330 06-MAY-16 05.22.28.000000000 PM 1245331 1245331 06-MAY-16 05.22.31.000000000 PM 1245332 1245332 06-MAY-16 05.22.34.000000000 PM 1245333 1245333 06-MAY-16 05.22.37.000000000 PM 1245334 1245334 06-MAY-16 05.22.40.000000000 PM 1245335 1245335 06-MAY-16 05.22.43.000000000 PM 1245336 1245336 06-MAY-16 05.22.46.000000000 PM 1245337 1245337 06-MAY-16 05.22.49.000000000 PM 1245338 1245338 06-MAY-16 05.22.52.000000000 PM 1245339 1245339
How can we map SCN with Timestamp in versions prior to 10g?
It’s important to remember that this table doesn’t show why there would be any increase in the rate of increase in the SCN numbers. With a little formatting, it is possible to have an idea of the SCN numbers generated but the source of their growth won’t be evident from this table.
Another way to check this is from the view V$LOG_HISTORY. The view contains the SCN in the form of the columns FIRST_CHANGE# and NEXT_CHANGE# and we can see through these two columns the amount of SCNs generated in the database over a period of time. The “first_change# is the lowest SCN that appears in the archived log file at a given sequence number of this thread. The “next_change#” is the lowest SCN that appears in the next archived log file.
SQL> select thread#, first_change#,next_change# from V$log_history; THREAD# FIRST_CHANGE# NEXT_CHANGE# ---------- ------------- ------------ 1 925702 958823 1 958823 959634 1 959634 972579 1 972579 993714 1 993714 1020785 1 1020785 1023738 1 1023738 1023873 1 1023873 1023995
As with SMON_SCN_TABLE it’s not possible to find the source of the increase in the generation of the SCN numbers from this table view. Still, you can use this view in the single instance as well as in an RAC environment.
SCN Increases Using a Sequence?
By now it should be pretty evident that SCN appears to be a number that’s constantly increasing. Interestingly, even though it’s a number, Oracle doesn’t use any sequence to increase it but uses internal functions instead. For example, to increase the SCN_BASE, the function used internally is KCMGAS(Get and Advance SCN). This function is called each time a new SCN is requested and the call for this function is increased. Similarly to this function, KCMGCS (Get Current SCN) is used to get the current SCN and the call used for it. These function calls can be seen from the V$sysstat view. A description of these statistics can be found in the 12.1 Reference guide.
Let’s see how these calls link with the SCN generation. We are using two sessions here – one to see the calls in the view V$SYSSTAT view, and the other session to pull the SCN.
Session -1 SQL> l 1* select current_SCN from V$database SQL> / CURRENT_SCN ----------- 698815 SQL>
For this SCN, these were the values for V$sysstat:
SQL> l 1 select name,value from V$sysstat 2* where name like '%calls to%' SQL> / NAME VALUE ---------------------------------------------------------------- ---------- calls to kcmgcs 427 calls to kcmgrs 0 calls to kcmgas 7272 calls to get snapshot SCN: kcmgss 159790 SQL>
Let’s issue a query to see the current SCN in session 1:
SQL> l 1* select current_SCN from V$database SQL> / CURRENT_SCN ----------- 698815 SQL> / CURRENT_SCN ----------- 698889 SQL>
And here is the result of the second session 2:
SQL> / NAME VALUE ---------------------------------------------------------------- ---------- calls to kcmgcs 427 calls to kcmgrs 0 calls to kcmgas 7272 calls to get snapshot SCN: kcmgss 159790 SQL> / NAME VALUE ---------------------------------------------------------------- ---------- calls to kcmgcs 427 calls to kcmgrs 0 calls to kcmgas 7278 calls to get snapshot SCN: kcmgss 159905 SQL>
We can see that the calls to the KCMGAS has increased to 7278 from the last value, 7272. As Oracle doesn’t use a sequence to increase the number, there is no guarantee that the SCN number will always be increased in the same order.
In this article, we have had a look at what the SCN is, how to view it and what the requirements for it are. In the next article, we will see what different types of SCNs are available and how they are used in the database. Stay tuned!