 | | | Effective Oracle by Design - p259 - 260 - confused, is there a mistake? | Effective Oracle by Design - p259 - 260 - confused, is there a mistake? 2005-11-02 - By t_adolph@(protected)
Hi All,
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>
|
|
|