A Brief History of Oracle Time

Recently, while in my vehicle on the way to work I was lulled into a deeper-than-usual thought stream thanks to a clog of traffic. It was then that I entered into an Oracle cosmological musing. I was pondering the notion of time inside Oracle. Outside of the Oracle database the idea of concurrency, and thusly simultaneity, within the database appear abundantly unambiguous. That is, from the outside different transactions within the same database can affect change on the database simultaneously. In the world outside of Oracle the idea of simultaneity outside of Oracle is relative. My chronicle on the time an event occurs, such as typing this article, might differ substantially from another’s due to my position, motion, etc. We can thank Albert Einstein for this discovery. However, Oracle’s notion of its time and simultaneity is a bit different.

Consider the fact that Oracle only chronicles changes to the database. Yes, even changes created by queries, e.g. delayed block cleanout. Therefore, we are only concerned with Oracle time as it relates to modifications. It we were to rewind Oracle time, via a database restoration, and then roll Oracle time forward we would only see the affects of change being replayed.

How is change affected in the database? We know that all transactions contend for the lone redo allocation latch, per redo thread, to affect their respective changes. Until the redo allocation latch has been acquired a transaction cannot produce change and therefore cannot make any contribution to Oracle time. Once the redo allocation latch has been acquired a transaction can then contribute to Oracle time, its position in Oracle time henceforth solidified. After getting some external time on a redo copy latch and copying its change into the redo buffer the transaction has made an indelible contribution (barring instance failure) to time in Oracle. Although the change is forever imprinted in Oracle time it does not mean the change will be perpetually visible, e.g. an uncommitted change during an instance crash.

How is Oracle time measured? Every change made to the database is chronicled using a system change number (SCN). The SCN is Oracle’s time quantum. We cannot recover to a position in Oracle time to a more granular point than a specific SCN. Is an SCN unique to a particular change? Not necessarily. Many changes from distinct transactions can comprise a single SCN. So, simultaneity in Oracle can be defined as the set of all redo records that are recorded for a single SCN. Therefore, simultaneity in Oracle time is not relative as your change either occurred at an absolute point in Oracle time T or it did not.

How does Oracle map external time to its internal time? What are we instructing Oracle to do when we tell it to recover to a particular point in external time? You can view external time as an index into Oracle time. Each redo record has an external time associated with it. If I want Oracle to recover to an external point in time E, it resolves E to an SCN such that this SCN has the highest external timestamp that precedes E.

Notice, Oracle time is not a continuum. It cannot be arbitrarily divided into equal units. Relativistic time can be arbitrarily divided into seconds, minutes, hours, days, etc. Oracle time is discontinuous because it is necessarily divided along the bounds of each indivisible SCN. Oracle time is analogous to a movie being played at your local theatre. Each frame on the tape can be the analog to an Oracle SCN. For two successive frames many changes on the movie screen are possible. Some frames can contain more change (altered "pixels") than the previous frame. For example, a man pauses to think versus children riding bikes and people crossing streets. So the next time you sit down with a tub of popcorn at the movies whisper to your friend that what he/she is observing is analogous to Oracle time. Be prepared for a strange look in return :)