Effective Oracle by Design - p259 - 260 - confused, is there amistake?
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
