Oracle Read Consistency

Oralce use before image of data in undo segments to provide read consistency.

Read consistency ensures the following:

.A statement sees data that is consistent with a point in time for the period of its execution

.Users reading data don't wait for other read or write operations to the same data

.Userd writing to datra don't wait for other readers of the same data

.Users writing to data waiting for only other write operations in concurrent transactions that update identical rows

 

This is a very important point to know why select SQL statement causes writing disk operations, which brings great resource cost for SQL statement.

 

Oracle provides statement-level read consitency by default. To do this, Oracle determines the current system change number(SCN) a the start of select statement. Oracle then ensures that the statement don't process any uncommitted changes made before the SCN.

 

Oracle also provides Transasction-level read consistency that allows you to use a single transaction to run multiple queries that are read consistent for the same point in time.

 

To get transaction-level read consistency for transaction that has multiple queries and performs no updating, Set the transaction read-only by the following comand at the start of the transaction:

 

Set Transaction Read Only;

 

To get transaction-level read consistency for transaction that has multiple queries and performs DML, Another command should be used at the start of the transaction:

 

Set Transaction Isolation level Serializable;

 

Serializable command can impair system performance.