Effective Oracle by Design - p259 - 260 - confused, is there a mistake?
- From: <t_adolph@xxxxxxxxxxx>
- To: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 2 Nov 2005 11:05:57 +0100
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@DB1> 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@DB1> 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@DB1> 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
Other related posts:
