Effective Oracle by Design - p259 - 260 - confused, is there amistake? 2005-11-02 - By t_adolph@(protected)
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@(protected)>
To: <t_adolph@(protected)>
Cc: "ORACLE-L" <oracle-l@(protected)>
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 (See ORA-01555.ora-code.com) 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@(protected) 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@(protected)> 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@(protected)> 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@(protected)> 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
|
|
