SELECT SYSDATE AS DATE_L,
       ta.OPER_MONTH,
       ta.AGENT_ID,
       ta.GAME_ID,
       ta.DRAW_ID,
       SUM(ta.TICKET_CNT) AS TICKET_CNT,
       SUM(ta.amount) AS AMOUNT
FROM (
      SELECT TRUNC(t.pay_date, 'MM') AS OPER_MONTH,
             t.pay_gate_agent_id AS AGENT_ID,
             t.id_game AS GAME_ID,
             t.id_draw_play AS DRAW_ID,
             COUNT(DISTINCT t.id_ticket_root) AS TICKET_CNT,
             SUM(t.win_amount) AS AMOUNT
      FROM DWH.T$ALL#TICKET t
      INNER JOIN dwh_mart.t$games g ON g.id = t.id_game AND g.game_is_active = 1 AND g.game_group <> 4
      WHERE t.pay_date >= to_date ('01.07.2021') 
        AND t.pay_date < to_date ('06.07.2021') 
        AND (t.payment_flag = 0 OR t.payment_flag IS NULL)
        AND t.pay_gate_agent_id <> 2404
      GROUP BY TRUNC(t.pay_date, 'MM'),
               t.pay_gate_agent_id,
               t.id_game,
               t.id_draw_play
               
      UNION ALL

      SELECT TRUNC(arm.oper_date, 'MM') AS OPER_MONTH,
             arm.agent_id AS AGENT_ID,
             arm.id_game AS GAME_ID,
             arm.id_draw_play AS DRAW_ID,
             COUNT(DISTINCT arm.ticket_barcode) AS TICKET_CNT,
             SUM(arm.amount) AS AMOUNT
      FROM ODS_MART.T$1CB#ARM_OPER arm
      WHERE arm.oper_date >= TO_DATE('01.07.2021')
       AND arm.oper_date < to_date ('06.07.2021') 
       AND arm.reg_ticket_type IN (0,2)   --гейтовые билеты
      GROUP BY TRUNC(arm.oper_date, 'MM'),
               arm.agent_id,
               arm.id_game,
               arm.id_draw_play
      ) ta
GROUP BY ta.OPER_MONTH,
         ta.AGENT_ID,
         ta.GAME_ID,
         ta.DRAW_ID

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  cf06fwacdmgfk, child number 2
-------------------------------------
select 'x' from dual

Plan hash value: 308129442

--------------------------------------------------------------------------------
| Id  | Operation        | Name | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Ro
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |      1 |        |     2 (100)|          |
|   1 |  FAST DUAL       |      |      1 |      1 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / DUAL@SEL$1

Outline Data
-------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      END_OUTLINE_DATA
  */

Query Block Registry:
---------------------

  <q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[DUAL]]></t><s><![CDAT
        /s></h></f></q>

Peeked Binds (identified by position):
--------------------------------------

   1 - :1 (VARCHAR2(30), CSID=171): 'PLUS42111161'

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("STATEMENT_ID"=:1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   2 - (cmp=2; rowset=256) "PLAN_TABLE".ROWID[ROWID,10], "STATEMENT_ID"[VARCHAR2,30]

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

Query Block Registry:
---------------------

  <q o="2" f="y"><n><![CDATA[DEL$1]]></n><f><h><t><![CDATA[PLAN_TABLE]]></t><s><![CDATA[DEL$1]]></s></h></f></q>



59 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2381643777

----------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                |  8168 | 16336 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR |  8168 | 16336 |    29   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
         81  recursive calls
          0  db block gets
         25  consistent gets
          0  physical reads
          0  redo size
       3492  bytes sent via SQL*Net to client
        561  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
         59  rows processed