_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.

This entry was written by Timur Akhmadeev, posted on October 5, 2010 at 06:01, filed under CBO, Oracle, SQL and tagged 11gR2, bug, connect by. Bookmark the permalink. Follow any comments here with the RSS feed for this post. Post a comment or leave a trackback: Trackback URL.

10 Comments

Noons

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

Wow! Good catch, Timur!
- Timur Akhmadeev
  
  Posted October 5, 2010 at 07:50 | Permalink | Reply
  
  Hi Noons,
  
  yeah, it’s so gooood to catch (and kill) a bug or two 🙂
Timur Akhmadeev

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.

Timur Akhmadeev

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           |
-----------------------------------------

Timur Akhmadeev

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.

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], [], [], [], []

Timur Akhmadeev

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

Timur Akhmadeev

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)

Timur Akhmadeev's blog