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

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_at_roughsea.com> To: <t_adolph_at_hotmail.com> Cc: "ORACLE-L" <oracle-l_at_freelists.org> 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_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