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

Tony,

   You have missed what is called read consistency - Oracle guarantees that what you 'see' when querying a table reflects the state of the table when you hit <return> after your SELECT statement. If COMMITs occur while your SELECT is going on, it's exactly as if no COMMIT had occurred at all. This can happen in the case of concurrent sessions, or when in a single session you open a cursor loop and, inside the loop, update one of the tables queried and commit changes (a famous reason for the infamous ORA-01555 error).
If your SELECT follows a committed update, then the state of the table when you start the SELECT is the committed state, and you have no reason to read anything from the undo tablespace.

HTH Stephane Faroult

On Wed, 2005-11-02 at 11:05 +0100, t_adolph_at_hotmail.com wrote:
> 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