Read Consistency across Statements

A recent blog posting on the SET TRANSACTION READ ONLY reminded me of a note
that I had written in 2004. [note the references to AUM because most of my databases
were still 8i]

Oracle uses Rollback/Undo segments to ensure Read Consistency. Thus, if your query started at time t0, and it takes 30 minutes to run [ie till t30], you expect to see the data as of time t0 throughout the duration of the query. If another user were to issue an update at time t5 you would continue to see the data as of time t4 [or t0] even if your query comes to the respective row at, say, time t25. Read-consistency is provided by Oracle by ensuring that the pre-update image of the row is available in a Rollback/Undo segment [If that other user had committed at time t15, the information may or may not be still available in the Rollback/Undo segment at t25 as undo information relating to a committed update can be overwritten by other transactions -- in which case your query may encounter the "ORA-01555 'snapshot too old or rollback segment too small'" error]. All of this is common knowledge.

However, we sometimes forget that Read Consistency is at the Statement level, NOT the Transaction level. Thus, if you have two different SELECTs reading the same data, one after the other, and a second user updates *and* commits his update after the end of the first SELECT but before the beginning of the second SELECT, your second SELECT would *NOT* see the same data as the first SELECT.


Here are a few examples :

In the Query session :
1* select * from t1
18:10:12 SQL> /
COL1 COL2
---------- -----
1 a
2 b

In the Update session :
18:10:10 SQL> update t1 set col1=3,col2='c' where col1=2
18:10:41 2 /
1 row updated.

Back in the Query session :
1* select * from t1
18:12:38 SQL> /
COL1 COL2
---------- -----
1 a
2 b

So far, so good. As the Update session hasn't yet committed, I can still see the data.
18:13:32 SQL> l
1* select * from t1
18:13:33 SQL> /
COL1 COL2
---------- -----
1 a
2 b
18:13:34 SQL>

Here, I commit my update :
18:14:00 SQL> commit;
Commit complete.
18:14:01 SQL>

The Query session, now re-running the same SQL, sees the new data :
18:15:08 SQL> l
1* select * from t1
18:15:09 SQL> /
COL1 COL2
---------- -----
1 a
3 c
18:15:09 SQL>

As expected !
But, wait .... What if my Query session was actually a long-running report, consisting of multiple SQLs? If, say the first SQL fetched a count of records in a table and then printed that at the beginning of the report, and the last SQL also fetched a count and printed the count again, we might see different counts at the beginning and ending of the report !!
Here's an example of how you could get inconsistent data across different fetches in the same PLSQL block :

In the Query session :
18:53:35 SQL> l
1 declare
2 i number;
3 c1 number;
4 c2 varchar2(10);
5 cursor cursor1 is select col1, col2 from t1;
6 begin
7 i :=1;
8 loop
9 exit when i > 5;
10 open cursor1;
11 loop
12 fetch cursor1 into c1,c2;
13 exit when cursor1%NOTFOUND;
14 dbms_output.put_line('Got the values ' c1' and 'c2' in run 'i
' at ' to_char(sysdate,'HH24:MI:SS'));
15 end loop;
16 close cursor1;
17 dbms_lock.sleep(3);
18 i := i+1;
19 end loop;
20* end;
18:53:36 SQL> /
Got the values 1 and a in run 1 at 18:53:37
Got the values 3 and c in run 1 at 18:53:37
Got the values 1 and a in run 2 at 18:53:40
Got the values 26 and z in run 2 at 18:53:40
Got the values 1 and a in run 3 at 18:53:44
Got the values 26 and z in run 3 at 18:53:44
Got the values 1 and a in run 4 at 18:53:47
Got the values 26 and z in run 4 at 18:53:47
Got the values 1 and a in run 5 at 18:53:50
Got the values 26 and z in run 5 at 18:53:50
PL/SQL procedure successfully completed.
18:53:53 SQL>

In the Update session :
18:53:32 SQL> l
1* update t1 set col1=26, col2='z' where col1=3
18:53:38 SQL> /
1 row updated.
18:53:39 SQL> commit;
Commit complete.
18:53:40 SQL>

As the update was committed at 18:53:39, the Query session started seeing the new data thereafter, even though it was still within one PLSQL Block, within one Loop -- because a new SELECT was executed each time.
So how do we ensure that we get Read Consistent data across statements ? We can use the SET TRANSACTION READ ONLY command.

In the Query session :
19:00:35 SQL> set transaction read only;
Transaction set.
19:00:48 SQL> l
1* set transaction read only
19:00:49 SQL> get afiedt.buf
1 declare
2 i number;
3 c1 number;
4 c2 varchar2(10);
5 cursor cursor1 is select col1, col2 from t1;
6 begin
7 i :=1;
8 loop
9 exit when i > 5;
10 open cursor1;
11 loop
12 fetch cursor1 into c1,c2;
13 exit when cursor1%NOTFOUND;
14 dbms_output.put_line('Got the values ' c1' and 'c2' in run 'i
' at ' to_char(sysdate,'HH24:MI:SS'));
15 end loop;
16 close cursor1;
17 dbms_lock.sleep(3);
18 i := i+1;
19 end loop;
20* end;
19:00:59 SQL> /
Got the values 1 and a in run 1 at 19:01:02
Got the values 26 and z in run 1 at 19:01:02
Got the values 1 and a in run 2 at 19:01:06
Got the values 26 and z in run 2 at 19:01:06
Got the values 1 and a in run 3 at 19:01:09
Got the values 26 and z in run 3 at 19:01:09
Got the values 1 and a in run 4 at 19:01:12
Got the values 26 and z in run 4 at 19:01:12
Got the values 1 and a in run 5 at 19:01:15
Got the values 26 and z in run 5 at 19:01:15
PL/SQL procedure successfully completed.
19:01:18 SQL> select * from t1;
COL1 COL2
---------- -----
1 a
26 z
19:01:28 SQL> commit;
Commit complete.
19:01:43 SQL> select * from t1;
COL1 COL2
---------- -----
1 a
10 j
19:01:48 SQL>

In the Update session :
19:00:01 SQL> update t1 set col1=10, col2='j' where col1=26
19:01:03 2 /
1 row updated.
19:01:04 SQL> commit;
Commit complete.
19:01:05 SQL>

Although I had updated and committed at 19:01:04, my Query session continued to see the "old" or "Consistent" data, even at 19:01:18. I ended my "Read Consistent Across Statements" view by ending my pseudo-transaction in the Query session {It can be ended with either the COMMIT or the ROLLBACK keyword -- as we have not really done any DML in this session it makes no difference whether we issue a COMMIT or a ROLLBACK}. Only after that, could I see the updated records. The SET TRANSACTION READ ONLY "pseudo-transaction" does NOT allow DML [e.g INSERT or UPDATE] statements. Such DML would get an ORA-01456 error :
" 01456, 00000, "may not perform insert/delete/update operation inside a READ ONLY
transaction"
// *Cause: A non-DDL insert/delete/update or select for update operation
// was attempted
// *Action: commit (or rollback) transaction, and re-execute"

The SET TRANSACTION READ ONLY is useful when you have a report consisting of multiple statements and you need to ensure that all executions see the same Read-Consistent image. What is the downside ? You are more likely to hit ORA-01555 errors as Oracle does not guarantee preservation of the pre-image of updated data in the Rollback segments once an updating tranaction has committed. Oracle 9i with Automatic Undo Management and UNDO_RETENTION helps alleviate this problem by making a "best" attempt to keep data available in the Undo Segment for the duration specified by UNDO_RETENTION. Non-AUM 9i and 8i and below do not guarantee any retention of pre-update images in the Rollback Segments. The DBA might create more and larger rollback segments to reduce the probability {but not guarantee} of pre-update information being overwritten in the Rollback Segments.

Some observations from the latest Oracle-HP Benchmark

A quick read of the latest Oracle-HP Benchmark (world record TPC-C, using Single Processor, Oracle Enterprise Linux, Oracle 10g SE-One) shows some curious things :
1. Oracle has priced for the Xeon QuadCore Processor at the rate of 1 Processor based on
the single socket justified as "When licensing Oracle programs with Standard Edition One or Standard Edition in the product name, a processor is counted equivalent to an occupied socket" for a 3-year licence. Thus, Oracle used the combination of "Processor, not Core" for SE/SE-One and 50% of List price for a 3-Year Licence.
2. This provides us a hint that we could go for a 2 QuadCore Processor server and pay for only 2 CPUs for the SE-One licence.
The licence costing for 1 Processor for 3 years at 50% did help Oracle achieve "a new world record for price/performance"

3. For the benchmark, the there were only 2 Online Redo Logs but of 12,817MB each ! Huge ! Obviously, log_checkpoint_interval and log_checkpoint_timeout were both 0.
Checkpointing only at the 12GB log switch.
4. DB_BLOCK_SIZE was 2048, not the "standard" 8192.
5. DB_CACHE_SIZE was 2000M but DB_KEEP_CACHE_SIZE was 16,500M.
6. The Benchmark used "ANALYZE TABLE/INDEX ESTIMATE STATISTICS" instead of the normally recommended "DBMS_STATS..."
7. Database Recovery was using the SQLPlus "RECOVER" command, _not_ with RMAN.
8. "Demonstration" of recovery from loss of a log file was actually demonstrating that "
system continued running because the logs are mirrored" . They didn't have to show Recovery in case of loss of online/active redo logs !

A Bug in OWI

Tanel Poder has discovered a bug in how Oracle reports wait events -- when he was testing a program reading from an External Table and V$SESSION_WAIT was showing neither the the correct STATE nor the correct EVENT.

Oracle Books in the Library

I was going through the books in the Geography section of the local library. An Apress book stood out. It was "Pro Oracle Spatial". I wonder if I should tell the librarians that they have wrongly shelved the book OR that they have correctly classified it.