Effective Oracle by Design - p259 - 260 - confused, is there a mistake?

From: <t_adolph_at_hotmail.com>
Date: Wed, 2 Nov 2005 11:05:57 +0100
Message-ID: <BAY103-DAV3BBEE04946D236958116AFA6E0@phx.gbl>

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_at_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_at_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_at_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

--

http://www.freelists.org/webpage/oracle-l Received on Wed Nov 02 2005 - 04:06:53 CST