Oracle Scratchpad

March 20, 2011

Upgrade Whoa

Filed under: Infrastructure,Oracle,Troubleshooting,Upgrades — Jonathan Lewis @ 8:58 pm BST Mar 20,2011

(The title’s a pun, by the way – an English form of humour that is not considered good unless it’s really bad.)

Very few people try to email me or call me with private problems – which is the way it should be, and I am grateful to my audience for realizing that this blog isn’t trying to compete with AskTom – but I do get the occasional communication and sometimes it’s an interesting oddity that’s worth a little time.

Today’s blog item is one such oddity – it was a surprise, it looked like a nasty change in behaviour, and it came complete with a description of environment, and a neatly formatted, complete, demonstration. For a discussion of the problem in Spanish you can visit the blog of John Ospino Rivas, who sent me the original email and has written his own blog post on the problem.

We start with a simple table, and then query it with a ‘select for update from two different sessions:


drop table tab1 purge;

create table tab1(
	id	number,
	info	varchar2(10),
	constraint tab1_pk primary key (id)
		using index (create index idx_tab1_pk on tab1(id))
);

insert into tab1 values(1,'a');
insert into tab1 values(2,'a');
insert into tab1 values(3,'a');
commit;

execute dbms_stats.gather_table_stats(user,'tab1',cascade=>true)

column id new_value m_id

set autotrace on explain

select  id
from    tab1
where   id = (
            select  min(id)
            from    tab1
        )
for update
;

set autotrace off

prompt	=============================================================
prompt  Now repeat the query in another session and watch it lock
prompt	And use a third session to check v$lock
prompt  Then delete here, commit and see what the second session does
prompt	=============================================================

accept X prompt 'Press return to delete and commit'

set verify on
delete from tab1 where id = &m_id;
commit;

The fact that the primary key index is created as a non-unique index isn’t a factor that affects this demonstration.

Given the query and the data in the table, you won’t be surprised by the result of the query from the first session (for convenience I’ve captured the selected value using the ‘column new_value’ option). Here’s the result of the query and its execution plan:


        ID
----------
         1

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     1 |     3 |     1   (0)| 00:00:01 |
|   1 |  FOR UPDATE                  |             |       |       |            |          |
|*  2 |   INDEX RANGE SCAN           | IDX_TAB1_PK |     1 |     3 |     0   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE            |             |     1 |     3 |            |          |
|   4 |     INDEX FULL SCAN (MIN/MAX)| IDX_TAB1_PK |     3 |     9 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"= (SELECT MIN("ID") FROM "TAB1" "TAB1"))

At this point the program issues instructions to repeat the query from a second session, then waits for you to press Return. When you run the same query from another session it’s going to see the data in read-consistent mode and try to select and lock the row where ID = 1, so the second session is going to hang waiting for the first session to commit or rollback.

Here’s the key question: what’s the second session going to return when you allow the first session to continue, delete the row it has selected, and commit ? Here’s the answer if you’re running 10.2.0.3 or 11.1.0.6 (which is what I happen to have easily available):

SQL> select  id
  2  from    tab1
  3  where   id = (
  4              select  min(id)
  5              from    tab1
  6          )
  7  for update
  8  ;

        ID
----------
         2

1 row selected.

Now, this seems perfectly reasonable to me – especially since I’ve read Tom Kyte’s notes on “write consistency” and seen the “rollback and restart” mechanism that kicks in when updates have to deal with data that’s changed since the start of the update. Session 2 had a (select for) update, and when it finally got to a point where it could lock the data it found that the read-consistent version of the data didn’t match the current version of the data so it restarted the statement at a new SCN. At the new SCN the current highest value was 2.

Now here’s what happened when I ran the test under 11.2.0.2:

SQL> select  id
  2  from    tab1
  3  where   id = (
  4              select  min(id)
  5              from    tab1
  6          )
  7  for update
  8  ;

no rows selected

The upgrade produces a different answer !

At first sight (or guess) it looks as if the query has run in two parts – the first part producing the min(id) of 1 using a read-consistent query block, with the second part then using the resulting “known value” to execute the outer select (shades of “precompute_subquery”) and restarting only the second part when it discovers that the row it has been waiting for has gone away.

It doesn’t really matter whether you think the old behaviour or the new behaviour is correct – the problem is that the behaviour has changed in a way that could silently produce unexpected results. Be careful if any of your code uses select for update with subqueries.

As a defensive measure you might want to change the code to use the serializable isolation level – that way the upgraded code will crash with Oracle error ORA-08177 instead of silently giving different answers:

SQL> alter session set isolation_level = serializable;

Session altered.

SQL> get afiedt.buf
  1  select  /*+ gather_plan_statistics */
  2          id
  3  from    tab1
  4  where   id = (
  5              select  min(id)
  6              from    tab1
  7          )
  8* for update
  9  /
from    tab1
        *
ERROR at line 3:
ORA-08177: can't serialize access for this transaction

It might be a way of avoiding this specific problem, of course, but it’s not a frequently used feature (the first pages of hits on Google are mostly about SQL Server) so who knows what other anomalies this change in isolation level might introduce.

46 Comments »

  1. Definitely an eye-opener.
    > It doesn’t really matter whether you think the old behaviour or the new behaviour is correct
    Just as long as it’s deliberate.

    Comment by Dom Brooks — March 21, 2011 @ 11:18 am BST Mar 21,2011 | Reply

    • And the worrying thing about being “deliberate” is that sometimes a MOS (Metalink) note can suggest it’s deliberate (the catch phrase is “expected behaviour”) when it isn’t really. I found some (MOS) metalink notes about this one that suggested it was deliberate – which is why I didn’t describe it as a possible bug.

      Comment by Jonathan Lewis — March 26, 2011 @ 12:49 pm BST Mar 26,2011 | Reply

  2. I wonder if this change is documented. To document the change the writer would have to provide a test case like yours to really explain the change.
    Whether documented or not, it will bite.
    (BTW : Need to test it in 11.2.0.1 ?)

    Comment by Hemant K Chitale — March 22, 2011 @ 7:30 am BST Mar 22,2011 | Reply

  3. I get “no rows selected” in 11.2.0.1.

    Comment by Hemant K Chitale — March 22, 2011 @ 2:57 pm BST Mar 22,2011 | Reply

  4. Found two Metalink entries on this :

    Bug 11799526: SUB-QUERY IN SELECT FOR UPDATE EXECUTES PREMATURELY, CAUSES WRONG RESULT

    Bug 10177779: SELECT FOR UPDATE RETURNS WRONG RESULTS WITH SUBQUERY.

    one closed as no-bug

    regards

    Comment by Nilo Segura — March 23, 2011 @ 2:08 pm BST Mar 23,2011 | Reply

  5. there are some things that changed in 11.2

    “… It doesn’t really matter whether you think the old behaviour or the new behaviour is correct – the problem is that the behaviour has changed in a way that could silently produce unexpected results. …”

    I don’t agree with that.
    It does matter whether the new behaviour is correct or rather a bug – it could influence your decision to use 11.2 at all.

    See for example

    http://marogel.wordpress.com/2011/03/27/prevent-validation-of-check-constraints-in-11-2-0-2/

    about a new behaviour in 11.2 which could lead to the decision “don’t use 11.2 at all”

    Comment by Matthias Rogel — March 27, 2011 @ 5:56 pm BST Mar 27,2011 | Reply

  6. FYI

    Bug 11869076: CHANGE IN LOCKING BEHAVIOUR FOR DML WITH SUBQUERY IN 11.2

    Comment by Óscar Pérez — April 1, 2011 @ 11:23 am BST Apr 1,2011 | Reply

  7. Hello,

    Isn’t the behaviour on 11.2.0.2 a violation of read-consistency, i.e. Oracle’s claim that

    Oracle Database always enforces statement-level read consistency, which guarantees that data returned by a single query is committed and consistent with respect to a single point in time

    (excerpt from Oracle Database Concepts, chapter 9 Data Concurrency and Consistency)?

    There was never a point in time where that query should have returned no rows (well there was, right after creation of the table, when the table was empty, but this point in time is potentially a long time before the statement/transaction began).

    In this sense shouldn’t it be considered a bug (difference between documentation and actual behaviour)?

    kind regards
    Martin

    Comment by Martin Maletinsky — April 29, 2011 @ 10:41 am BST Apr 29,2011 | Reply

    • Martin,

      Personallly I’d call it a bug; it’s clearly incorrect behaviour – but it gets harder to label something as a bug when it has been reported as “expected behaviour”.

      Many years ago (in the v7.2 timeline) I was given the following “definition” by someone on support: “Yes, I know it’s wrong, but it’s in the specification so it’s not a bug.”

      Comment by Jonathan Lewis — April 29, 2011 @ 11:41 am BST Apr 29,2011 | Reply

  8. Hello Jonathan,

    Thank you for your reply. Do you agree on my point that the behaviour under 11.2.0.2 is contradictory to the read consistency as cited from the Oracle Database Concepts in my initial comment?
    If this is the case, then there is a contradiction in the specification by (a) claiming read consistency and by (b) labelling something “expected behaviour” which contradicts (a).

    kind regards
    Martin

    Comment by Martin Maletinsky — April 29, 2011 @ 1:28 pm BST Apr 29,2011 | Reply

  9. [...] one here from Jonathan Lewis that may or may not be a [...]

    Pingback by Select For Update fetching across commit « OraStory — June 7, 2011 @ 10:45 am BST Jun 7,2011 | Reply

  10. The Oracle Support answer was …. “The bug has meanwhile been fixed and the fix is included in 11.2.0.3.”

    Regards.

    Comment by Óscar Pérez — June 17, 2011 @ 11:01 am BST Jun 17,2011 | Reply

  11. As I’ve commented, Bug 11869076: CHANGE IN LOCKING BEHAVIOUR FOR DML WITH SUBQUERY IN 11.2.

    And RELATED BUGS:
    Bug 10177779: SELECT FOR UPDATE RETURNS WRONG RESULTS WITH SUBQUERY.
    Bug 9176765: THE RECORD COUNT IS DIFFERENT BY THE SPECIFIED COLUMN.

    I only have followed the John Ospino’s SR.
    Regards.

    Comment by Óscar Pérez — June 18, 2011 @ 8:12 am BST Jun 18,2011 | Reply

  12. The patch is ready and running ok!

    From Metalink:

    The patch is ready, it can be downloaded from
    Patches and Updates -> Patch Search -> Patch Name or Number
    enter 11869076 and choose the HP-UX Itanium version.

    Regards, Óscar.

    Comment by Óscar Pérez — August 11, 2011 @ 9:31 am BST Aug 11,2011 | Reply

  13. Here is example of write inconsistency:

    drop table t purge;
    create table t as select 1 id, 0 val from dual;
    insert into t values(2, 0);
    update t set val = 1;
    
    -- Then in second session 
    update t set val = val + 1;
    -- Session waits for the first
    
    -- Then commit first
    commit;
    
    -- And query in second
    select * from t;

    The result will be

    SQL> select * from t;
    
            ID        VAL
    ---------- ----------
             1          2
             2          1

    Comment by Valentin Nikotin — December 23, 2011 @ 3:49 pm BST Dec 23,2011 | Reply

    • Valentin,

      That’s an interesting example of something that’s arguably doing the wrong thing.

      According to your test result, the second session has updated (1,1) to (1,2) – which means it has seen some of the effects of the transaction executed by the first session, but it hasn’t updated (2,1) to (2,2) – which means it hasn’t seen all of the effects of the transaction executed by the first session.

      In principle, the second transaction should have done one of three things:
      a) Failed and rolled back (if you have made the second session serializable this would have happened with an ORA-08177)
      b) Rolled back and restarted at an SCN later than the commit SCN generated by the first session.

      As it is, the second session has behaved inconsistently.
      I’ve repeated your test on 8.1.7.4 and 11.2.0.2, by the way, and the result is the same.

      Comment by Jonathan Lewis — December 23, 2011 @ 7:04 pm BST Dec 23,2011 | Reply

  14. It seems that the original problem described in the post refers to using the Scalar Subquery Caching in inappropriate place. This could be seen from 10200 trace for example. So, I created a simple test based on the same idea to show really wrong results:

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    -- SESS1
    
    SQL> create table t1 (id, max1, max2) as select level, 0, 0 from dual connect by level <= 3;
    
    Table created.
    
    SQL> update t1 set id = 0 where id = 3;
    
    1 row updated.
    
    -- SESS2
    
    SQL> update t1 a
      2  set max1 = +(select max(id) from t1),
      3      max2 = -(select max(id) from t1)
      4  where a.id in (1, 3);
    
    -- waiting for session1
    
    -- SESS1
    
    SQL> commit;
    
    Commit complete.
    
    SQL>
    
    -- SESS2
    
    1 row updated.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> select * from t1 where id = 1;
    
            ID       MAX1       MAX2
    ---------- ---------- ----------
             1          2         -3
    
    SQL>
    

    Comment by Valentin Nikotin — February 17, 2012 @ 10:29 pm BST Feb 17,2012 | Reply

  15. We hit another “strange/expected” behaviour related to this post after upgrade to 11g. We have simple select for update command without subquery, but the query is using function based index:

    -- SESSION1:
    create table aaa (n number, state char(1), s char(300));
    create index aaa_i on aaa(NVL(state,'N'));
    insert into aaa values(1,null,'row 1');
    insert into aaa values(2,null,'row 2');
    insert into aaa values(3,null,'row 3');
    commit;
    
    -- SESSION1:
    select n from aaa where NVL(state,'N') = 'N' and rownum<=1 for update;
    
    
    -- SESSION2:
    select n from aaa where NVL(state,'N') = 'N' and rownum<=1 for update;
    -- select is waiting for lock from session1
    
    -- SESSION1:
    update aaa set state='Y' where n=1;
    commit;
    
    -- SESSION2
    -- lock is released and select returns row 1 in 11R2 (But in 9i it returns row 2)
    

    Without function based index the query returns row 2. Only with FBI and in 11R2 it returns row 1.

    From our point of view only one result is good (I prefer row 2 of course ;) ). But in general the existence of FBI can’t affect the read consistent result.

    Comment by Vladimir Jelinek — September 18, 2012 @ 8:48 am BST Sep 18,2012 | Reply

    • Vladimir,

      Interesting example. Thanks.
      Have you raised this with Oracle support ?

      Comment by Jonathan Lewis — September 18, 2012 @ 12:24 pm BST Sep 18,2012 | Reply

      • Yes, we have SR. BDE tried to close this as “This is the expected behavior dicussed in Bug 10177779 SELECT FOR UPDATE RETURNS WRONG RESULTS WITH SUBQUERY. “.
        Now we are trying to hold the line with:
        – reference to Bug 11869076 which was accepted as a bug (we found it in this blog – thanks :-) )
        – our query has no subquery so it is not the same as Bug 10177779
        – the result differs if FBI is used or not – but it shouldn’t. Just one result can be the right one.
        We’ll see it’s very fresh now.

        Comment by Vladimir Jelinek — September 18, 2012 @ 1:23 pm BST Sep 18,2012 | Reply

      • I’m Vladimir’s collegue. We have also used bug 11869076, which was already mentioed here in discussion. The root cause of problem was requested, becase in DML with subquery, it was qutie straightforward (inappropiate scalar query caching). However, why is FBI causing inconsistent behaviour in 11gR2 (issue not reproducible in 11gR1 as far I know).

        There is plenty of code in Corebanking application depending on mentioned code (fortunately, without FBI). This specific bug means some messages are sent to bank’s clients twice …

        Comment by Pavol Babel — September 18, 2012 @ 1:46 pm BST Sep 18,2012 | Reply

        • Pavol,

          I ran Vladimir’s example against 11.1.0.7 and 10.2.0.3 – and got the same result in both cases.

          Comment by Jonathan Lewis — September 18, 2012 @ 1:57 pm BST Sep 18,2012

        • Jonathan,

          Very interesting. I personally checked only 9.2.0.8 and 11.2.0.3. From oracle support:

          DIAGNOSTIC ANALYSIS:
          Problem does not occur in 9.2.0.8 or 11.1.0.7

          So I will check 10.2.0.5, but we do not have any 11gR1 and I’m to lazy to install new OH (and I believe you, of course :) :) )

          Comment by Pavol Babel — September 18, 2012 @ 4:56 pm BST Sep 18,2012

      • Small variation to confuse the issue more: If you include the column ‘state’ as part of the select column in session 2, it returns ‘row 2′ instead.

        Comment by Andy — September 18, 2012 @ 1:58 pm BST Sep 18,2012 | Reply

        • We know. We used it as a workaround. But I don’t want to confuse it more :-).

          Comment by Vladimir Jelinek — September 18, 2012 @ 2:01 pm BST Sep 18,2012

        • Re-entered on behalf of Pavol Babel

          Exactly, when we add “state” column to SELECT list of SELECT FOR UPDATE statement, then it works as before :) But when we add NVL(state, ‘N’), we have this new “unpopula” behaviour :) To be honest, the hotfix to production added state column to SELECT clause… :)

          Comment by Jonathan Lewis — September 18, 2012 @ 5:01 pm BST Sep 18,2012

        • Guys, have you tested this behaviour for virtual column instead of functional expression ? I don’t have any db near to check it myself.

          Comment by Valentin Nikotin — September 18, 2012 @ 6:48 pm BST Sep 18,2012

        • No, we have not. In fact, we have more problems after upgrade, so: “We live in interesting times” now. Your idea is worth to try.
          But mainly, I’m scared by MOS approach to this problem. Based on their comments in related bugs it seems, that even they don’t know what is right result. From my point of view, the old one because it’s at least consistent. This time we have only problem with FBI. And we have only few FBI, so it’s easy to find the trap. But if this behaviour became “expected by design” in general, we’ll have very big problem, cause our developers using this way of locking a lot.

          Comment by Vladimir Jelinek — September 18, 2012 @ 7:35 pm BST Sep 18,2012

    • Vladimir,

      That is an interesting test case. I experimented with it for a bit over an hour using Oracle Database 11.2.0.2.

      * Setting the OPTIMIZER_FEATURES_ENABLE parameter at the session level in session 2 to a value of less than or equal to 9.2.0.8 results in session 2 selecting the number 2 (rather than 1):
      ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE=’9.2.0.8′;

      * Setting the OPTIMIZER_FEATURES_ENABLE parameter back to 11.2.0.2, and changing the OPTIMIZER_MODE from the 11.2.0.2 default of ALL_ROWS to CHOOSE in session 2 results in session 2 selecting the number 2 (rather than 1):
      ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE=’11.2.0.2′;
      ALTER SESSION SET OPTIMIZER_MODE=’CHOOSE';

      * Setting the OPTIMIZER_MODE from ALL_ROWS to CHOOSE in both sessions, results in session 1 selecting the number 1, while session 2 selects the number 2 (rather than 1).

      * Collecting the table statistics using DBMS_STATS.GATHER_TABLE_STATS with OPTIMIZER_MODE set to CHOOSE results in both sessions selecting the number 1.

      The execution plan with the OPTIMIZER_MODE set to CHOOSE (the same seen with OPTIMIZER_FEATURES_ENABLE=’9.2.0.8′ and no statistics for table AAA) (the RULE based optimizer cannot use function based indexes, if I recall correctly):

      Plan hash value: 2311360649
       
      -------------------------------------
      | Id  | Operation            | Name |
      -------------------------------------
      |   0 | SELECT STATEMENT     |      |
      |   1 |  FOR UPDATE          |      |
      |   2 |   BUFFER SORT        |      |
      |*  3 |    COUNT STOPKEY     |      |
      |*  4 |     TABLE ACCESS FULL| AAA  |
      -------------------------------------
       
      Predicate Information (identified by operation id):
      ---------------------------------------------------
         3 - filter(ROWNUM<=1)
         4 - filter(NVL("STATE",'N')='N')
       
      Note
      -----
         - rule based optimizer used (consider using cbo)
      

      The execution plan with OPTIMIZER_MODE set to ALL_ROWS:

      Plan hash value: 3492588397
       
      ----------------------------------------------------------------------------------------
      | Id  | Operation                      | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
      ----------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT               |       |       |       |     1 (100)|          |
      |   1 |  FOR UPDATE                    |       |       |       |            |          |
      |   2 |   BUFFER SORT                  |       |       |       |            |          |
      |*  3 |    COUNT STOPKEY               |       |       |       |            |          |
      |   4 |     TABLE ACCESS BY INDEX ROWID| AAA   |     1 |    15 |     1   (0)| 00:00:01 |
      |*  5 |      INDEX RANGE SCAN          | AAA_I |     1 |       |     1   (0)| 00:00:01 |
      ----------------------------------------------------------------------------------------
       
      Predicate Information (identified by operation id):
      ---------------------------------------------------
         3 - filter(ROWNUM<=1)
         5 - access("AAA"."SYS_NC00004$"='N')
       
      Note
      -----
         - dynamic sampling used for this statement (level=2)
      

      So, it appears that the switch of the OPTIMIZER_MODE from CHOOSE to ALL_ROWS resulted in a change of the execution plan – rather than the rows being access in index order, the rows are accessed in the order in which the rows appear in the table’s blocks.

      Jonathan, could this change in which row is selected be caused by the row moving its physical location in the block because the row is now longer than before (a NULL was set to a value of ‘Y’)? I am having trouble remembering if your latest book mentioned something along the lines that if there is sufficient free space in the block, that the row could be moved within the block with only a pointer change needed, thus saving the work of relocating the other rows in that block (I could very well be incorrectly remembering this concept).

      Comment by Charles Hooper — September 19, 2012 @ 1:51 am BST Sep 19,2012 | Reply

      • The second to the last paragraph on page 7 of “Oracle Core” is what I am referencing at the end of my previous comment.

        Comment by Charles Hooper — September 19, 2012 @ 1:57 am BST Sep 19,2012 | Reply

      • Hermant,

        I don’t think it has anything to do with row updating (when size of row is increasing), nor CHOOSE/ALL_ROWS

        1. we were not using CHOOSE in 9.2.0.8
        2. the FBI was used in hint in our application in 9.2.0.8 (but Vladimir didn’t add hints to synthetic testcace), so CBO was used to generate execution plan even in 9.2.0.8

        Sure, row will be copied to block free space and pointer in row directory will be changed. But if you drop FBI and create classic B-tree index, session 2 will select row number 2 as well.

        It seems oracle forgets to restart SELECT FOR UPDATE statement in this specific situation. Statement generates less consistent and current gets as it should

        Regards
        Pavol Babel

        Comment by Pavol Babel — September 19, 2012 @ 7:16 am BST Sep 19,2012 | Reply

  16. From experience, you will have to push past MOS level 1 support to get anything done. Some of the answers I got from their level 1 support are really amusing :)

    Comment by Andy — September 18, 2012 @ 8:50 pm BST Sep 18,2012 | Reply

    • We should raise severity level 1 bug only because of productional system outage etc. We “fixed” code exactly in way you suggested and it works. But maybe you are right, oracle is cheating regarding “expected behaviour”, so … :)

      select n, state from aaa where NVL(state,'N') = 'N' and rownum<=1 for update;
      

      Comment by Pavol Babel — September 18, 2012 @ 11:27 pm BST Sep 18,2012 | Reply

      • I’ve tried usage of virtual column instead of fbi (based on Valentin’s suggestion).
        I’ve done 5 testcases. Each testcase has the same beginning:

        drop table aaa;
        create table aaa (n number, state char(1), s char(300), snew char(1) GENERATED ALWAYS AS (NVL(state,'N')) VIRTUAL);
        create index aaa_i on aaa(snew);
        
        insert into aaa(n,state,s) values(1,null,'row 1');
        insert into aaa(n,state,s) values(2,null,'row 2');
        commit;
        

        Column “snew” is new virtual column with index.
        I did the same locking scenario as in my previous example:

        -- SESSION1:
        select /*+ INDEX(aaa) */ n from aaa where NVL(state,'N') = 'N' and rownum<=1 for update;
        -- SESSION2:
        select /*+ INDEX(aaa) */ n from aaa where NVL(state,'N') = 'N' and rownum<=1 for update;
        -- SESSION1:
        update aaa set state='Y' where n=1;
        commit;
        

        In each scenario, I used different “select for update” command. They are here, together with their results:

        -- scen 1
        select /*+ INDEX(aaa) */ n            from aaa where NVL(state,'N') = 'N' and rownum<=1 for update;
        -- result is: row 1
        
        -- scen 2
        select /*+ INDEX(aaa) */ n,state      from aaa where NVL(state,'N') = 'N' and rownum<=1 for update;
        -- result is: row 2
        
        -- scen 3
        select /*+ INDEX(aaa) */ n            from aaa where snew = 'N'           and rownum<=1 for update;
        -- result is: row 1
        
        -- scen 4
        select /*+ INDEX(aaa) */ n,snew       from aaa where snew = 'N'           and rownum<=1 for update;
        -- result is: row 1
        
        -- scen 5
        select /*+ INDEX(aaa) */ n,snew,state from aaa where snew = 'N'           and rownum<=1 for update;
        -- result is: row 2
        

        Scenario 1 and 2 is in fact the same as FBI usage, with the same results.
        Result in scenario 5 is little surprise, because the column state is not in the where clause, but it presence in select clause cause different result.
        It seems, that when Oracle is forced to read this column from table, then it’s value is used to reevaluate the where clause (directly in scenario 2 and indirectly in scenario 5 by recalculationof the virtual column value).

        Comment by Vladimir Jelinek — September 19, 2012 @ 3:46 pm BST Sep 19,2012 | Reply

  17. FYI: Our problem with “select for update when FBI is used” has been approved as a bug by Oracle development after 3 months.
    So this expected behaviour will be fixed in 12c:
    Bug 14605021 : SELECT FOR UPDATE RETURNS WRONG RESULT WHEN FUNCTION BASED INDEX USED

    Comment by Vladimir Jelinek — December 5, 2012 @ 1:41 pm BST Dec 5,2012 | Reply

    • Vladimir,

      Thanks for the update.

      Comment by Jonathan Lewis — December 13, 2012 @ 9:49 pm BST Dec 13,2012 | Reply

      • Jonathan,

        Fortunately, when Oracle BDE states that some weird behavior as “expected behavior by design”, there is still wait to persuade him it is a bug. I think your blog and this thread was also helpful in our case.

        Regards
        Pavol Babel

        Comment by Pavol Babel — October 21, 2013 @ 9:57 am BST Oct 21,2013 | Reply

  18. Another example of inconsistency related to undocumented hint PRECOMPUTE_SUBQUERY in 11.2 :

    [oracle@SB1 ~]$ sqlplus / as sysdba
    
    SQL*Plus: Release 11.2.0.3.0 Production on Sat Dec 29 02:20:02 2012
    
    Copyright (c) 1982, 2011, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning and Data Mining options
    
    SQL> @sql
    SQL> drop table t0001 purge;
    
    Table dropped.
    
    SQL> create table t0001 (id number);
    
    Table created.
    
    SQL> declare
      2    c number;
      3  begin
      4    for i in 1 .. 10 loop
      5      select count(*) into c from t0001 where id in (select /*+ PRECOMPUTE_SUBQUERY */ * from t0001);
      6      dbms_output.put_line(c);
      7      insert into t0001 values (1);
      8      commit;
      9    end loop;
     10    for i in 1 .. 10 loop
     11      select count(*) into c from t0001 where id in (select * from t0001);
     12      dbms_output.put_line(c);
     13      insert into t0001 values (1);
     14      commit;
     15    end loop;
     16  end;
     17  /
    0
    0
    0
    0
    0
    0
    0
    0
    0
    0
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    
    PL/SQL procedure successfully completed.
    

    Comment by Valentin Nikotin — December 28, 2012 @ 10:52 pm BST Dec 28,2012 | Reply

    • Valentin,

      I’m not totally surprised that /*+ precompute subquery */ should produce contradictory results – especially when it appears in a cursor that’s pinned by the pl/sql cursor cache. Strangely, though, my results are different from yours despite running your test on 11.2.0.3.

      I get 0 as the first result, then 1 for the next 9 results, before getting 10 – 19. The 10053 trace shows that I re-optimise the hinted SQL, getting two different plans (although the first one disappears from the library cache very promptly). The first plan is:

      ---------------------------------------+-----------------------------------+
      | Id  | Operation            | Name    | Rows  | Bytes | Cost  | Time      |
      ---------------------------------------+-----------------------------------+
      | 0   | SELECT STATEMENT     |         |       |       |     1 |           |
      | 1   |  SORT AGGREGATE      |         |     1 |       |       |           |
      | 2   |   FILTER             |         |       |       |       |           |
      | 3   |    TABLE ACCESS FULL | T0001   |     1 |       |     2 |  00:00:01 |
      ---------------------------------------+-----------------------------------+
      Predicate Information:
      ----------------------
      2 - filter(NULL IS NOT NULL)
      
      

      The second plan, that appears after two inserts is:

      --------------------------------------+-----------------------------------+
      | Id  | Operation           | Name    | Rows  | Bytes | Cost  | Time      |
      --------------------------------------+-----------------------------------+
      | 0   | SELECT STATEMENT    |         |       |       |     2 |           |
      | 1   |  SORT AGGREGATE     |         |     1 |    13 |       |           |
      | 2   |   TABLE ACCESS FULL | T0001   |     1 |    13 |     2 |  00:00:01 |
      --------------------------------------+-----------------------------------+
      Predicate Information:
      ----------------------
      2 - filter("ID"=1)
      
      

      Possibly we have some differences in dynamic sampling or cardinality feedback settings.

      Comment by Jonathan Lewis — December 29, 2012 @ 3:33 pm BST Dec 29,2012 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,990 other followers