This discussion is archived
11 Replies Latest reply: Feb 3, 2013 3:32 AM by William Robertson RSS

SQL using large amounts of temp space.

user3206995 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points