Intermediate SQL Color Coded SQL, UNIX and Database Essays

1Jun/127

Read inconsistent queries in ORACLE database ? Sure, why not!

Read consistency is one of the coolest features of ORACLE database.

In a nutshell, it means that your queries (at least, logically) do not pay attention to the outside world. I.e. your tables may be hit with thousands of changes per second, but your query will ignore all the hoopla and will always see only the data that existed when the query started.


Read consistency is quite addictive, because in ORACLE it is so trivial. Once you figure out that you don’t need to do much to get it (i.e. deal with locks or synchronize with other users), running queries and expecting consistent results every time quickly becomes a second nature.

And … it’s quite easy to to fall into a trap of thinking that working any other way for a query is impossible.

My job today is to show that this expectation is not entirely correct 🙂

What does it mean to be read consistent

So, what exactly does it mean for a query to be read consistent ?

I’m going to explore this point using a slightly convoluted (and specially crafted) example, that will query this simple table:

CREATE TABLE t (order_id INT);
INSERT INTO t VALUES (1);
commit;
SELECT * FROM t;

  ORDER_ID
----------
         1

With this slightly complicated query:

WITH int_g AS (
  SELECT level l, sleep(1) s FROM dual CONNECT BY level <= 5
) SELECT /*+ ordered no_merge use_nl(t) */ int_g.l, int_g.s, t.order_id
FROM int_g, t
/

         L          S   ORDER_ID
---------- ---------- ----------
         1          1          1
         2          1          1
         3          1          1
         4          1          1
         5          1          1

Because I’m using nested loops the query above implements this fairly simple algorithm during its execution:

  FOR I in 1..5:
    SELECT ...
    sleep(1)

sleep(1) function here is a simple wrap around dbms_lock.sleep:

CREATE OR REPLACE FUNCTION sleep (seconds IN NUMBER)
RETURN NUMBER AS
BEGIN
  sys.dbms_lock.sleep(seconds);
  RETURN seconds;
END;
/

that causes the query to sleep for 1 second.

In other words, I’m probing inner table: T 5 times with 1 second delay between each try.

To make matters interesting, once the above query starts, I’m going to also start changing data in T from a separate session with this simple script:

BEGIN
  FOR i IN 1 .. 10 loop
    INSERT INTO t (order_id) VALUES (i);
    commit;
    dbms_lock.sleep(1);
  END loop;
END;
/

The bottom line is: each probe in the query should deal with a slightly different data in the table and we can now see how ORACLE reacts to that.

As most people would expect, the result of the query, even with concurrent updates still looks like this:

         L          S   ORDER_ID
---------- ---------- ----------
         1          1          1
         2          1          1
         3          1          1
         4          1          1
         5          1          1

In other words, the original query did NOT see any data changes coming from session 2. We queried table T 5 times over 5 seconds interval. Every time the data in T was slightly different, but our query always saw the same image, as it existed when query started. This is how read consistency rules the day.

How to be read inconsistent

Still, if this is what you’ve come to expect from ALL ORACLE queries, prepare to be surprised.

Let’s slightly modify our query, replacing our regular table with one of ORACLE dictionary views – v$session. In a nutshell, we’ll query our session status as we move along in our query.

WITH int_g AS (
  SELECT level l, sleep(1) s FROM dual
  CONNECT BY level <= 5
)
SELECT /*+ ordered no_merge use_nl(se) */
  se.event, se.wait_time_micro, int_g.s
FROM int_g, v$session se
WHERE sid=sys_context('userenv', 'sid')
/

EVENT                WAIT_TIME_MICRO   S
-------------------- --------------- ---
PL/SQL LOCK timer          1,000,760   1
PL/SQL LOCK timer          1,000,610   1
PL/SQL LOCK timer          1,000,765   1
PL/SQL LOCK timer          1,000,755   1
PL/SQL LOCK timer          1,000,688   1

Surprise! Look at the “wait times” – all of them are different.

We still query underlying table (v$session in this case) 5 times in a nested loop, but now every single time we look at the table we see different results! It’s almost like read consistency is not in the picture here.

Well, the truth is:

When it comes to v$ views read consistency is REALLY not in the picture

v$ views are mostly based on x$ objects, which, although look like tables to us and are even called (“fixed”) tables are something else entirely: x$ objects are kernel memory arrays that are exposed to us (for our convenience) through a table interface. And being arrays, they do NOT have read consistency!

To prove the point even further I’m going to slightly modify this example by changing order of tables in the FROM clause:

With SQL being declarative language, this shouldn’t affect the results, right ? Well, see for yourself:

WITH int_g AS (
  SELECT level l, sleep(1) s FROM dual
  CONNECT BY level <= 5
)
SELECT /*+ ordered no_merge use_nl(se) */
  se.event, se.wait_time_micro, int_g.s
FROM v$session se, int_g -- <-- ORDER IS REVERSED
WHERE sid=sys_context('userenv', 'sid')
/

EVENT                     WAIT_TIME_MICRO   S
------------------------- --------------- ---
SQL*Net message TO client               3   1
SQL*Net message TO client               3   1
SQL*Net message TO client               3   1
SQL*Net message TO client               3   1
SQL*Net message TO client               3   1

The picture is completely different! Since we start with v$session this time, we read it only once and, of course, that means that wait times are the same everywhere (I guess, everything becomes consistent if you have one sample 🙂 )

And there you have it folks! Read consistency is NOT a natural property of any ORACLE query. It requires work to enforce and sometimes ORACLE chooses not to do it.

This is cool, but how we make it useful ?
In the next article, I’ll talk about precisely that.

Comments (7) Trackbacks (0)
  1. What kind of color-coding tool that you use in SQL*PLUS?

    Thanks

  2. It’s not really sqlplus coloring. What you see is CodeColorer wordpress plugin.

  3. Nice writeup. Much more entertaining than the second bullet point in the note here 🙂
    http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_1001.htm#sthref3086

    “Because the information in the V$ views is dynamic, read consistency is not guaranteed for SELECT operations on these views.”

    By the way, have you noticed that some functions are “read consistent” while others are not? Just add a SYSDATE column to your query and then wrap the SYSDATE call in a PL/SQL function to see the difference.

    http://jonathanlewis.wordpress.com/2010/01/07/testing-testing/#comment-35233

  4. Thanks, Flado!

    I did not know about systimestamp/f(systimestamp) trick. Now I have another tool in my arsenal.

    Much appreciated,
    Maxym Kharchenko

  5. Can you please convert your wordpress blog rss to FULL TEXT RSS , but not summary? I’d like to subscribe your blog, and access it frequently !

  6. As soon as I figure out how to do it. This seems to be a fairly recent problem and poking around in WP options, did not solve it (even though rss settings are set at “full text”) – must be a bug somewhere in php code …

  7. Tanel Poder’s latchprofx based on this feature. Simple example:
    [sourcecode language=”sql”]
    with gen as (select/*+ inline no_merge */ level l from dual connect by level<=1000)
    ,timer1 as (select hsecs from v$timer)
    ,timer2 as (select hsecs from v$timer)
    select–+ leading(timer1 g1 g2 timer2) use_merge(timer1) use_nl(timer2)
    min(timer1.HSECS),max(timer2.HSECS)
    from timer1
    ,gen g1
    ,gen g2
    ,timer2
    [/sourcecode]


Leave a comment

No trackbacks yet.