NOT NULL Plus TAF Causing ORA-08176

Just stumbled onto an interesting situation with TAF. Turns out that you can get an ORA-08176 error (consistent read failure; rollback data not available) from a SELECT statement that fails over with Transparent Application Failover in a RAC configuration. And I seem to have found a situation where adding a NOT NULL constraint to a query triggers this error. What in the world could cause t`his to happen?

Let’s reproduce the error and see if you can guess.

Making it Happen Again

We’ll start by making a table with lots of rows so that we can get a long-running query going. I’m also going to stuff some data in there to make sure that this table uses a good number of blocks and a full scan requires a little I/O.

SESSION 1/NODE 1
============
SQL> connect jeremy/jeremy@t1
Connected.
SQL> create table t2 as
  2  select rownum id, rpad(rownum,999,'*') d1
  3  from all_objects where rownum exec dbms_stats.gather_table_stats('JEREMY','T2');

PL/SQL procedure successfully completed.

Now let’s start a query on the table we’ve created. I’ve connected over a VPN for SESSION 1 so it will take a little while to fetch and display 5000 rows. I’ve also enabled BASIC/SELECT TAF so that our select statement can automatically fail over to the other node and re-execute itself if there are problems. There is no transaction in this session; this is where TAF is supposed to shine.

After starting this query, we’re going to do some maintenance from another session then simulate a crash of the first node with SHUTDOWN ABORT. The select statement reconnects and re-executes itself; you experience a slight delay then it picks up right where it left off. But as you’ll see, if I include the NOT NULL filter then I receive an error. First, let’s try it without the filter.

SESSION 1/NODE 1
============
SQL> -- just to make sure
SQL> commit;

Commit complete.

SQL> select id from t2;
         1
         2
         3
         4
         5
         6
         7
         8
         9
[...]

                    SESSION 2/NODE 1
                    ============
                    SQL> connect / as sysdba
                    Connected.
                    SQL> create index jeremy.t2x on jeremy.t2(id);

                    Index created.

                    SQL> shutdown abort;
                    ORACLE instance shut down.

SESSION 1/NODE 1
============
[...]
      4994
      4995
      4996
      4997
      4998
      4999
      5000

5000 rows selected.

SQL>

Fantastic – great demo of TAF. Or is it? Let’s try the same thing but add a NOT NULL filter to our query.

SESSION 1/NODE 1
============
SQL> drop index t2x;

Index dropped.

SQL> -- just to make sure
SQL> commit;

Commit complete.

SQL> select id from t2 where id is not null;
         1
         2
         3
         4
         5
         6
         7
         8
         9
[...]

                    SESSION 2/NODE 1
                    ============
                    SQL> connect / as sysdba
                    Connected.
                    SQL> create index jeremy.t2x on jeremy.t2(id);

                    Index created.

                    SQL> shutdown abort;
                    ORACLE instance shut down.

SESSION 1/NODE 1
============
[...]
      1267
      1268
      1269
      1270
      1271
      1272
      1273
      1274
      1275
ERROR:
ORA-08176: consistent read failure; rollback data not available



1275 rows selected.

SQL>

What happened? Why would a NOT NULL constraint cause an ORA-08176 error?

Analysis

You’ve probably already guessed that the index I’m creating has something to do with it. And of course you’re right. The error message is unfortunately a little misleading because the problem actually has nothing to do with rollback and everything to do with the optimizer. What’s actually happening is that once we add the index and the not null constraint Oracle can use an Index Fast Full Scan to get the answer much more quickly.

SQL> explain plan for select id from t2;

Explained.

SQL> select * from table(dbms_xplan.display);

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  5000 | 20000 |   191  (16)| 00:00:03 |
|   1 |  TABLE ACCESS FULL| T2   |  5000 | 20000 |   191  (16)| 00:00:03 |
--------------------------------------------------------------------------

SQL> explain plan for
  2  select id from t2 where id is not null;

Explained.

SQL> select * from table(dbms_xplan.display);

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |  5000 | 20000 |     8  (50)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| T2X  |  5000 | 20000 |     8  (50)| 00:00:01 |
-----------------------------------------------------------------------------

SQL>

What’s actually happening is that TAF is attempting to re-execute the query and it realizes that the plan has changed. I’m guessing that it saves the plan hash and compares before re-executing. Why can’t TAF just re-execute anyway? Because Oracle needs to gurantee that the rows return in the exact same order as the first execution. Oracle is going to count off the number of rows you already have and discard them in order to resume your query where you left off. If the plan changes then the rows could come back in a different order.

Guranteed Order Without ORDER BY

This brings up an interesting point – Oracle is in fact relying on the fact that rows come back in the same order. But we’ve been [correctly] trained that order can always change unless you have an ORDER BY clause – how can TAF assume that rows come back in the same order in its absence? For example, what if several rows migrated to different blocks before a FULL SCAN query was re-executed? This is an easy test; if you full scan a table then migrate some rows and full scan it again then you’ll see that the order changes.

My best estimation is that the answer is a two-part formula: SAME PLAN + SAME BLOCKS = SAME ORDER. To preserve the plan Oracle probably is saving the hash as I mentioned before, then making sure that this hash doesn’t change when the query is executed. To preserve the blocks Oracle TAF is actually running a FLASHBACK QUERY when you failover. In addition to the plan hash Oracle stores the current SCN and when it re-executes the query it runs AS OF [SCN]. And a quick test observing row order from a full scan query will demonstrate that when oracle creates consistent images of blocks it does seem to rollback row migrations. (At least my test on 10.2.0.2.0 appeared to indicate this.) Which is also interesting because it indicates that Oracle must be storing undo about row migrations; perhaps it is actually recording a complete row delete from block A and a complete row insert into block B. It almost certainly means that row migrations cause additional undo (yet another reason excessive row migrations are bad).

So the NOT NULL filter itself didn’t cause an ORA-08176. (Obviously.) It was the fact that the plan changed. And anything in any environment which causes a plan to change before SELECT-mode TAF can re-execute the query will cause this error. Just don’t sweat it; re-start your process and move on with life. :)