11 Replies Latest reply: Feb 3, 2013 5:32 AM by William Robertson RSS

    SQL using large amounts of temp space.

    user3206995
      Hallo,

      I have a SQL with a structure similar to :

      mege into sale x using
      select x.hist,x.date,x.name ...etc, x.*
      from
      a subquery


      The third party company is responisble for the query, I can post a part of it if required.

      My question is why name the columns then follow it with a ,x.*?

      Sorry for being vauge but the question is more why would someone choose to use x.*


      Thank you
      user3206995
        • 1. Re: SQL using large amounts of temp space.
          Bawer
          user3206995 wrote:
          Hallo,

          I have a SQL with a structure similar to :

          mege into sale x using
          select x.hist,x.date,x.name ...etc, x.*
          from
          a subquery
          Query isn't clear, please post the original code with all definitions of x and a (package/type/table).
          • 2. Re: SQL using large amounts of temp space.
            user3206995
            here is the original query:


            MERGE INTO DWH_TABLE_B t
            USING
            (Select
            ORA_HASH(TABLE_B_DELETED||'|'||TABLE_B_SRCSYS||'|'||TABLE_B_SRCID||'|'||TABLE_B_GAME||'|'||TABLE_B_CANTON||'|'||TABLE_B_POS||'|'||TABLE_B_CUS_SRCID||'|'||TABLE_B_BUY_DATE||'|'||TABLE_B_STATE||'|'||TABLE_B_UNITS_TOTAL||'|'||TABLE_B_UNITS_FREE||'|'||TABLE_B_UNITS_PAID||'|'||TABLE_B_UNIT_PRICE||'|'||TABLE_B_CALC_AMOUNT||'|'||TABLE_B_CALC_PAID||'|'||TABLE_B_HEADER_AMOUNT||'|'||TABLE_B_FEE||'|'||TABLE_B_WAGER_NUM||'|'||TABLE_B_TICKET_SRCID||'|'||TABLE_B_SALESCHANNEL||'|'||TABLE_B_FIRSTDRAW||'|'||TABLE_B_LASTDRAW||'|'||TABLE_B_DRAWCOUNT||'|'||TABLE_B_CONFIRM_DATE)
            AS TABLE_B_CHECKSUM,
            x.*
            FROM
            (SELECT
            GREATEST(NVL(ASM_GIP_TOP_HISTORY.BATCH,-1),
            NVL(ASM_GIP_TOP.BATCH,-1),
            NVL(ASM_GIP_TOP_PACKAGES.BATCH,-1),
            NVL(ASM_GIP_TOP_SERIES.BATCH,-1),
            NVL(ASM_GIP_TOPSTORIES.BATCH,-1)) AS TABLE_B_BATCH,
            0 AS TABLE_B_DELETED,
            'IGP' AS TABLE_B_SRCSYS,
            LOH_ID AS TABLE_B_SRCID,
            LTY_ID AS TABLE_B_GAME,
            LOH_PLAYER_CANTON AS TABLE_B_CANTON,
            to_number(LOH_POS_REFERENCE_ID) AS TABLE_B_POS,
            LOH_PLAYER_ID AS TABLE_B_CUS_SRCID,
            LOH_PURCHASE_DATE AS TABLE_B_BUY_DATE,
            DECODE(LOH_PROCESSING_STATE,3,1,4,1,5,1,6,1,2) AS TABLE_B_STATE,
            1 AS TABLE_B_UNITS_TOTAL,
            1 AS TABLE_B_UNITS_FREE,
            1 AS TABLE_B_UNITS_PAID,
            LTY_PRICE_PER_LOT AS TABLE_B_UNIT_PRICE,
            LTY_PRICE_PER_LOT AS TABLE_B_CALC_AMOUNT,
            LTY_PRICE_PER_LOT AS TABLE_B_CALC_PAID,
            LOH_PURCHASE_AMOUNT AS TABLE_B_HEADER_AMOUNT,
            CLW_FEE AS TABLE_B_FEE,
            CLW_ID AS TABLE_B_WAGER_NUM,
            CLE_ID AS TABLE_B_TICKET_SRCID,
            NULL AS TABLE_B_SALESCHANNEL,
            CLE_FIRST_PARTCP AS TABLE_B_FIRSTDRAW,
            CLE_FIRST_PARTCP AS TABLE_B_LASTDRAW,
            NVL(CLW_PARTICIPATIONS,0) AS TABLE_B_DRAWCOUNT,
            LOH_COMMIT_DATE AS TABLE_B_CONFIRM_DATE,
            rank() over (partition by LOH_ID order by NVL(ASM_GIP_TOP_HISTORY.BATCH,-1) desc,
            NVL(ASM_GIP_TOP.BATCH,-1) desc,
            NVL(ASM_GIP_TOP_PACKAGES.BATCH,-1) desc,
            NVL(ASM_GIP_TOP_SERIES.BATCH,-1) desc,
            NVL(ASM_GIP_TOPSTORIES.BATCH,-1) desc,
            NVL(ASM_GIP_PIPT_DATA.BATCH,-1) desc,
            NVL(ASM_GIP_COMPUNDLW.BATCH,-1) desc,
            NVL(ASM_GIP_COMPUNDRECEIPT.BATCH,-1) desc ) as rnk
            FROM  ASM_GIP_TOP_HISTORY
            LEFT OUTER JOIN ASM_GIP_TOP ON LOH_LOT_ID = LOT_ID
            LEFT OUTER JOIN ASM_GIP_TOP_PACKAGES ON LOT_LOP_ID = LOP_ID
            LEFT OUTER JOIN ASM_GIP_TOP_SERIES ON LOP_LSE_ID = LSE_ID
            LEFT OUTER JOIN ASM_GIP_TOPSTORIES ON LSE_LTY_ID = LTY_ID
            LEFT OUTER JOIN ASM_GIP_PIPT_DATA ON TIP_LOH_ID = LOH_ID
            LEFT OUTER JOIN ASM_GIP_COMPUNDLW ON TIP_CLW_ID = CLW_ID
            LEFT OUTER JOIN ASM_GIP_COMPUNDRECEIPT ON TIP_CLW_ID = CLE_ID
            WHERE LOH_TYPE = 50
            AND (NVL(ASM_GIP_TOP_HISTORY.BATCH,-1)  IN (SELECT CTL_BATCH_ID FROM  CTL_BATCH_MASTER WHERE  CTL_BATCH_STATUS = 0)
            OR NVL(ASM_GIP_TOP.BATCH,-1) IN (SELECT CTL_BATCH_ID FROM  CTL_BATCH_MASTER WHERE  CTL_BATCH_STATUS = 0))) x WHERE rnk = 1) s
            ON (s.TABLE_B_SRCID = t.TABLE_B_SRCID
            AND s.TABLE_B_GAME = t.TABLE_B_GAME
            AND t.TABLE_B_SRCSYS  = s.TABLE_B_SRCSYS)
            WHEN MATCHED THEN
              UPDATE SET
            t.TABLE_B_BATCH = s.TABLE_B_BATCH,
            t.TABLE_B_CHECKSUM = s.TABLE_B_CHECKSUM,
            t.TABLE_B_DELETED = s.TABLE_B_DELETED,
            t.TABLE_B_CANTON = s.TABLE_B_CANTON,
            t.TABLE_B_POS = s.TABLE_B_POS,
            t.TABLE_B_CUS_SRCID = s.TABLE_B_CUS_SRCID,
            t.TABLE_B_BUY_DATE = s.TABLE_B_BUY_DATE,
            t.TABLE_B_STATE = s.TABLE_B_STATE,
            t.TABLE_B_UNITS_TOTAL = s.TABLE_B_UNITS_TOTAL,
            t.TABLE_B_UNITS_FREE = s.TABLE_B_UNITS_FREE,
            t.TABLE_B_UNITS_PAID = s.TABLE_B_UNITS_PAID,
            t.TABLE_B_UNIT_PRICE = s.TABLE_B_UNIT_PRICE,
            t.TABLE_B_CALC_AMOUNT = s.TABLE_B_CALC_AMOUNT,
            t.TABLE_B_CALC_PAID = s.TABLE_B_CALC_PAID,
            t.TABLE_B_FEE = s.TABLE_B_FEE,
            t.TABLE_B_WAGER_NUM = s.TABLE_B_WAGER_NUM,
            t.TABLE_B_TICKET_SRCID = s.TABLE_B_TICKET_SRCID,
            t.TABLE_B_SALESCHANNEL = s.TABLE_B_SALESCHANNEL,
            t.TABLE_B_FIRSTDRAW = s.TABLE_B_FIRSTDRAW,
            t.TABLE_B_LASTDRAW  = s.TABLE_B_LASTDRAW,
            t.TABLE_B_DRAWCOUNT = s.TABLE_B_DRAWCOUNT,
            t.TABLE_B_CONFIRM_DATE = s.TABLE_B_CONFIRM_DATE,
            t.TABLE_B_HEADER_AMOUNT = s.TABLE_B_HEADER_AMOUNT
            WHERE t.TABLE_B_CHECKSUM != s.TABLE_B_CHECKSUM     
            WHEN NOT MATCHED THEN
            INSERT
            (
            t.TABLE_B_BATCH,
            t.TABLE_B_CHECKSUM,
            t.TABLE_B_DELETED,
            t.TABLE_B_SRCSYS,
            t.TABLE_B_ID,
            t.TABLE_B_SRCID,
            t.TABLE_B_GAME,
            t.TABLE_B_CANTON,
            t.TABLE_B_POS,
            t.TABLE_B_CUS_SRCID,
            t.TABLE_B_BUY_DATE,
            t.TABLE_B_STATE,
            t.TABLE_B_UNITS_TOTAL,
            t.TABLE_B_UNITS_FREE,
            t.TABLE_B_UNITS_PAID,
            t.TABLE_B_UNIT_PRICE,
            t.TABLE_B_CALC_AMOUNT,
            t.TABLE_B_CALC_PAID,
            t.TABLE_B_FEE,
            t.TABLE_B_WAGER_NUM,
            t.TABLE_B_TICKET_SRCID,
            t.TABLE_B_SALESCHANNEL,
            t.TABLE_B_FIRSTDRAW,
            t.TABLE_B_LASTDRAW,
            t.TABLE_B_DRAWCOUNT,
            t.TABLE_B_CONFIRM_DATE,
            t.TABLE_B_HEADER_AMOUNT
            ) values (
            s.TABLE_B_BATCH,
            s.TABLE_B_CHECKSUM,
            s.TABLE_B_DELETED,
            s.TABLE_B_SRCSYS,
            SEQ_DWH_TABLE_B_ID.nextval,
            s.TABLE_B_SRCID,
            s.TABLE_B_GAME,
            s.TABLE_B_CANTON,
            s.TABLE_B_POS,
            s.TABLE_B_CUS_SRCID,
            s.TABLE_B_BUY_DATE,
            s.TABLE_B_STATE,
            s.TABLE_B_UNITS_TOTAL,
            s.TABLE_B_UNITS_FREE,
            s.TABLE_B_UNITS_PAID,
            s.TABLE_B_UNIT_PRICE,
            s.TABLE_B_CALC_AMOUNT,
            s.TABLE_B_CALC_PAID,
            s.TABLE_B_FEE,
            s.TABLE_B_WAGER_NUM,
            s.TABLE_B_TICKET_SRCID,
            s.TABLE_B_SALESCHANNEL,
            s.TABLE_B_FIRSTDRAW,
            s.TABLE_B_LASTDRAW,
            s.TABLE_B_DRAWCOUNT,
            s.TABLE_B_CONFIRM_DATE,
            s.TABLE_B_HEADER_AMOUNT
            );
            • 3. Re: SQL using large amounts of temp space.
              Bawer
              user3206995 wrote:
              Sorry for being vauge but the question is more why would someone choose to use x.*
              if you don't want to write all column names in select, you can use only select * ...
              BUT
              if you want to use an expression/function/column alias you should write
              select substr(...), colname1, colname2 as cc2, ...
              If you must to select all columns, you can use aliases as follows
              select substr(a.col,...), a.* from table_name a
              because of you can't use the asterix with an column name specified (example: select colname1 as cn1, * from table_name)
              • 4. Re: SQL using large amounts of temp space.
                user3206995
                OK, so that bit of the coding can be considered a normal approach.
                It just seems that it does not matter how much temp space I use,
                the query always ends with the following error ORA-1652: unable to extend temp segment by 128 in tablespace

                in our production environment the same query used about 30GB.

                Thanks for
                • 5. Re: SQL using large amounts of temp space.
                  Bawer
                  user3206995 wrote:
                  ORA-1652: unable to extend temp segment by 128 in tablespace
                  change the logic so that you can process data partly, and then commit.
                  or
                  add new data file to temp tablespace -as sysdba-
                  ALTER TABLESPACE TEMP ADD TEMPFILE
                  'C:\Oracle\oradata\ORCL\Temp02.dbf' size 50M reuse autoextend on next 1M maxsize 32767M;
                  • 6. Re: SQL using large amounts of temp space.
                    user3206995
                    I have tried extending the temp file from 30GB it is now about 120GB and still I get the ORA-1652: unable to extend temp segment by 128 in tablespace.
                    I can either continue to extend and hope for the best or just let third party company rewrite the query.

                    My problem is I will need to be sure that there is a problem with the query.

                    i have checked the table sizes statistics etc, I have even copied the stats from prod into the test environment and still no resolution.
                    The SQL-PLAN is very similar to the production.

                    I will post both shortly.


                    Any idea will be useful

                    Thanks
                    • 7. Re: SQL using large amounts of temp space.
                      user3206995
                      this is the plan out from our production server.
                      Plan hash value: 2193995552

                      ------------------------------------------------------------------------------------------------------------------------------
                      | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
                      ------------------------------------------------------------------------------------------------------------------------------
                      | 0 | MERGE STATEMENT | | | | | 2567K(100)| |
                      | 1 | MERGE | DWH_TABLE_B | | | | | |
                      | 2 | VIEW | | | | | | |
                      | 3 | SEQUENCE | SEQ_DWH_TABLE_B_SALE_ID | | | | | |
                      | 4 | HASH JOIN RIGHT OUTER | | 55M| 21G| 1556M| 2567K (2)| 03:41:59 |
                      | 5 | TABLE ACCESS FULL | DWH_TABLE_B | 12M| 1412M| | 22615 (6)| 00:01:58 |
                      | 6 | VIEW | | 55M| 15G| | 2233K (2)| 03:13:09 |
                      | 7 | WINDOW SORT PUSHED RANK | | 55M| 8520M| | 2233K (2)| 03:13:09 |
                      | 8 | CONCATENATION | | | | | | |
                      | 9 | FILTER | | | | | | |
                      | 10 | HASH JOIN RIGHT OUTER | | 27M| 4260M| | 696K (3)| 01:00:12 |
                      | 11 | TABLE ACCESS FULL | ASM_GIP_TOPSTORIES | 35 | 420 | | 2 (0)| 00:00:01 |
                      | 12 | HASH JOIN RIGHT OUTER | | 27M| 3940M| | 695K (3)| 01:00:10 |
                      | 13 | TABLE ACCESS FULL | ASM_GIP_TOP_SERIES | 76 | 988 | | 2 (0)| 00:00:01 |
                      | 14 | HASH JOIN RIGHT OUTER | | 27M| 3594M| 78M| 695K (2)| 01:00:08 |
                      | 15 | TABLE ACCESS FULL | ASM_GIP_COMPUNDRECEIPT | 2942K| 44M| | 10444 (3)| 00:00:55 |
                      | 16 | HASH JOIN RIGHT OUTER | | 18M| 2154M| 76M| 641K (2)| 00:55:31 |
                      | 17 | TABLE ACCESS FULL | ASM_GIP_COMPUNDLW | 2879K| 43M| | 11809 (3)| 00:01:02 |
                      | 18 | HASH JOIN RIGHT OUTER | | 12M| 1275M| 279M| 603K (2)| 00:52:12 |
                      | 19 | TABLE ACCESS FULL | ASM_GIP_PIPT_DATA | 10M| 163M| | 8921 (5)| 00:00:47 |
                      | 20 | HASH JOIN RIGHT OUTER | | 11M| 948M| 8008K| 570K (2)| 00:49:21 |
                      | 21 | TABLE ACCESS FULL | ASM_GIP_TOP_PACKAGES | 315K| 4310K| | 120 (9)| 00:00:01 |
                      | 22 | HASH JOIN OUTER | | 11M| 794M| 613M| 553K (2)| 00:47:54 |
                      | 23 | TABLE ACCESS FULL | ASM_GIP_TOP_HISTORY | 9457K| 505M| | 431K (2)| 00:37:19 |
                      | 24 | TABLE ACCESS FULL | ASM_GIP_TOP | 26M| 409M| | 98475 (2)| 00:08:31 |
                      | 25 | TABLE ACCESS BY INDEX ROWID| CTL_BATCH_MASTER | 1 | 9 | | 2 (0)| 00:00:01 |
                      | 26 | INDEX UNIQUE SCAN | PK_BATCH_MASTER | 1 | | | 1 (0)| 00:00:01 |
                      | 27 | FILTER | | | | | | |
                      | 28 | HASH JOIN RIGHT OUTER | | 27M| 4260M| | 696K (3)| 01:00:13 |
                      | 29 | TABLE ACCESS FULL | ASM_GIP_TOPSTORIES | 35 | 420 | | 2 (0)| 00:00:01 |
                      | 30 | HASH JOIN RIGHT OUTER | | 27M| 3940M| | 695K (3)| 01:00:11 |
                      | 31 | TABLE ACCESS FULL | ASM_GIP_TOP_SERIES | 76 | 988 | | 2 (0)| 00:00:01 |
                      | 32 | HASH JOIN RIGHT OUTER | | 27M| 3594M| 78M| 695K (2)| 01:00:09 |
                      | 33 | TABLE ACCESS FULL | ASM_GIP_COMPUNDRECEIPT | 2942K| 44M| | 10444 (3)| 00:00:55 |
                      | 34 | HASH JOIN RIGHT OUTER | | 18M| 2154M| 76M| 641K (2)| 00:55:31 |
                      | 35 | TABLE ACCESS FULL | ASM_GIP_COMPUNDLW | 2879K| 43M| | 11809 (3)| 00:01:02 |
                      | 36 | HASH JOIN RIGHT OUTER | | 12M| 1275M| 279M| 603K (2)| 00:52:12 |
                      | 37 | TABLE ACCESS FULL | ASM_GIP_PIPT_DATA | 10M| 163M| | 8921 (5)| 00:00:47 |
                      | 38 | HASH JOIN RIGHT OUTER | | 11M| 948M| 8008K| 570K (2)| 00:49:21 |
                      | 39 | TABLE ACCESS FULL | ASM_GIP_TOP_PACKAGES | 315K| 4310K| | 120 (9)| 00:00:01 |
                      | 40 | HASH JOIN OUTER | | 11M| 794M| 613M| 553K (2)| 00:47:55 |
                      | 41 | TABLE ACCESS FULL | ASM_GIP_TOP_HISTORY | 9457K| 505M| | 431K (2)| 00:37:20 |
                      | 42 | TABLE ACCESS FULL | ASM_GIP_TOP | 26M| 409M| | 98475 (2)| 00:08:31 |
                      | 43 | TABLE ACCESS BY INDEX ROWID| CTL_BATCH_MASTER | 1 | 9 | | 2 (0)| 00:00:01 |
                      | 44 | INDEX UNIQUE SCAN | PK_BATCH_MASTER | 1 | | | 1 (0)| 00:00:01 |
                      | 45 | TABLE ACCESS BY INDEX ROWID| CTL_BATCH_MASTER | 1 | 9 | | 2 (0)| 00:00:01 |
                      | 46 | INDEX UNIQUE SCAN | PK_BATCH_MASTER | 1 | | | 1 (0)| 00:00:01 |
                      ------------------------------------------------------------------------------------------------------------------------------

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

                      1 - MRG$1
                      3 - SEL$5428C7F1
                      5 - SEL$5428C7F1 / T@SEL$2
                      6 - SEL$4FD011D6 / X@SEL$3
                      7 - SEL$4FD011D6
                      11 - SEL$4FD011D6_1 / ASM_GIP_TOPSTORIES@SEL$10
                      13 - SEL$4FD011D6_1 / ASM_GIP_TOP_SERIES@SEL$8
                      15 - SEL$4FD011D6_1 / ASM_GIP_COMPUNDRECEIPT@SEL$16
                      17 - SEL$4FD011D6_1 / ASM_GIP_COMPUNDLW@SEL$14
                      19 - SEL$4FD011D6_1 / ASM_GIP_PIPT_DATA@SEL$12
                      21 - SEL$4FD011D6_1 / ASM_GIP_TOP_PACKAGES@SEL$6
                      23 - SEL$4FD011D6_1 / ASM_GIP_TOPSTORIES@SEL$5
                      24 - SEL$4FD011D6_1 / ASM_GIP_TOP@SEL$4
                      25 - SEL$20 / CTL_BATCH_MASTER@SEL$20
                      26 - SEL$20 / CTL_BATCH_MASTER@SEL$20
                      29 - SEL$4FD011D6_2 / ASM_GIP_TOPSTORIES@SEL$4FD011D6_2
                      31 - SEL$4FD011D6_2 / ASM_GIP_TOP_SERIES@SEL$4FD011D6_2
                      33 - SEL$4FD011D6_2 / ASM_GIP_COMPUNDRECEIPT@SEL$4FD011D6_2
                      35 - SEL$4FD011D6_2 / ASM_GIP_COMPUNDLW@SEL$4FD011D6_2
                      37 - SEL$4FD011D6_2 / ASM_GIP_PIPT_DATA@SEL$4FD011D6_2
                      39 - SEL$4FD011D6_2 / ASM_GIP_TOP_PACKAGES@SEL$4FD011D6_2
                      41 - SEL$4FD011D6_2 / ASM_GIP_TOP_HISTORY@SEL$4FD011D6_2
                      42 - SEL$4FD011D6_2 / ASM_GIP_TOP@SEL$4FD011D6_2
                      43 - SEL$20 / CTL_BATCH_MASTER@SEL$20
                      44 - SEL$20 / CTL_BATCH_MASTER@SEL$20
                      45 - SEL$19 / CTL_BATCH_MASTER@SEL$19
                      46 - SEL$19 / CTL_BATCH_MASTER@SEL$19
                      • 8. Re: SQL using large amounts of temp space.
                        user3206995
                        TEST server :

                        from our test::::::::::::::


                        Plan hash value: 4119998805

                        ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                        | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
                        ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                        | 0 | MERGE STATEMENT | | 75M| 30G| | 700K (1)| 02:20:08 | | | |
                        | 1 | MERGE | DWH_TABLE_B | | | | | | | | |
                        | 2 | VIEW | | | | | | | | | |
                        | 3 | SEQUENCE | SEQ_DWH_TABLE_B_SALE_ID | | | | | | | | |
                        | 4 | PX COORDINATOR | | | | | | | | | |
                        | 5 | PX SEND QC (RANDOM) | :TQ30002 | 75M| 29G| | 700K (1)| 02:20:08 | Q3,02 | P->S | QC (RAND) |
                        |* 6 | HASH JOIN RIGHT OUTER | | 75M| 29G| 194M| 700K (1)| 02:20:08 | Q3,02 | PCWP | |
                        | 7 | PX RECEIVE | | 12M| 1412M| | 6927 (4)| 00:01:24 | Q3,02 | PCWP | |
                        | 8 | PX SEND HASH | :TQ30001 | 12M| 1412M| | 6927 (4)| 00:01:24 | Q3,01 | P->P | HASH |
                        | 9 | PX BLOCK ITERATOR | | 12M| 1412M| | 6927 (4)| 00:01:24 | Q3,01 | PCWC | |
                        | 10 | TABLE ACCESS FULL | DWH_TABLE_B | 12M| 1412M| | 6927 (4)| 00:01:24 | Q3,01 | PCWP | |
                        | 11 | BUFFER SORT | | | | | | | Q3,02 | PCWC | |
                        | 12 | PX RECEIVE | | 75M| 21G| | 544K (1)| 01:48:49 | Q3,02 | PCWP | |
                        | 13 | PX SEND HASH | :TQ30000 | 75M| 21G| | 544K (1)| 01:48:49 | | S->P | HASH |
                        |* 14 | VIEW | | 75M| 21G| | 544K (1)| 01:48:49 | | | |
                        |* 15 | WINDOW SORT PUSHED RANK | | 75M| 11G| | 544K (1)| 01:48:49 | | | |
                        | 16 | CONCATENATION | | | | | | | | | |
                        |* 17 | FILTER | | | | | | | | | |
                        | 18 | PX COORDINATOR | | | | | | | | | |
                        | 19 | PX SEND QC (RANDOM) | :TQ10010 | 37M| 5767M| | 179K (1)| 00:35:49 | Q1,10 | P->S | QC (RAND) |
                        |* 20 | HASH JOIN RIGHT OUTER BUFFERED | | 37M| 5767M| | 179K (1)| 00:35:49 | Q1,10 | PCWP | |
                        | 21 | PX RECEIVE | | 2854K| 43M| | 3193 (1)| 00:00:39 | Q1,10 | PCWP | |
                        | 22 | PX SEND HASH | :TQ10007 | 2854K| 43M| | 3193 (1)| 00:00:39 | Q1,07 | P->P | HASH |
                        | 23 | PX BLOCK ITERATOR | | 2854K| 43M| | 3193 (1)| 00:00:39 | Q1,07 | PCWC | |
                        | 24 | TABLE ACCESS FULL | ASM_GIP_COMPUNDRECEIPT | 2854K| 43M| | 3193 (1)| 00:00:39 | Q1,07 | PCWP | |
                        |* 25 | HASH JOIN RIGHT OUTER | | 22M| 3024M| | 175K (1)| 00:35:10 | Q1,10 | PCWP | |
                        | 26 | PX RECEIVE | | 2807K| 42M| | 3573 (2)| 00:00:43 | Q1,10 | PCWP | |
                        | 27 | PX SEND HASH | :TQ10008 | 2807K| 42M| | 3573 (2)| 00:00:43 | Q1,08 | P->P | HASH |
                        | 28 | PX BLOCK ITERATOR | | 2807K| 42M| | 3573 (2)| 00:00:43 | Q1,08 | PCWC | |
                        | 29 | TABLE ACCESS FULL | ASM_GIP_COMPUNDLW | 2807K| 42M| | 3573 (2)| 00:00:43 | Q1,08 | PCWP | |
                        | 30 | PX RECEIVE | | 13M| 1592M| | 172K (1)| 00:34:26 | Q1,10 | PCWP | |
                        | 31 | PX SEND HASH | :TQ10009 | 13M| 1592M| | 172K (1)| 00:34:26 | Q1,09 | P->P | HASH |
                        |* 32 | HASH JOIN RIGHT OUTER BUFFERED | | 13M| 1592M| | 172K (1)| 00:34:26 | Q1,09 | PCWP | |
                        | 33 | PX RECEIVE | | 9685K| 157M| | 2635 (3)| 00:00:32 | Q1,09 | PCWP | |
                        | 34 | PX SEND HASH | :TQ10005 | 9685K| 157M| | 2635 (3)| 00:00:32 | Q1,05 | P->P | HASH |
                        | 35 | PX BLOCK ITERATOR | | 9685K| 157M| | 2635 (3)| 00:00:32 | Q1,05 | PCWC | |
                        | 36 | TABLE ACCESS FULL | ASM_GIP_PIPT_DATA | 9685K| 157M| | 2635 (3)| 00:00:32 | Q1,05 | PCWP | |
                        | 37 | PX RECEIVE | | 11M| 1265M| | 169K (1)| 00:33:54 | Q1,09 | PCWP | |
                        | 38 | PX SEND HASH | :TQ10006 | 11M| 1265M| | 169K (1)| 00:33:54 | Q1,06 | P->P | HASH |
                        |* 39 | HASH JOIN RIGHT OUTER BUFFERED| | 11M| 1265M| | 169K (1)| 00:33:54 | Q1,06 | PCWP | |
                        | 40 | PX RECEIVE | | 35 | 420 | | 2 (0)| 00:00:01 | Q1,06 | PCWP | |
                        | 41 | PX SEND BROADCAST | :TQ10000 | 35 | 420 | | 2 (0)| 00:00:01 | Q1,00 | P->P | BROADCAST |
                        | 42 | PX BLOCK ITERATOR | | 35 | 420 | | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
                        | 43 | TABLE ACCESS FULL | ASM_GIP_TOPSTORIES | 35 | 420 | | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
                        |* 44 | HASH JOIN RIGHT OUTER | | 11M| 1128M| | 169K (1)| 00:33:53 | Q1,06 | PCWP | |
                        | 45 | PX RECEIVE | | 78 | 1014 | | 2 (0)| 00:00:01 | Q1,06 | PCWP | |
                        | 46 | PX SEND BROADCAST | :TQ10001 | 78 | 1014 | | 2 (0)| 00:00:01 | Q1,01 | P->P | BROADCAST |
                        | 47 | PX BLOCK ITERATOR | | 78 | 1014 | | 2 (0)| 00:00:01 | Q1,01 | PCWC | |
                        | 48 | TABLE ACCESS FULL | ASM_GIP_TOP_SERIES | 78 | 1014 | | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
                        |* 49 | HASH JOIN RIGHT OUTER | | 11M| 980M| | 169K (1)| 00:33:53 | Q1,06 | PCWP | |
                        | 50 | PX RECEIVE | | 321K| 4397K| | 41 (5)| 00:00:01 | Q1,06 | PCWP | |
                        | 51 | PX SEND BROADCAST | :TQ10002 | 321K| 4397K| | 41 (5)| 00:00:01 | Q1,02 | P->P | BROADCAST |
                        | 52 | PX BLOCK ITERATOR | | 321K| 4397K| | 41 (5)| 00:00:01 | Q1,02 | PCWC | |
                        | 53 | TABLE ACCESS FULL | ASM_GIP_TOP_PACKAGES | 321K| 4397K| | 41 (5)| 00:00:01 | Q1,02 | PCWP | |
                        |* 54 | HASH JOIN OUTER | | 11M| 821M| 76M| 169K (1)| 00:33:52 | Q1,06 | PCWP | |
                        | 55 | PX RECEIVE | | 9414K| 502M| | 130K (1)| 00:26:09 | Q1,06 | PCWP | |
                        | 56 | PX SEND HASH | :TQ10003 | 9414K| 502M| | 130K (1)| 00:26:09 | Q1,03 | P->P | HASH |
                        | 57 | PX BLOCK ITERATOR | | 9414K| 502M| | 130K (1)| 00:26:09 | Q1,03 | PCWC | |
                        |* 58 | TABLE ACCESS FULL | ASM_GIP_TOP_HISTORY | 9414K| 502M| | 130K (1)| 00:26:09 | Q1,03 | PCWP | |
                        | 59 | PX RECEIVE | | 26M| 405M| | 30259 (1)| 00:06:04 | Q1,06 | PCWP | |
                        | 60 | PX SEND HASH | :TQ10004 | 26M| 405M| | 30259 (1)| 00:06:04 | Q1,04 | P->P | HASH |
                        | 61 | PX BLOCK ITERATOR | | 26M| 405M| | 30259 (1)| 00:06:04 | Q1,04 | PCWC | |
                        | 62 | TABLE ACCESS FULL | ASM_GIP_TOP | 26M| 405M| | 30259 (1)| 00:06:04 | Q1,04 | PCWP | |
                        |* 63 | TABLE ACCESS BY INDEX ROWID | CTL_BATCH_MASTER | 1 | 9 | | 2 (0)| 00:00:01 | | | |
                        |* 64 | INDEX UNIQUE SCAN | PK_BATCH_MASTER | 1 | | | 1 (0)| 00:00:01 | | | |
                        |* 65 | FILTER | | | | | | | | | |
                        | 66 | PX COORDINATOR | | | | | | | | | |
                        | 67 | PX SEND QC (RANDOM) | :TQ20010 | 37M| 5767M| | 179K (1)| 00:35:49 | Q2,10 | P->S | QC (RAND) |
                        |* 68 | HASH JOIN RIGHT OUTER BUFFERED | | 37M| 5767M| | 179K (1)| 00:35:49 | Q2,10 | PCWP | |
                        | 69 | PX RECEIVE | | 2854K| 43M| | 3193 (1)| 00:00:39 | Q2,10 | PCWP | |
                        | 70 | PX SEND HASH | :TQ20007 | 2854K| 43M| | 3193 (1)| 00:00:39 | Q2,07 | P->P | HASH |
                        | 71 | PX BLOCK ITERATOR | | 2854K| 43M| | 3193 (1)| 00:00:39 | Q2,07 | PCWC | |
                        | 72 | TABLE ACCESS FULL | ASM_GIP_COMPUNDRECEIPT | 2854K| 43M| | 3193 (1)| 00:00:39 | Q2,07 | PCWP | |
                        |* 73 | HASH JOIN RIGHT OUTER | | 22M| 3024M| | 175K (1)| 00:35:10 | Q2,10 | PCWP | |
                        | 74 | PX RECEIVE | | 2807K| 42M| | 3573 (2)| 00:00:43 | Q2,10 | PCWP | |
                        | 75 | PX SEND HASH | :TQ20008 | 2807K| 42M| | 3573 (2)| 00:00:43 | Q2,08 | P->P | HASH |
                        | 76 | PX BLOCK ITERATOR | | 2807K| 42M| | 3573 (2)| 00:00:43 | Q2,08 | PCWC | |
                        | 77 | TABLE ACCESS FULL | ASM_GIP_COMPUNDLW | 2807K| 42M| | 3573 (2)| 00:00:43 | Q2,08 | PCWP | |
                        | 78 | PX RECEIVE | | 13M| 1592M| | 172K (1)| 00:34:26 | Q2,10 | PCWP | |
                        | 79 | PX SEND HASH | :TQ20009 | 13M| 1592M| | 172K (1)| 00:34:26 | Q2,09 | P->P | HASH |
                        |* 80 | HASH JOIN RIGHT OUTER BUFFERED | | 13M| 1592M| | 172K (1)| 00:34:26 | Q2,09 | PCWP | |
                        | 81 | PX RECEIVE | | 9685K| 157M| | 2635 (3)| 00:00:32 | Q2,09 | PCWP | |
                        | 82 | PX SEND HASH | :TQ20005 | 9685K| 157M| | 2635 (3)| 00:00:32 | Q2,05 | P->P | HASH |
                        | 83 | PX BLOCK ITERATOR | | 9685K| 157M| | 2635 (3)| 00:00:32 | Q2,05 | PCWC | |
                        | 84 | TABLE ACCESS FULL | ASM_GIP_PIPT_DATA | 9685K| 157M| | 2635 (3)| 00:00:32 | Q2,05 | PCWP | |
                        | 85 | PX RECEIVE | | 11M| 1265M| | 169K (1)| 00:33:54 | Q2,09 | PCWP | |
                        | 86 | PX SEND HASH | :TQ20006 | 11M| 1265M| | 169K (1)| 00:33:54 | Q2,06 | P->P | HASH |
                        |* 87 | HASH JOIN RIGHT OUTER BUFFERED| | 11M| 1265M| | 169K (1)| 00:33:54 | Q2,06 | PCWP | |
                        | 88 | PX RECEIVE | | 35 | 420 | | 2 (0)| 00:00:01 | Q2,06 | PCWP | |
                        | 89 | PX SEND BROADCAST | :TQ20000 | 35 | 420 | | 2 (0)| 00:00:01 | Q2,00 | P->P | BROADCAST |
                        | 90 | PX BLOCK ITERATOR | | 35 | 420 | | 2 (0)| 00:00:01 | Q2,00 | PCWC | |
                        | 91 | TABLE ACCESS FULL | ASM_GIP_TOPSTORIES | 35 | 420 | | 2 (0)| 00:00:01 | Q2,00 | PCWP | |
                        |* 92 | HASH JOIN RIGHT OUTER | | 11M| 1128M| | 169K (1)| 00:33:53 | Q2,06 | PCWP | |
                        | 93 | PX RECEIVE | | 78 | 1014 | | 2 (0)| 00:00:01 | Q2,06 | PCWP | |
                        | 94 | PX SEND BROADCAST | :TQ20001 | 78 | 1014 | | 2 (0)| 00:00:01 | Q2,01 | P->P | BROADCAST |
                        | 95 | PX BLOCK ITERATOR | | 78 | 1014 | | 2 (0)| 00:00:01 | Q2,01 | PCWC | |
                        | 96 | TABLE ACCESS FULL | ASM_GIP_PIPT_DATA | 78 | 1014 | | 2 (0)| 00:00:01 | Q2,01 | PCWP | |
                        |* 97 | HASH JOIN RIGHT OUTER | | 11M| 980M| | 169K (1)| 00:33:53 | Q2,06 | PCWP | |
                        | 98 | PX RECEIVE | | 321K| 4397K| | 41 (5)| 00:00:01 | Q2,06 | PCWP | |
                        | 99 | PX SEND BROADCAST | :TQ20002 | 321K| 4397K| | 41 (5)| 00:00:01 | Q2,02 | P->P | BROADCAST |
                        | 100 | PX BLOCK ITERATOR | | 321K| 4397K| | 41 (5)| 00:00:01 | Q2,02 | PCWC | |
                        | 101 | TABLE ACCESS FULL | ASM_GIP_TOP_PACKAGES | 321K| 4397K| | 41 (5)| 00:00:01 | Q2,02 | PCWP | |
                        |*102 | HASH JOIN OUTER | | 11M| 821M| 76M| 169K (1)| 00:33:52 | Q2,06 | PCWP | |
                        | 103 | PX RECEIVE | | 9414K| 502M| | 130K (1)| 00:26:09 | Q2,06 | PCWP | |
                        | 104 | PX SEND HASH | :TQ20003 | 9414K| 502M| | 130K (1)| 00:26:09 | Q2,03 | P->P | HASH |
                        | 105 | PX BLOCK ITERATOR | | 9414K| 502M| | 130K (1)| 00:26:09 | Q2,03 | PCWC | |
                        |*106 | TABLE ACCESS FULL | ASM_GIP_TOP_HISTORY | 9414K| 502M| | 130K (1)| 00:26:09 | Q2,03 | PCWP | |
                        | 107 | PX RECEIVE | | 26M| 405M| | 30259 (1)| 00:06:04 | Q2,06 | PCWP | |
                        | 108 | PX SEND HASH | :TQ20004 | 26M| 405M| | 30259 (1)| 00:06:04 | Q2,04 | P->P | HASH |
                        | 109 | PX BLOCK ITERATOR | | 26M| 405M| | 30259 (1)| 00:06:04 | Q2,04 | PCWC | |
                        | 110 | TABLE ACCESS FULL | ASM_GIP_TOP | 26M| 405M| | 30259 (1)| 00:06:04 | Q2,04 | PCWP | |
                        |*111 | TABLE ACCESS BY INDEX ROWID | CTL_BATCH_MASTER | 1 | 9 | | 2 (0)| 00:00:01 | | | |
                        |*112 | INDEX UNIQUE SCAN | PK_BATCH_MASTER | 1 | | | 1 (0)| 00:00:01 | | | |
                        |*113 | TABLE ACCESS BY INDEX ROWID | CTL_BATCH_MASTER | 1 | 9 | | 2 (0)| 00:00:01 | | | |
                        |*114 | INDEX UNIQUE SCAN | PK_BATCH_MASTER | 1 | | | 1 (0)| 00:00:01 | | | |
                        ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

                        6 - access("T"."DWH_TABLE_B_SRCSYS"(+)="X"."DWH_TABLE_B_SRCSYS" AND "X"."DWH_TABLE_B_GAME"="T"."DWH_TABLE_B_GAME"(+) AND
                        "X"."DWH_TABLE_B_SRCID"="T"."DWH_TABLE_B_SRCID"(+))
                        14 - filter("RNK"=1)
                        15 - filter(RANK() OVER ( PARTITION BY "ASM_GIP_TOP_HISTORY"."LOH_ID" ORDER BY NVL("ASM_GIP_TOP_HISTORY"."BATCH",(-1)) DESC ,NVL("ASM_GIP_TOP"."BATCH",(-1))
                        DESC ,NVL("ASM_GIP_TOP_PACKAGES"."BATCH",(-1)) DESC ,NVL("ASM_GIP_TOP_SERIES"."BATCH",(-1)) DESC ,NVL("ASM_GIP_TOPSTORIES"."BATCH",(-1)) DESC
                        ,NVL("ASM_GIP_PIPT_DATA"."BATCH",(-1)) DESC ,NVL("ASM_GIP_COMPUNDLW"."BATCH",(-1)) DESC ,NVL("ASM_GIP_COMPUNDRECEIPT"."BATCH",(-1)) DESC )<=1)
                        17 - filter( EXISTS (SELECT 0 FROM "CTL_BATCH_MASTER" "CTL_BATCH_MASTER" WHERE "CTL_BATCH_ID"=NVL(:B1,(-1)) AND "CTL_BATCH_STATUS"=0))
                        20 - access("ASM_GIP_PIPT_DATA"."TIP_CLW_ID"="CLE_ID"(+))
                        25 - access("ASM_GIP_PIPT_DATA"."TIP_CLW_ID"="CLW_ID"(+))
                        32 - access("TIP_LOH_ID"(+)="ASM_GIP_TOP_HISTORY"."LOH_ID")
                        39 - access("ASM_GIP_TOP_SERIES "."LSE_LTY_ID"="LTY_ID"(+))
                        44 - access("ASM_GIP_TOP_PACKAGES"."LOP_LSE_ID"="LSE_ID"(+))
                        49 - access("ASM_GIP_TOP"."LOT_LOP_ID"="LOP_ID"(+))
                        54 - access("LOH_LOT_ID"="LOT_ID"(+))
                        58 - filter("ASM_GIP_TOP_HISTORY"."LOH_TYPE"=50)
                        63 - filter("CTL_BATCH_STATUS"=0)
                        64 - access("CTL_BATCH_ID"=NVL(:B1,(-1)))
                        65 - filter(LNNVL( EXISTS (SELECT 0 FROM "CTL_BATCH_MASTER" "CTL_BATCH_MASTER" WHERE "CTL_BATCH_ID"=NVL(:B1,(-1)) AND "CTL_BATCH_STATUS"=0)) AND EXISTS (SELECT
                        0 FROM "CTL_BATCH_MASTER" "CTL_BATCH_MASTER" WHERE "CTL_BATCH_ID"=NVL(:B2,(-1)) AND "CTL_BATCH_STATUS"=0))
                        68 - access("ASM_GIP_PIPT_DATA"."TIP_CLW_ID"="CLE_ID"(+))
                        73 - access("ASM_GIP_PIPT_DATA"."TIP_CLW_ID"="CLW_ID"(+))
                        80 - access("TIP_LOH_ID"(+)="ASM_GIP_TOP_HISTORY"."LOH_ID")
                        87 - access("ASM_GIP_TOP_SERIES "."LSE_LTY_ID"="LTY_ID"(+))
                        92 - access("ASM_GIP_TOP_PACKAGES"."LOP_LSE_ID"="LSE_ID"(+))
                        97 - access("ASM_GIP_TOP"."LOT_LOP_ID"="LOP_ID"(+))
                        102 - access("LOH_LOT_ID"="LOT_ID"(+))
                        106 - filter("ASM_GIP_TOP_HISTORY"."LOH_TYPE"=50)
                        111 - filter("CTL_BATCH_STATUS"=0)
                        112 - access("CTL_BATCH_ID"=NVL(:B1,(-1)))
                        113 - filter("CTL_BATCH_STATUS"=0)
                        114 - access("CTL_BATCH_ID"=NVL(:B1,(-1)))
                        • 9. Re: SQL using large amounts of temp space.
                          William Robertson
                          In answer to your original question, the query doesn't have
                          select x.hist, x.date, x.name ...etc, x.*
                          it actually has
                          select ora_hash(x.hist || '|' || x.date || '|' || x.name ...etc) as table_b_checksum, x.*
                          As for temp space, it uses a lot of hash joins and a window sort, so unless you can either restrict the row set early (so the hashes are smaller) or change it to nested-loops (which may be much slower - but I don't know your data) you might just need a lot of space to achieve the requirement.
                          • 10. Re: SQL using large amounts of temp space.
                            user3206995
                            yep, that was my thought too. I am not familiar with the data set and asked the third party company to confirm that the data has not been corrupted in any way.

                            Thanks for your feedback.
                            • 11. Re: SQL using large amounts of temp space.
                              William Robertson
                              That's a good point - we experienced a runaway SQL statement recently (execution time normally minutes but taking several hours or failing) and our first thought was a plan flip due to stats or bind variable peeking etc, but after ruling that out we discovered a load of duplicated data (it's an old batch system with a terrible data model) whose only effect was exponential execution times for certain processes.