A question for those of you who have read Effective Oracle by Design by Thomas Kyte:
Chpt 5, page 259 - 260: Tom is explaining that undo is read for read for read consistency....
I understood something like this:
Session A: -- ---- ---- ---- tony@(protected)> select * from t1 where a=1;
A B -- ---- -- -- ---- -- 1 1
Statistics -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 451 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
tony@(protected)> begin 2 for i in 1 .. 1000 3 loop 4 update t1 set b=b where a=1; 5 end loop; 6 end; 7 /
PL/SQL procedure successfully completed.
No commit in my example.
Session B -- ---- ---- ----
tony@(protected)> select * from t1 where a=1;
A B -- ---- -- -- ---- -- 1 1
Statistics -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----- 0 recursive calls 0 db block gets 1005 consistent gets <---- interesting bit here - 1000 undo's got (as expected) 0 physical reads 52 redo size 451 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
Now this makes sense to me as in session A there was no commit. But in Tom's pl/sql there's a commit every update. Shouldn't that throw away the undo meaning that session B wouldn't be interested in it? I tried with a commit every update and confirmed what I'd expected, only 4 gets. What have I missed folks?
Tony PS I thinks its irrelevant here, but Ora 9.2.0.7 on Win2k
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=Content-Type content="text/html; charset=iso-8859-1"> <META content="MSHTML 6.00.2800.1522" name=GENERATOR> <STYLE></STYLE> </HEAD> <BODY bgColor=#ffffff> <DIV><FONT face=Arial size=2>Hi All,</FONT></DIV> <DIV><FONT face=Arial size=2></FONT> </DIV> <DIV><FONT face=Arial size=2>A question for those of you who have read Effective Oracle by Design by Thomas Kyte:</FONT></DIV> <DIV><FONT face=Arial size=2></FONT> </DIV> <DIV><FONT face=Arial size=2>Chpt 5, page 259 - 260: Tom is explaining that undo is read for read for read consistency....</FONT></DIV> <DIV><FONT face=Arial size=2></FONT> </DIV> <DIV><FONT face=Arial size=2>I understood something like this:</FONT></DIV> <DIV><FONT face=Arial size=2></FONT> </DIV> <DIV><FONT face=Arial size=2>Session A:</FONT></DIV> <DIV><FONT face=Arial size=2>-- ---- ---- ----</FONT></DIV> <DIV><A href="mailto:tony@(protected)"><FONT face=Arial size=2>tony@(protected)</FONT></A><FONT face=Arial size=2>> select * from t1 where a=1;</FONT></DIV> <DIV><FONT face=Arial size=2></FONT> </DIV> <DIV><FONT face=Arial size=2> A B<BR>-- ---- -- -- ---- --<BR> 1 1</FONT></DIV> <DIV><FONT face=Arial size=2></FONT> </DIV> <DIV><FONT face=Arial size=2>Statistics<BR>-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----- <BR> 0 recursive calls<BR> 0 db block gets<BR> 4 consistent gets<BR> 0 physical reads<BR>   ; 0 redo size<BR> 451 bytes sent via SQL*Net to client<BR> 503 bytes received via SQL*Net from client<BR> 2 SQL *Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 1 rows processed</FONT></DIV> <DIV><FONT face=Arial size=2></FONT> </DIV> <DIV><FONT face=Arial><A href="mailto:tony@(protected)"><FONT size=2>tony@(protected)</FONT></A><FONT size=2>> begin<BR> 2 for i in 1 . 1000<BR> 3 loop<BR> 4 update t1 set b=b where a=1;<BR> 5 end loop;<BR> 6 end;<BR> 7 /</FONT></FONT></DIV> <DIV><FONT size=2></FONT> </DIV> <DIV><FONT face=Arial size=2>PL/SQL procedure successfully completed.</FONT></DIV> <DIV><FONT face=Arial size=2></FONT> </DIV> <DIV><FONT face=Arial size=2>No commit in my example.</FONT></DIV> <DIV><FONT face=Arial size=2></FONT> </DIV> <DIV><FONT face=Arial size=2>Session B</FONT></DIV> <DIV><FONT face=Arial size=2>-- ---- ---- ----</FONT></DIV> <DIV><FONT face=Arial size=2></FONT> </DIV> <DIV><A href="mailto:tony@(protected)"><FONT face=Arial size=2>tony@(protected)</FONT></A><FONT face=Arial size=2>> select * from t1 where a=1;</FONT></DIV> <DIV><FONT face=Arial size=2></FONT> </DIV> <DIV><FONT face=Arial size=2> A B<BR>-- ---- -- -- ---- --<BR> 1 1</FONT></DIV> <DIV><FONT face=Arial><BR><FONT size=2>Statistics<BR>-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----- <BR> 0 recursive calls<BR> 0 db block gets<BR> 1005 consistent gets <---- interesting bit here - 1000 undo's got (as expected)<BR> 0 physical reads<BR> 52 redo size<BR> 451 bytes sent via SQL*Net to client<BR> 503 bytes received via SQL*Net from client<BR> 2 SQL *Net roundtrips to/from client<BR> 0 sorts (memory)<BR> 0 sorts (disk)<BR> 1 rows processed</FONT></FONT></DIV> <DIV><FONT face=Arial size=2></FONT> </DIV> <DIV><FONT face=Arial size=2>Now this makes sense to me as in session A there was no commit. But in Tom's pl/sql there's a commit every update. Shouldn't that throw away the undo meaning that session B wouldn't be interested in it? I tried with a commit every update and confirmed what I'd expected , only 4 gets. What have I missed folks?</FONT></DIV> <DIV><FONT face=Arial size=2></FONT> </DIV> <DIV><FONT face=Arial size=2>Tony</FONT></DIV> <DIV><FONT face=Arial size=2>PS I thinks its irrelevant here, but Ora 9.2.0.7 on Win2k</FONT></DIV> <DIV><FONT face=Arial size=2></FONT> </DIV></BODY></HTML>
