Sukhendu Naskar, August 07, 2009 - 2:55 pm UTC
Alexander, August 10, 2009 - 12:29 pm UTC
Is there an implied "commit" after the update takes place? What you described sounds like a dirty read to me, which Oracle doesn't do, right?
August 13, 2009 - 8:50 am UTC
No. there is not (any sort of implied commit)
what sounds like a dirty read to you? We read the block at 9:10am - we notice that it was modified since your query began at 9am. Therefore, it contains data you are not allowed to see so we roll back that change and present you with the data that was committed in the database as of the time your query begain - 9am.
At no time, AT NO TIME, are you ever presented with data that was modified by another transaction and not committed (that is a dirty read). At no time do you see uncommitted work performed by other sessions.
where do you see a dirty read here?
Alexander, August 13, 2009 - 11:43 am UTC
That was my understanding also but that's not how I interpreted this statement:
You get my copy of the block - the one I modified and you notice "it changed since my query began"
How would I notice it changed if I can't see your uncommitted changes from my session?
August 13, 2009 - 12:42 pm UTC
this processing happens way before YOU (Alexander) sees it. The Oracle database software gives you (Alexander) the read consistent image of the block. You (running the Oracle database software) see the current block and realize you (Alexander) cannot see it so you fix it.
This is all processing done in your dedicated/shared server - "you" (your client application) never sees these intermediate things, you (your application) never sees the read inconsistent versions but you (your session, your transaction) must 'see them' in order to 'correct them' put them back the way they were.
Harel Safra, August 13, 2009 - 12:13 pm UTC
"How would I notice it changed if I can't see your uncommitted changes from my session?"
It's not you - the user, it's you - the server process.
So the server process sees that the block has changed, rolls it back and then presents the user the needed data (or ora-1555).
Harel
August 13, 2009 - 12:44 pm UTC
yes, thanks :)
PI in RAC
krish, August 18, 2009 - 8:35 pm UTC
But how do you measure it?
Jeff Hunter, August 21, 2009 - 3:29 pm UTC
OK, but how do you measure this scenario? In other words, say you want to find out at 9:10 that a session had to read the "older" version of the block. Is there a counter or a v$ table that keeps up with this activity?
Or, if you do a 10046 trace, will the CR= under "Row Source Operation" as unusually high values?
August 25, 2009 - 8:53 am UTC
ops$tkyte%ORA10GR2> create table t ( x int );
Table created.
ops$tkyte%ORA10GR2> insert into t values ( 1 );
1 row created.
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T' );
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> variable x refcursor
ops$tkyte%ORA10GR2> variable y refcursor
ops$tkyte%ORA10GR2> begin
2 open :x for select * from t;
3 open :y for select * from t;
4 end;
5 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> @mystat "data blocks consistent reads - undo records applied"
ops$tkyte%ORA10GR2> set echo off
NAME VALUE
---------------------- ----------
data blocks consistent 66
reads - undo records
applied
ops$tkyte%ORA10GR2> print x
X
----------
1
ops$tkyte%ORA10GR2> @mystat2
ops$tkyte%ORA10GR2> set echo off
NAME VALUE DIFF
---------------------- ---------- ------------------
data blocks consistent 66 0
reads - undo records
applied
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
2 for i in 1 .. 10000
3 loop
4 update t set x = x+1;
5 commit;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @mystat "data blocks consistent reads - undo records applied"
ops$tkyte%ORA10GR2> set echo off
NAME VALUE
---------------------- ----------
data blocks consistent 66
reads - undo records
applied
ops$tkyte%ORA10GR2> print y
X
----------
1
ops$tkyte%ORA10GR2> @mystat2
ops$tkyte%ORA10GR2> set echo off
NAME VALUE DIFF
---------------------- ---------- ------------------
data blocks consistent 10067 10,001
reads - undo records
applied