_connect_by_use_union_all

This is just a short note on the parameter introduced in the 11gR2 called _connect_by_use_union_all. I’ve noticed it for the first time in Doc ID 7210630.8, which gives a brief overview of the changes made to the way CBO generates plans for hierarchical queries. As usually happens, the change helps to one problem, but produces a bunch of unexpected side effects, such as wrong or incorrect results, and even ORA-00904 in a simple case. All these bugs have been fixed in the 11.2.0.2 patch set, but who knows how many issues related to the change are still there? If you see something unusual with a standard Oracle hierarchical query in the 11gR2, I think it’s good to try turning this parameter off and see if it helps.

About these ads

10 Comments

  1. Posted October 5, 2010 at 07:42 | Permalink | Reply

    Wow! Good catch, Timur!

  2. Posted January 21, 2011 at 11:46 | Permalink | Reply

    A small update: it looks like the value you would want to try for this parameter in case of a problem is “old_plan_mode” rather than “false”. And here is a new bug 10218578 related to the parameter; claimed to be fixed in 12.1.

  3. Posted February 23, 2011 at 21:53 | Permalink | Reply

    Another update: it looks like this new feature allows Oracle to do “magic” things with CONNECT BY queries that build hierarchy over a join. The documentation states

    A join, if present, is evaluated first, whether the join is specified in the FROM clause or with WHERE clause predicates.

    But look at this plan which is built on a 11.2.0.2 with a query that has two joins in a CONNECT BY query block:

    -----------------------------------------
    | Id  | Operation                       |
    -----------------------------------------
    |   0 | SELECT STATEMENT                |
    |*  1 |  CONNECT BY WITH FILTERING      |
    |   2 |   NESTED LOOPS OUTER            |
    |   3 |    NESTED LOOPS OUTER           |
    |   4 |     TABLE ACCESS BY INDEX ROWID |
    |*  5 |      INDEX UNIQUE SCAN          |
    |*  6 |     TABLE ACCESS BY INDEX ROWID |
    |*  7 |      INDEX RANGE SCAN           |
    |   8 |    INLIST ITERATOR              |
    |   9 |     TABLE ACCESS BY INDEX ROWID |
    |* 10 |      INDEX RANGE SCAN           |
    |  11 |   NESTED LOOPS OUTER            |
    |  12 |    NESTED LOOPS OUTER           |
    |  13 |     NESTED LOOPS                |
    |  14 |      CONNECT BY PUMP            |
    |  15 |      TABLE ACCESS BY INDEX ROWID|
    |* 16 |       INDEX UNIQUE SCAN         |
    |* 17 |     TABLE ACCESS BY INDEX ROWID |
    |* 18 |      INDEX RANGE SCAN           |
    |  19 |    INLIST ITERATOR              |
    |  20 |     TABLE ACCESS BY INDEX ROWID |
    |* 21 |      INDEX RANGE SCAN           |
    -----------------------------------------
    
  4. Posted January 31, 2012 at 22:36 | Permalink | Reply

    One more bug associated with this parameter introduced in 11.2.0.3: Bug 13117043 – ORA-600 [13009] Raised for SELECT FOR UPDATE on Hierarchical Query.

  5. Igor Usoltsev
    Posted May 10, 2012 at 12:29 | Permalink | Reply

    And one more for 11.2.0.1, 11.2.0.2 – Bug 9906422 – ORA-600 [qctVCO:csform] from query using WITH, CONNECT BY and CLOBs / ORA-979 with CONNECT BY and SUBSTR [ID 9906422.8]
    This may noticed in simple query on the GV$SQL_PLAN table with parameter _connect_by_use_union_all = TRUE (without any WITH, CLOBs and SUBSTR usage):

    11.2.0.1.ORC11201@SYS SQL> SELECT
      2    id as OPERATION_ID
      3  , lpad(' ', 2 * level) || pt.operation || ' ' || pt.options "Query Plan"
      4  , pt.object_owner||nvl2(pt.object_node,'','.')||pt.object_name||nvl2(pt.object_node,'@','')||pt.object_node, pt.object_alias, pt.qblock_name
      5  , pt.cost, pt.cardinality, pt.bytes, pt.cpu_cost, pt.io_cost, pt.temp_space, pt.access_predicates, pt.filter_predicates
      6  , pt.other_xml, pt.temp_space
      7    FROM (
      8          select * from gv$sql_plan
      9                  where
     10                  sql_id = '57pfs5p8xc07w'
     11                  and child_number = 0
     12                  and inst_id = 1
     13                  ) pt
     14  CONNECT BY PRIOR pt.id = pt.parent_id
     15   START WITH pt.id = 0
     16  /
            select * from gv$sql_plan
                          *
    ERROR at line 8:
    ORA-00600: internal error code, arguments: [qctVCO:csform], [0], [0], [0], [0], [112], [2], [224], [], [], [], []
    • Posted May 10, 2012 at 16:51 | Permalink | Reply

      Hi Igor

      thanks for that. I tried the query on one of 11.2.0.2.0 and it works OK for me.

      • Igor Usoltsev
        Posted May 10, 2012 at 17:17 | Permalink | Reply

        Possible platform specific:

        11.2.0.2.ORC11202@SYS SQL> @inst
        
        INST_ID INSTANCE_NAME        HOST_NAME      VERSION    OPEN_MODE  PLATFORM_NAME        STATUS PARALLEL
        ------- -------------------- -------------- ---------- ---------- -------------------- ------ --------
        1*      orc11202             oracle-dom01g  11.2.0.2.0 READ WRITE Linux x86 64-bit     OPEN   NO      
        
        11.2.0.2.ORC11202@SYS SQL> SELECT
          2    id as OPERATION_ID
          3  , lpad(' ', 2 * level) || pt.operation || ' ' || pt.options "Query Plan"
          4  , pt.object_owner||nvl2(pt.object_node,'','.')||pt.object_name||nvl2(pt.object_node,'@','')||pt.object_node, pt.object_alias, pt.qblock_name
          5  , pt.cost, pt.cardinality, pt.bytes, pt.cpu_cost, pt.io_cost, pt.temp_space, pt.access_predicates, pt.filter_predicates
          6  , pt.other_xml, pt.temp_space
          7    FROM (
          8          select * from gv$sql_plan
          9                  where sql_id = 'b6n8z254t802k'
         10                  and child_number = 0
         11                  and inst_id = 1
         12                  ) pt
         13  CONNECT BY PRIOR pt.id = pt.parent_id
         14   START WITH pt.id = 0
         15  /
                select * from gv$sql_plan
                              *
        ERROR at line 8:
        ORA-00600: internal error code, arguments: [qctVCO:csform], [0], [0], [0], [0], [112], [2], [224], [], [], [], []
        
        
        11.2.0.2.ORC11202@SYS SQL> @param_ _connect_by_use_union_all
        
        NAME                                       VALUE                                   
        ------------------------------------------ ----------------------------------------
        _connect_by_use_union_all                  TRUE                                    
        • Posted May 10, 2012 at 18:58 | Permalink | Reply

          I have the same platform. Do you have fixed objects stats in place (I don’t)? Maybe some other optimizer related parameters? Here are plans with two possible modes:

          --------------------------------------------------------------------------------------------------
          | Id  | Operation                               | Name              | Rows  | Bytes | Cost (%CPU)|
          --------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT                        |                   |       |       |     1 (100)|
          |*  1 |  CONNECT BY NO FILTERING WITH START-WITH|                   |       |       |            |
          |*  2 |   FIXED TABLE FIXED INDEX               | X$KQLFXPL (ind:4) |     1 |  6363 |     0   (0)|
          --------------------------------------------------------------------------------------------------
          
          Outline Data
          -------------
          
            /*+
                BEGIN_OUTLINE_DATA
                IGNORE_OPTIM_EMBEDDED_HINTS
                OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
                DB_VERSION('11.2.0.2')
                ALL_ROWS
                OUTLINE_LEAF(@"SEL$863EBB6B")
                MERGE(@"SEL$1B7D9AE9")
                OUTLINE_LEAF(@"SEL$C55D3CEC")
                MERGE(@"SEL$2F35337B")
                OUTLINE_LEAF(@"SEL$E56701A0")
                MERGE(@"SEL$5ED1C707")
                OUTLINE_LEAF(@"SET$1")
                OUTLINE_LEAF(@"SEL$1")
                OUTLINE(@"SEL$2")
                OUTLINE(@"SEL$1B7D9AE9")
                MERGE(@"SEL$68B588A0")
                OUTLINE(@"SEL$3")
                OUTLINE(@"SEL$2F35337B")
                MERGE(@"SEL$0EE6DB63")
                OUTLINE(@"SEL$4")
                OUTLINE(@"SEL$5ED1C707")
                MERGE(@"SEL$61262C81")
                OUTLINE(@"SEL$5")
                OUTLINE(@"SEL$68B588A0")
                MERGE(@"SEL$7")
                OUTLINE(@"SEL$8")
                OUTLINE(@"SEL$0EE6DB63")
                MERGE(@"SEL$10")
                OUTLINE(@"SEL$11")
                OUTLINE(@"SEL$61262C81")
                MERGE(@"SEL$13")
                OUTLINE(@"SEL$6")
                OUTLINE(@"SEL$7")
                OUTLINE(@"SEL$9")
                OUTLINE(@"SEL$10")
                OUTLINE(@"SEL$12")
                OUTLINE(@"SEL$13")
                NO_ACCESS(@"SEL$1" "connect$_by$_working$_set$_010"@"SEL$1")
                NO_CONNECT_BY_FILTERING(@"SEL$1")
                CONNECT_BY_COMBINE_SW(@"SEL$1")
                FULL(@"SEL$E56701A0" "P"@"SEL$13")
                FULL(@"SEL$C55D3CEC" "connect$_by$_pump$_003"@"SEL$3")
                FULL(@"SEL$C55D3CEC" "P"@"SEL$10")
                LEADING(@"SEL$C55D3CEC" "connect$_by$_pump$_003"@"SEL$3" "P"@"SEL$10")
                USE_NL(@"SEL$C55D3CEC" "P"@"SEL$10")
                FULL(@"SEL$863EBB6B" "P"@"SEL$7")
                END_OUTLINE_DATA
            */
          
          Predicate Information (identified by operation id):
          ---------------------------------------------------
          
             1 - access("PT"."PARENT_ID"=PRIOR NULL)
                 filter("KQLFXPL_OPID"=0)
             2 - filter(("KQLFXPL_SQLID"='gypy7xwbp3j30' AND "KQLFXPL_CHNO"=0 AND "INST_ID"=1))
          
          -----------------------------------------------------------------------------------
          | Id  | Operation                 | Name              | Rows  | Bytes | Cost (%CPU)
          -----------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT          |                   |       |       |     2 (100)
          |*  1 |  CONNECT BY WITH FILTERING|                   |       |       |
          |*  2 |   FIXED TABLE FIXED INDEX | X$KQLFXPL (ind:4) |     1 |  6363 |     0   (0)
          |   3 |   NESTED LOOPS            |                   |     1 |  6376 |     0   (0)
          |   4 |    CONNECT BY PUMP        |                   |       |       |
          |*  5 |    FIXED TABLE FIXED INDEX| X$KQLFXPL (ind:4) |     1 |  6363 |     0   (0)
          -----------------------------------------------------------------------------------
          
          Outline Data
          -------------
          
            /*+
                BEGIN_OUTLINE_DATA
                IGNORE_OPTIM_EMBEDDED_HINTS
                OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
                DB_VERSION('11.2.0.2')
                ALL_ROWS
                OUTLINE_LEAF(@"SEL$863EBB6B")
                MERGE(@"SEL$1B7D9AE9")
                OUTLINE_LEAF(@"SEL$C55D3CEC")
                MERGE(@"SEL$2F35337B")
                OUTLINE_LEAF(@"SEL$E56701A0")
                MERGE(@"SEL$5ED1C707")
                OUTLINE_LEAF(@"SET$1")
                OUTLINE_LEAF(@"SEL$1")
                OUTLINE(@"SEL$2")
                OUTLINE(@"SEL$1B7D9AE9")
                MERGE(@"SEL$68B588A0")
                OUTLINE(@"SEL$3")
                OUTLINE(@"SEL$2F35337B")
                MERGE(@"SEL$0EE6DB63")
                OUTLINE(@"SEL$4")
                OUTLINE(@"SEL$5ED1C707")
                MERGE(@"SEL$61262C81")
                OUTLINE(@"SEL$5")
                OUTLINE(@"SEL$68B588A0")
                MERGE(@"SEL$7")
                OUTLINE(@"SEL$8")
                OUTLINE(@"SEL$0EE6DB63")
                MERGE(@"SEL$10")
                OUTLINE(@"SEL$11")
                OUTLINE(@"SEL$61262C81")
                MERGE(@"SEL$13")
                OUTLINE(@"SEL$6")
                OUTLINE(@"SEL$7")
                OUTLINE(@"SEL$9")
                OUTLINE(@"SEL$10")
                OUTLINE(@"SEL$12")
                OUTLINE(@"SEL$13")
                NO_ACCESS(@"SEL$1" "connect$_by$_working$_set$_010"@"SEL$1")
                CONNECT_BY_FILTERING(@"SEL$1")
                FULL(@"SEL$E56701A0" "P"@"SEL$13")
                FULL(@"SEL$C55D3CEC" "connect$_by$_pump$_003"@"SEL$3")
                FULL(@"SEL$C55D3CEC" "P"@"SEL$10")
                LEADING(@"SEL$C55D3CEC" "connect$_by$_pump$_003"@"SEL$3" "P"@"SEL$10")
                USE_NL(@"SEL$C55D3CEC" "P"@"SEL$10")
                FULL(@"SEL$863EBB6B" "P"@"SEL$7")
                END_OUTLINE_DATA
            */
          
          Predicate Information (identified by operation id):
          ---------------------------------------------------
          
             1 - access("PT"."PARENT_ID"=PRIOR NULL)
             2 - filter(("KQLFXPL_OPID"=0 AND "KQLFXPL_SQLID"='gypy7xwbp3j30' AND
                        "KQLFXPL_CHNO"=0 AND "INST_ID"=1))
             5 - filter(("KQLFXPL_SQLID"='gypy7xwbp3j30' AND "KQLFXPL_CHNO"=0 AND
                        "INST_ID"=1 AND "connect$_by$_pump$_003"."PRIOR pt.id
                        "=TO_NUMBER(DECODE("KQLFXPL_OPID",0,NULL,TO_CHAR("KQLFXPL_PAID")))))
          
          • Igor Usoltsev
            Posted May 10, 2012 at 20:37 | Permalink | Reply

            Without the fixed object stats too, and here is plan with _connect_by_use_union_all = FALSE | OLD_PLAN_MODE:

            -------------------------------------------------------------------------------------------------------------
            | Id  | Operation                               | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
            -------------------------------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT                        |                   |     1 |  6363 |     0   (0)| 00:00:01 |
            |*  1 |  CONNECT BY NO FILTERING WITH START-WITH|                   |       |       |            |       |
            |*  2 |   FIXED TABLE FIXED INDEX               | X$KQLFXPL (ind:4) |     1 |  6363 |     0   (0)| 00:00:01 |
            -------------------------------------------------------------------------------------------------------------
            
            Query Block Name / Object Alias (identified by operation id):
            -------------------------------------------------------------
            
               1 - SEL$5C160134
               2 - SEL$5C160134 / P@SEL$3
            
            Outline Data
            -------------
            
              /*+
                  BEGIN_OUTLINE_DATA
                  CONNECT_BY_COMBINE_SW(@"SEL$5C160134")
                  NO_CONNECT_BY_FILTERING(@"SEL$5C160134")
                  FULL(@"SEL$5C160134" "P"@"SEL$3")
                  OUTLINE(@"SEL$3")
                  OUTLINE(@"SEL$2")
                  MERGE(@"SEL$3")
                  OUTLINE(@"SEL$335DD26A")
                  OUTLINE(@"SEL$1")
                  MERGE(@"SEL$335DD26A")
                  OUTLINE_LEAF(@"SEL$5C160134")
                  ALL_ROWS
                  OPT_PARAM('_connect_by_use_union_all' 'false')
                  DB_VERSION('11.2.0.2')
                  OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
                  IGNORE_OPTIM_EMBEDDED_HINTS
                  END_OUTLINE_DATA
              */
            
            Predicate Information (identified by operation id):
            ---------------------------------------------------
            
               1 - access(TO_NUMBER(DECODE("KQLFXPL_OPID",0,NULL,TO_CHAR("KQLFXPL_PAID")))=PRIOR "KQLFXPL_OPID")
                   filter("KQLFXPL_OPID"=0)
               2 - filter("KQLFXPL_SQLID"='b6n8z254t802k' AND "KQLFXPL_CHNO"=0 AND "INST_ID"=1)

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

Follow

Get every new post delivered to your Inbox.

Join 290 other followers