Re: Effective Oracle by Design - p259 - 260 - confused, is there a mistake?
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: t_adolph@xxxxxxxxxxx
- Date: Wed, 02 Nov 2005 11:43:26 +0100
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@xxxxxxxxxxx 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@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
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
