FreeLists / oracle-l / Re: Effective Oracle by Design - p259 - 260 - confused, is there amistake?

[<t_adolph@xxxxxxxxxxx>]

Hi Stephane,

I think I haven't explained my query very well, as you're the second person
with the same feedback :-(

I understand that if a session at 10:00 makes some uncommitted changes then
there are active undos with the before image.

If another session at 10:12 then queries this data it must access the undo
to get a consistent read.  Hence the "1005  consistent gets  <---- 
interesting bit here - 1000" in my post.

I'd hoped that's what I put in my original post.

But, in Tom's example, pages 259-260 it looks to me that my session at 10:00
*is* committing the changes, so the sessions at 10:12 should not need to get
any undo.

Tony



----- Original Message ----- 
From: "Stephane Faroult" <sfaroult@xxxxxxxxxxxx>
To: <t_adolph@xxxxxxxxxxx>
Cc: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, November 02, 2005 11:43 AM
Subject: Re: Effective Oracle by Design - p259 - 260 - confused, is there
amistake?


> 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
> >
>
>
>
--
http://www.freelists.org/webpage/oracle-l