Read Consistency: Why You Need It, How to Avoid It.

This presentation from the UKOUG 2024 conference looks at the Oracle database's read consistency mechanism.  It is one of the reasons you buy Oracle.  It ensures that readers do not block writers and vice versa.  

However, this mechanism, although very robust, can be expensive.  This session looks at strategies to avoid doing it in the first place.

In the Oracle database, the undo mechanism ensures that multiple versions of the same data, all at different points in time, can be concurrently queried in the database without sessions blocking each other.  Thus the database can provide read-consistent views of the data and enable non-blocking queries. 

This mechanism has been at the very heart of the Oracle database for many years.  It is very robust.  It is one of the reasons to buy Oracle, and other database platforms have either followed it to implement similar mechanisms, or they no longer exist!

However, it comes at a price. It consumes memory, CPU, and time. It can lead to contention issues when different transactions, in different high-intensity sessions, update different rows in the same data block. 

This presentation explains how the read consistency mechanism works, and then discusses some approaches that can avoid the need to do read consistency processing, illustrated with examples taken from real life.  Some of these require additional licencing, and some do not.