В разных сессиях, разный план запросов

select * from table(dbms_xplan.display_cursor('cguqg2a507ffq',null,'advanced'));

with sql_shared as (
   select--+ NO_XML_QUERY_REWRITE materialize
      x1.cur_n
     ,x1.sql_id       
     ,x1.ADDRESS      
     ,x1.CHILD_ADDRESS
     ,x1.CHILD_NUMBER
     ,x1.X_DATA
   from 
      table(xmlsequence(xmltype(cursor(select c.* from v$sql_shared_cursor c where c.sql_id='&1')))) sq
     ,xmltable('/ROWSET/ROW'
               passing sq.column_value
               columns 
                 CUR_N         for ordinality
                ,X_DATA        xmltype      path '.'
                ,SQL_ID        varchar2(14) path 'SQL_ID'
                ,ADDRESS       varchar2(16) path 'ADDRESS'
                ,CHILD_ADDRESS varchar2(16) path 'CHILD_ADDRESS'
                ,CHILD_NUMBER  number       path 'CHILD_NUMBER'
      ) x1
)
,params as (
   select 
         t.cur_n
        ,count(decode(x_val,'Y',1)) over(partition by x_key) if_yes
        ,x2.x_n       
        ,x2.x_key
        ,x2.x_val
   from sql_shared t
       ,xmltable('//*[empty(fn:index-of(( "ROW","SQL_ID","ADDRESS","CHILD_ADDRESS","CHILD_NUMBER","REASON"),name()))]'
         passing t.x_data
         columns 
           x_n   for ordinality
          ,x_key varchar2(30)   path 'name()'
          ,x_val varchar2(1000) path '.'
        ) x2
   where x2.x_key not in ('ROW'
                         ,'SQL_ID'
                         ,'ADDRESS'
                         ,'CHILD_ADDRESS'
                         ,'CHILD_NUMBER'
                         ,'REASON')
),reasons as(
   select
        p.cur_n
       ,listagg(X_KEY||'='||x_val,', ') within group(order by x_key) reasons
   from params p
   where p.if_yes > 0
   group by p.cur_n
)
select 
   --s.cur_n,
   s.sql_id       
--  ,s.ADDRESS      
--  ,s.CHILD_ADDRESS
  ,s.child_number
  ,r.reasons            as all_reasons
   ,x.child_node_n      as reason_n
   ,x.child_node_id     as reason#
   ,x.child_node_reason as reason
   ,x2.n2               as param#
   ,x2.name
   ,x2.val
from sql_shared s
    ,reasons r
    ,v$sql_shared_cursor s2
    ,xmltable( 
               '/XMLDATA/ChildNode'
               passing xmltype('<XMLDATA>'||reason||'</XMLDATA>')
               columns 
                   child_node_n      for ordinality
                  ,child_node_Child  number        path 'ChildNumber'
                  ,child_node_id     number        path './ID'
                  ,child_node_reason varchar2(400) path 'reason'
                  ,child_node        xmltype       path '.'
    )(+)  x
   ,xmltable(
               '/ChildNode/*[not(name(.)=("ChildNumber","reason", "ID"))]'
               passing child_node
               columns 
                   n2   for ordinality
                  ,name varchar2(100) path 'name()'
                  ,val  varchar2(400) path '.'

            )(+) x2

where 
      r.cur_n (+)     = s.cur_n
  and s2.sql_id       = '&1'
  and s2.sql_id       = s.sql_id
  and s2.child_number = s.child_number
order by sql_id,child_number,all_reasons,reason_n,reason#,reason,param#
/

SQL_ID  cguqg2a507ffq, child number 0
-------------------------------------
SELECT QUEUEID AS SUMMARY_TRAVELLED_QUEUEID,count(QUEUEID) AS 
SUMMARY_TRAVELLED_COUNT FROM queue_summary_travelled  GROUP BY QUEUEID
 
Plan hash value: 3904541875
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                    |       |       | 17224 (100)|          |
|   1 |  HASH GROUP BY                |                    |    16 |   960 | 17224   (1)| 00:03:27 |
|   2 |   NESTED LOOPS                |                    |  7794 |   456K| 17224   (1)| 00:03:27 |
|   3 |    NESTED LOOPS               |                    |  8588 |   456K| 17224   (1)| 00:03:27 |
|   4 |     VIEW                      | VW_GBF_7           |  8588 |   209K|    42   (3)| 00:00:01 |
|   5 |      HASH GROUP BY            |                    |  8588 |   192K|    42   (3)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN        | QUEUEITEM_PRF1_IDX |  8588 |   192K|    41   (0)| 00:00:01 |
|*  7 |     INDEX UNIQUE SCAN         | ITINERARY_PK       |     1 |       |     1   (0)| 00:00:01 |
|*  8 |    TABLE ACCESS BY INDEX ROWID| ITINERARY          |     1 |    35 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$4F79D887
   4 - SEL$018EA6A6 / VW_GBF_7@SEL$CB688260
   5 - SEL$018EA6A6
   6 - SEL$018EA6A6 / QUEUEITEMS@SEL$3
   7 - SEL$4F79D887 / ITINERARY@SEL$5
   8 - SEL$4F79D887 / ITINERARY@SEL$5
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('_bloom_filter_enabled' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$018EA6A6")
      OUTLINE_LEAF(@"SEL$4F79D887")
      PLACE_GROUP_BY(@"SEL$186B67DE" ( "QUEUEITEMS"@"SEL$3" ) 7)
      OUTLINE(@"SEL$CB688260")
      OUTLINE(@"SEL$186B67DE")
      ELIMINATE_JOIN(@"SEL$A1A5A5EF" "QUEUES"@"SEL$4")
      OUTLINE(@"SEL$A1A5A5EF")
      MERGE(@"SEL$1AD51037")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$1AD51037")
      OUTER_JOIN_TO_INNER(@"SEL$F8F71D57" "QUEUEITEMS"@"SEL$3")
      OUTER_JOIN_TO_INNER(@"SEL$F8F71D57" "ITINERARY"@"SEL$5")
      OUTLINE(@"SEL$F8F71D57")
      MERGE(@"SEL$45781C00")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$45781C00")
      MERGE(@"SEL$9EF9DE0C")
      OUTLINE(@"SEL$7")
      OUTLINE(@"SEL$9EF9DE0C")
      MERGE(@"SEL$37633EB5")
      MERGE(@"SEL$5")
      OUTLINE(@"SEL$6")
      OUTLINE(@"SEL$37633EB5")
      MERGE(@"SEL$3")
      OUTLINE(@"SEL$5")
      OUTLINE(@"SEL$4")
      OUTLINE(@"SEL$3")
      NO_ACCESS(@"SEL$4F79D887" "VW_GBF_7"@"SEL$CB688260")
      INDEX(@"SEL$4F79D887" "ITINERARY"@"SEL$5" ("ITINERARY"."SPNRID"))
      LEADING(@"SEL$4F79D887" "VW_GBF_7"@"SEL$CB688260" "ITINERARY"@"SEL$5")
      USE_NL(@"SEL$4F79D887" "ITINERARY"@"SEL$5")
      NLJ_BATCHING(@"SEL$4F79D887" "ITINERARY"@"SEL$5")
      USE_HASH_AGGREGATION(@"SEL$4F79D887")
      INDEX(@"SEL$018EA6A6" "QUEUEITEMS"@"SEL$3" ("QUEUEITEMS"."CREATED" "QUEUEITEMS"."SPNRID" 
              "QUEUEITEMS"."QUEUEID"))
      USE_HASH_AGGREGATION(@"SEL$018EA6A6")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("QUEUEITEMS"."CREATED"<="OJ_TIMESTAMP"())
   7 - access("ITEM_1"="ITINERARY"."SPNRID")
   8 - filter(("ITINERARY"."TOTALPAID"="ITINERARY"."TOTALPRICE" AND 
              "ITINERARY"."RESERVATIONSTATUS"='booked' AND INTERNAL_FUNCTION("ITINERARY"."RESERVATIONEND")
              <"OJ_TIMESTAMP"() AND "ITINERARY"."LASTAUDITID">0))
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "ITEM_3"[VARCHAR2,20], SUM("ITEM_2")[22]
   2 - "ITEM_2"[NUMBER,22], "ITEM_3"[VARCHAR2,20]
   3 - "ITEM_2"[NUMBER,22], "ITEM_3"[VARCHAR2,20], "ITINERARY".ROWID[ROWID,10]
   4 - "ITEM_1"[VARCHAR2,40], "ITEM_2"[NUMBER,22], "ITEM_3"[VARCHAR2,20]
   5 - "QUEUEITEMS"."QUEUEID"[VARCHAR2,20], "QUEUEITEMS"."SPNRID"[VARCHAR2,40], COUNT(*)[22]
   6 - "QUEUEITEMS"."QUEUEID"[VARCHAR2,20], "QUEUEITEMS"."SPNRID"[VARCHAR2,40]
   7 - "ITINERARY".ROWID[ROWID,10]