title: Performance drop returning cursor with union all
body: <p>I'm facing unsolvable and impossible performace drop while using UNION ALL with two sub-queries in one cursor (at least I think that's the problem). PL/SQL Developer just freezes when opening cursor results in test window.</p>
  <p>If I turn off no matter which sub-query - everything works fine.</p>
  <p>If I take the whole query out of cursor to regular SQL Query windows - everything is okay without any need to turn off some parts.</p>
  <p>Procedure structure is down below, looking forward any help:</p>
  <pre><code>procedure p_proc(p_param varchar2,
                   outcur  out sys_refcursor) is
  begin
    open outcur for
      select *
        from (select -- visible cols
                     si.item_full_name
                   , si.final_price
                   , si.full_price
                   , si.receipt_num
                   , si.receipt_date
                   , si.vendor_code
                   , case when det.br_summary is null and mr.motiv_rate_value is not null then mr.motiv_rate_value
                          when det.br_summary is not null then det.br_summary
                      end personal_bonus_amount
                   , case when det.br_summary is null and mr.motiv_rate_value is not null then 1
                          when det.br_summary is not null then det.cross_sale_kt
                      end personal_bonus_koeff
                   -- service cols
                   , case when det.br_summary is null and mr.motiv_rate_value is not null then 'approximate'
                          when det.br_summary is not null then 'definite'
                      end personal_bonus_type
                   , coalesce(det.sale_stream, mr.sale_stream, 'Not defined') item_group_name
                   , si.operation_type
                   , si.src
                   -- pagination
                   , row_number() over (order by si.receipt_date desc) rn 
                from (-- curr day
                      select b.cost final_price
                           , case when b.discount = 0 then null else b.price
                              end full_price
                           , b.doc_number receipt_num
                           , b.receipt_date receipt_date
                           , i.item_code vendor_code
                           , i.full_name item_full_name
                           , b.subsite code_op
                           , b.operator_id
                           , to_char(b.businessday, 'yyyymm') sale_period
                           , b.oper_type operation_type
                           , 'bill' src
                        from scheme.bills b
                        join scheme.items i on i.item_code = b.item
                       where b.businessday = trunc(p_date_to)
                         and b.subsite = p_office_id
                         and b.operator_id = p_emp_id
                      union all
                      -- prev days
                      select l.txn_amount final_price
                           , case when l.disc = 0 then null else l.price
                               end full_price
                           , t.receipt_num receipt_num
                           , t.ts receipt_date
                           , i.item_code vendor_code
                           , i.full_name item_full_name
                           , s.office_code code_op
                           , e.emp_code operator_id
                           , to_char(l.dt,'yyyymm') sale_period
                           , l.txn_type operation_type
                           , 'txn' src
                        from scheme.txn t
                        join scheme.txn_lines l on t.rtl_txn_id = l.rtl_txn_id
                        join scheme.items i on l.item_id = i.item_id
                        join scheme.offices s on t.subsite_id = s.subsite_id
                        join scheme.employees e on t.employee_id = e.employee_id
                       where t.ts between trunc(p_date_from) and trunc(p_date_to)
                         and t.subsite_id = v_op_id
                         and t.employee_id = v_emp_id
                   ) si
               /* fact */
               left join scheme.sales_details det on si.sale_period = det.period
                                                 and si.code_op = det.op_code
                                                 and ltrim(si.operator_id,'0') = ltrim(det.tab_num,'0')
                                                 and si.receipt_num = det.rcpt_num
                                                 and si.vendor_code = det.item_article
               /* prognosis */
               left join scheme.rates mr on si.sale_period = mr.motiv_rate_period
                                        and si.code_op = mr.code_op
                                        and si.vendor_code = mr.code_1c
              where 1 = 1
                and si.final_price between nvl(p_price_from, si.final_price) and nvl(p_price_to, si.final_price)
                /* if no filters */
                and (item_group_cnt = 0 or coalesce(det.sale_stream, mr.sale_stream, 'Not defined') in (select * from table(p_item_group)))
                and si.receipt_num = nvl(p_receipt_num, si.receipt_num)                                        
             )
       where rn between p_page_num * p_page_size + 1 and (p_page_num + 1) * p_page_size;
  end;
  </code></pre>
  <p><strong>UPD</strong> Explain plan for the whole query used in a cursor:</p>
  <pre><code>----------------------------------------------------------------------------------------------------------------------------------
  | Id   | Operation                                             | Name                           | Rows | Bytes | Cost | Time     |
  ----------------------------------------------------------------------------------------------------------------------------------
  |    0 | SELECT STATEMENT                                      |                                |   10 | 32810 |   62 | 00:00:01 |
  |  * 1 |   VIEW                                                |                                |   10 | 32810 |   62 | 00:00:01 |
  |  * 2 |    WINDOW SORT PUSHED RANK                            |                                |    2 |  2956 |   62 | 00:00:01 |
  |    3 |     NESTED LOOPS OUTER                                |                                |    2 |  2956 |   61 | 00:00:01 |
  |    4 |      NESTED LOOPS OUTER                               |                                |    2 |  2826 |   53 | 00:00:01 |
  |    5 |       VIEW                                            |                                |    2 |  2728 |   46 | 00:00:01 |
  |    6 |        UNION-ALL                                      |                                |      |       |      |          |
  |    7 |         NESTED LOOPS                                  |                                |    1 |   138 |   32 | 00:00:01 |
  |    8 |          NESTED LOOPS                                 |                                |    1 |   138 |   32 | 00:00:01 |
  |    9 |           PARTITION RANGE SINGLE                      |                                |    1 |    66 |   29 | 00:00:01 |
  | * 10 |            TABLE ACCESS BY LOCAL INDEX ROWID BATCHED  | F003_BILL                      |    1 |    66 |   29 | 00:00:01 |
  | * 11 |             INDEX RANGE SCAN                          | IX_SUBSITE_DOCNUM_BUSINDAY_SEQ |    1 |       |    5 | 00:00:01 |
  | * 12 |           INDEX RANGE SCAN                            | IX_D001_CODE_1C_ITEM_ID        |    1 |       |    2 | 00:00:01 |
  |   13 |          TABLE ACCESS BY INDEX ROWID                  | D001_ITEM                      |    1 |    72 |    3 | 00:00:01 |
  |   14 |         NESTED LOOPS                                  |                                |    1 |   183 |   14 | 00:00:01 |
  |   15 |          NESTED LOOPS                                 |                                |    1 |   183 |   14 | 00:00:01 |
  |   16 |           NESTED LOOPS                                |                                |    1 |   104 |   12 | 00:00:01 |
  |   17 |            NESTED LOOPS                               |                                |    1 |    70 |    7 | 00:00:01 |
  |   18 |             NESTED LOOPS                              |                                |    1 |    30 |    4 | 00:00:01 |
  |   19 |              TABLE ACCESS BY INDEX ROWID              | D005_EMPLOYEE                  |    1 |    18 |    3 | 00:00:01 |
  | * 20 |               INDEX UNIQUE SCAN                       | PK_D005                        |    1 |       |    2 | 00:00:01 |
  |   21 |              TABLE ACCESS BY INDEX ROWID              | D018_SUBSITE                   |    1 |    12 |    1 | 00:00:01 |
  | * 22 |               INDEX UNIQUE SCAN                       | PK_D018                        |    1 |       |    0 | 00:00:01 |
  |   23 |             PARTITION RANGE ITERATOR                  |                                |    1 |    40 |    3 | 00:00:01 |
  |   24 |              PARTITION HASH SINGLE                    |                                |    1 |    40 |    3 | 00:00:01 |
  | * 25 |               TABLE ACCESS FULL                       | F007_RTL_TXN                   |    1 |    40 |    3 | 00:00:01 |
  | * 26 |            TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | F008_RTL_TXN_LI                |    1 |    34 |    5 | 00:00:01 |
  | * 27 |             INDEX RANGE SCAN                          | IX_F008_RTL_TXN_ID             |    7 |       |    3 | 00:00:01 |
  | * 28 |           INDEX UNIQUE SCAN                           | PK_D001                        |    1 |       |    1 | 00:00:01 |
  |   29 |          TABLE ACCESS BY INDEX ROWID                  | D001_ITEM                      |    1 |    79 |    2 | 00:00:01 |
  | * 30 |       TABLE ACCESS BY INDEX ROWID BATCHED             | T_OP_MOTIVATION_RATE_MYRTK     |    1 |    49 |    7 | 00:00:01 |
  | * 31 |        INDEX RANGE SCAN                               | IDX02_CODE_OP_1C               |    3 |       |    3 | 00:00:01 |
  | * 32 |      TABLE ACCESS BY INDEX ROWID BATCHED              | DET_SALES_PPT_DWH              |    1 |    65 |    4 | 00:00:01 |
  | * 33 |       INDEX RANGE SCAN                                | IDX_03_RCPT_NUM                |    3 |       |    2 | 00:00:01 |
  ----------------------------------------------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ------------------------------------------
  * 1 - filter(&quot;RN&quot;&gt;=1 AND &quot;RN&quot;&lt;=10)
  * 2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION(&quot;SI&quot;.&quot;RECEIPT_DATE&quot;) DESC )&lt;=10)
  * 10 - filter(&quot;F003&quot;.&quot;OPERATOR_ID&quot;='000189513' AND &quot;F003&quot;.&quot;COST&quot;&gt;=TO_NUMBER(TO_CHAR(&quot;F003&quot;.&quot;COST&quot;)) AND &quot;F003&quot;.&quot;COST&quot;&lt;=TO_NUMBER(TO_CHAR(&quot;F003&quot;.&quot;COST&quot;)))
  * 11 - access(&quot;F003&quot;.&quot;SUBSITE&quot;='S165' AND &quot;F003&quot;.&quot;BUSINESSDAY&quot;=TO_DATE(' 2021-11-23 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  * 11 - filter(&quot;F003&quot;.&quot;BUSINESSDAY&quot;=TO_DATE(' 2021-11-23 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND &quot;F003&quot;.&quot;DOC_NUMBER&quot; IS NOT NULL)
  * 12 - access(&quot;I&quot;.&quot;D001_CODE_1C&quot;=&quot;F003&quot;.&quot;ITEM&quot;)
  * 12 - filter(&quot;I&quot;.&quot;D001_CODE_1C&quot; IS NOT NULL)
  * 20 - access(&quot;E&quot;.&quot;EMPLOYEE_ID&quot;=3561503543)
  * 22 - access(&quot;S&quot;.&quot;SUBSITE_ID&quot;=29260)
  * 25 - filter(&quot;T&quot;.&quot;EMPLOYEE_ID&quot;=3561503543 AND &quot;T&quot;.&quot;SUBSITE_ID&quot;=29260 AND &quot;T&quot;.&quot;F007_TS&quot;&lt;=TO_DATE(' 2021-11-23 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND &quot;T&quot;.&quot;F007_RCPT_NUM_1C&quot; IS NOT NULL)
  * 26 - filter(&quot;L&quot;.&quot;F008_AMOUNT&quot;&gt;=TO_NUMBER(TO_CHAR(&quot;L&quot;.&quot;F008_AMOUNT&quot;)) AND &quot;L&quot;.&quot;F008_AMOUNT&quot;&lt;=TO_NUMBER(TO_CHAR(&quot;L&quot;.&quot;F008_AMOUNT&quot;)))
  * 27 - access(&quot;T&quot;.&quot;RTL_TXN_ID&quot;=&quot;L&quot;.&quot;RTL_TXN_ID&quot;)
  * 28 - access(&quot;L&quot;.&quot;ITEM_ID&quot;=&quot;I&quot;.&quot;ITEM_ID&quot;)
  * 30 - filter(&quot;SI&quot;.&quot;SALE_PERIOD&quot;=&quot;MR&quot;.&quot;MOTIV_RATE_PERIOD&quot;(+))
  * 31 - access(&quot;SI&quot;.&quot;CODE_OP&quot;=&quot;MR&quot;.&quot;CODE_OP&quot;(+) AND &quot;SI&quot;.&quot;VENDOR_CODE&quot;=&quot;MR&quot;.&quot;CODE_1C&quot;(+))
  * 32 - filter(&quot;SI&quot;.&quot;CODE_OP&quot;=&quot;DET&quot;.&quot;OP_CODE&quot;(+) AND &quot;SI&quot;.&quot;VENDOR_CODE&quot;=&quot;DET&quot;.&quot;ITEM_ARTICLE&quot;(+) AND &quot;DET&quot;.&quot;ITEM_ARTICLE&quot;(+) IS NOT NULL AND &quot;DET&quot;.&quot;PERIOD&quot;(+)=TO_NUMBER(&quot;SI&quot;.&quot;SALE_PERIOD&quot;) AND
    LTRIM(&quot;SI&quot;.&quot;OPERATOR_ID&quot;,'0')=LTRIM(&quot;DET&quot;.&quot;TAB_NUM_RTK&quot;(+),'0'))
  * 33 - access(&quot;SI&quot;.&quot;RECEIPT_NUM&quot;=&quot;DET&quot;.&quot;RCPT_NUM&quot;(+))
  * 33 - filter(&quot;DET&quot;.&quot;RCPT_NUM&quot;(+) IS NOT NULL)
  </code></pre>