29 June, 2007

A recent blog posting on the SET TRANSACTION READ ONLY reminded me of a notethat I had written in 2004. [note the references to AUM because most of my databaseswere 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.

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> l1 declare2 i number;3 c1 number;4 c2 varchar2(10);5 cursor cursor1 is select col1, col2 from t1;6 begin7 i :=1;8 loop9 exit when i > 5;10 open cursor1;11 loop12 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:37Got the values 3 and c in run 1 at 18:53:37Got the values 1 and a in run 2 at 18:53:40Got the values 26 and z in run 2 at 18:53:40Got the values 1 and a in run 3 at 18:53:44Got the values 26 and z in run 3 at 18:53:44Got the values 1 and a in run 4 at 18:53:47Got the values 26 and z in run 4 at 18:53:47Got the values 1 and a in run 5 at 18:53:50Got the values 26 and z in run 5 at 18:53:50PL/SQL procedure successfully completed.18:53:53 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> l1* set transaction read only19:00:49 SQL> get afiedt.buf1 declare2 i number;3 c1 number;4 c2 varchar2(10);5 cursor cursor1 is select col1, col2 from t1;6 begin7 i :=1;8 loop9 exit when i > 5;10 open cursor1;11 loop12 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:02Got the values 26 and z in run 1 at 19:01:02Got the values 1 and a in run 2 at 19:01:06Got the values 26 and z in run 2 at 19:01:06Got the values 1 and a in run 3 at 19:01:09Got the values 26 and z in run 3 at 19:01:09Got the values 1 and a in run 4 at 19:01:12Got the values 26 and z in run 4 at 19:01:12Got the values 1 and a in run 5 at 19:01:15Got the values 26 and z in run 5 at 19:01:15PL/SQL procedure successfully completed.19:01:18 SQL> select * from t1;COL1 COL2---------- -----1 a26 z19:01:28 SQL> commit;Commit complete.19:01:43 SQL> select * from t1;COL1 COL2---------- -----1 a10 j19:01:48 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 ONLYtransaction"// *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.

27 June, 2007

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 onthe 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 !

25 June, 2007

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.

06 June, 2007

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.