Monday, January 28, 2013

Oracle breaking read consistency by design

Read consistency is one feature Oracle rdbms offer by default and it is one of the reasons why Oracle is an industry leader, but there are few types of queries where Oracle by design doesn't offer read consistency.

As part of read consistency, Oracle read current scn and use it as query scn for executing any given sql query.

Here is the list of sql query types where read consistency is not guaranteed by design:

1) Any queries on dynamic performance views (v$) and fixed tables (x$)
2) Any queries on table functions (including pipelined functions)
3) Any queries on external table
4) Reading any global variables set in memory like context values set as part of a query, dbms_application_info, etc.

Demo for v$ views:
create or replace function sleep1 return number is
begin
sys.dbms_lock.sleep(10);
return 1;
end;
/

select a.sid, sleep1(), (select b.current_scn from v$database b where b.current_scn > a.sid) current_scn from v$session a;

  SID             SLEEP1()          CURRENT_SCN
----- -------------------- --------------------
    1                    1        6727855283317
  119                    1        6727855285974
  237                    1        6727855288603
  238                    1        6727855291192

current_scn values in the above query are different for each record and hence it is read inconsistent

Demo for pipelined function:


create table t_piped as select 1 a, rownum b from dual connect by level <= 10;

create type o_tab as table of number;
/


create or replace function f_piped1(val1 in number) return o_tab
as
v1 number;
v2 o_tab := o_tab();
cursor c1 is select a.a from t_piped a, (select sleep1 b from dual) b where rownum=1 and a.a >= b.b;
begin
open c1;
fetch c1 bulk collect into v2;
return v2;
end;
/



Now run below pl/sql in one session.


begin
update t_piped set a=1;
commit;
for i in 1..10 loop
update t_piped set a=(a*(i+1));
commit;
dbms_lock.sleep(2);
end loop;
end;
/



Run the below query in second session at the same time.
select a.a, b.column_value from t_piped a, table(f_piped1(1)) b  where rownum<=5;


         A COLUMN_VALUE
---------- ------------
        24           24
        24           24
        24          120
        24          120
        24          720



Above query is selecting same column from the table and table function, while the underlying table is being updated with different value for each run, we expect to see result set for column "A" and column "COLUMN_VALUE", but they are different and  the table function is accessing t_piped table as of different query scn each time the function is accessed as part of the same query.

We can make the function deterministic to make sure that it only executes once for the whole query and see what happens.


create or replace function f_piped2(val1 in number) return o_tab
deterministic
as
v1 number;
v2 o_tab := o_tab();
cursor c1 is select a.a from t_piped a, (select sleep1 b from dual) b where rownum=1 and a.a >= b.b;
begin
open c1;
fetch c1 bulk collect into v2;
return v2;
end;
/

Run the below query and pl/sql table update in a loop at the same time from different sessions.

select a.a, b.column_value, c.column_value from t_piped a, table(f_piped2(1)) b, table(f_piped2(1)) c  where rownum<=5;

         A COLUMN_VALUE COLUMN_VALUE
---------- ------------ ------------
     40320        40320       362880
     40320        40320       362880
     40320        40320       362880
     40320        40320       362880
     40320        40320       362880

Now we see same "COLUMN_VALUE" for each record, but they are different across two table functions in the from clause. It means each invocation of table function will run as of current scn as each query within that function is being executed. 

The only way to guarantee read consistency at the query level when table functions are used is to use "AS OF SCN" clause for the tables and each table function being accessed in the query.



create or replace function f_piped3(val1 in number) return o_tab
as
v1 number;
v2 o_tab := o_tab();
--v4 number :=dbms_flashback.GET_SYSTEM_CHANGE_NUMBER();
--cursor c1 is select sleep1||(select b.a from t_piped as of scn v4 b where b.b=a.rn) from (select rn, dummy from
--(select rownum rn, dummy from dual connect by level <= 10) ) a ;
cursor c1 is select a.a from t_piped as of scn val1 a, (select sleep1 b from dual) b where rownum=1 and a.a >= b.b;
begin
open c1;
fetch c1 bulk collect into v2;
return v2;
end;
/

Run the below query and pl/sql table update in a loop at the same time from different sessions.

set numwidth 20
column current_scn new_value queryscn
select current_scn from v$database;

select a.a, b.column_value, c.column_value from t_piped as of scn (&&queryscn) a,
table(f_piped3(&&queryscn)) b, table(f_piped3(&&queryscn)) c  where rownum<=5
/

                   A         COLUMN_VALUE         COLUMN_VALUE
-------------------- -------------------- --------------------
                 120                  120                  120
                 120                  120                  120
                 120                  120                  120
                 120                  120                  120
                 120                  120                  120

Based on the above query output, I recommend using "AS OF SCN" clause for all queries within each table functions if the read consistency is needed for any queries accessing table functions. This could be a quite a bit of work and it'd be nice to have a feature as an option to execute table functions as of query scn. I'll be opening enhancement request with Oracle support for this.

5 Comments:

At January 30, 2013 at 12:39:00 AM PST, Blogger christi parks said...

Hello all,I am new and I would like to ask that what are the benefits of sql training, what all topics should be covered and it is kinda bothering me ... and has anyone studies from this course wiziq.com/course/125-comprehensive-introduction-to-sql of SQL tutorial online?? or tell me any other guidance...
would really appreciate help... and Also i would like to thank for all the information you are providing on sql.

 
At April 1, 2015 at 5:30:00 AM PDT, Anonymous IOS training in chennai said...

Your blog is unique among others nature.. I just heard about to survive all the nature. Is that so wind up or still it is in sign up.

 
At April 1, 2015 at 5:32:00 AM PDT, Anonymous Android training in chennai said...

If it still to be tough among others. Just come out.

 
At April 1, 2015 at 5:33:00 AM PDT, Anonymous Java training in chennai said...

Basically this kind of interest is worth.

 
At June 16, 2015 at 4:42:00 AM PDT, Anonymous Sathik Ali.A said...

Excellent blog..

 

Post a Comment

Links to this post:

Create a Link

<< Home