9 Replies Latest reply on Feb 23, 2013 11:41 AM by mtefft

    Need help to debug a SQL (ORA-01652: unable to extend temp segment by 1280)

    rogers42
      Hi Folks,

      I was recently assigned to a new project to investigate a query that runs for an hour (and sometimes more) before terminating with ORA-01652 error

      Environment Details:

      Database Version: 10.2.0.2
      Platform: AIX

      Problem Description:

      The query runs for 1+ hours before terminating with the following error message

      ORA-12801: error signaled in parallel query server P020
      ORA-01652: unable to extend temp segment by 1280 in tablespace TEMP


      Query:
      ALTER SESSION SET NLS_DATE_FORMAT= 'MM/DD/YYYY'
      
      select    a11.SHIP_FROM_LOC_KEY SHIP_FROM_LOC_KEY,
          a17.LOC_DESC LOC_DESC,
          a16.SHIP_TO_ITEM_DEPT SHIP_TO_ITEM_DEPT,
          a16.SHIP_TO_ITEM_DEPT_DSCR SHIP_TO_ITEM_DEPT_DSCR,
          a11.SHIP_TO_ITEM_KEY SHIP_TO_ITEM_KEY,
          a16.SHIP_TO_ITEM_DSCR SHIP_TO_ITEM_DSCR,
          a16.SHIP_TO_ITEM_NBR SHIP_TO_ITEM_NBR,
          a16.SHIP_TO_ITEM_NBR SHIP_TO_ITEM_NBR0,
          a16.SHIP_TO_ITEM_SIZE SHIP_TO_ITEM_SIZE,
          a16.SHIP_TO_STR_PACK SHIP_TO_STR_PACK,
          a13.CUSTOM_CM_LVL3_KEY CUSTOM_CM_LVL3_KEY,
          a13.CUSTOM_CM_LVL3_DESC CUSTOM_CM_LVL3_DESC,
          a13.CUSTOM_CM_LVL4_KEY CUSTOM_CM_LVL4_KEY,
          a13.CUSTOM_CM_LVL4_DESC CUSTOM_CM_LVL4_DESC,
          a13.CUSTOM_CM_LVL5_KEY CUSTOM_CM_LVL5_KEY,
          a13.CUSTOM_CM_LVL5_DESC CUSTOM_CM_LVL5_DESC,
          a12.LOC_HIER_LVL3_KEY LOC_HIER_LVL3_KEY,
          a12.LOC_HIER_LVL3_DESC LOC_HIER_LVL3_DESC,
          a15.FISC_YR_KEY FISC_YR_KEY,
          a15.FISC_YR_DESC FISC_YR_DESC,
          a15.WK_KEY WK_KEY,
          a15.WK_DESC WK_DESC,
          sum(a11.QTY_SHIPPED) INVENTORYCASESSHIPPE
      from    F_WHSE_SHPMT_VW    a11
          join    D_LOC_STORE_VW    a12
           on     (a11.SHIP_TO_LOC_KEY = a12.LOC_KEY)
          join    D_PROD_UPC_SS_VW    a13
           on     (a11.PROD_KEY = a13.PROD_KEY)
          join    D_SHIP_FROM_PRODUCT_PURCH_VW    a14
           on     (a11.SHIP_FROM_ITEM_KEY = a14.SHIP_FROM_ITEM_KEY)
          join    D_TF_DAY_VW    a15
           on     (a11.TIMEFRAME_KEY = a15.TIMEFRAME_KEY)
          join    D_SHIP_TO_PRODUCT_PURCH_VW    a16
           on     (a11.SHIP_TO_ITEM_KEY = a16.SHIP_TO_ITEM_KEY and 
          a11.SHIP_TO_OPCO_KEY = a16.SHIP_TO_OPCO_KEY and 
          a12.CHN_CD = a16.SHIP_TO_CHN_CD)
          join    D_LOC_WHSE_VW    a17
           on     (a11.SHIP_FROM_LOC_KEY = a17.LOC_KEY)
      where    (a11.SHIP_FROM_LOC_KEY in (15509, 16910, 16912)
      and a12.LOC_HIER_LVL3_KEY in (17256, 17257)
      and ((a14.SHIP_FROM_ITEM_DEPT = 5
      and a14.SHIP_FROM_ITEM_DEPT_DSCR in ('DELI', 'GM', 'GROCERY', 'MEAT', 'MEAT OTHER', 'SEAFOOD', 'FROZEN', 'FROZEN GROCERY'))
      or (a14.SHIP_FROM_ITEM_DEPT = 1
      and a14.SHIP_FROM_ITEM_DEPT_DSCR in ('DAIRY', 'DELI', 'GROCERY', 'GROCERY OTHER', 'MEAT', 'PRODUCE'))
      or (a14.SHIP_FROM_ITEM_DEPT = 16
      and a14.SHIP_FROM_ITEM_DEPT_DSCR in ('FLORAL', 'GROCERY', 'HBC', 'MEAT', 'PRODUCE'))
      or (a14.SHIP_FROM_ITEM_DEPT = 20
      and a14.SHIP_FROM_ITEM_DEPT_DSCR in ('DELI', 'MEAT', 'SALAD BAR 6/9/10', 'SALAD OTHER'))
      or (a14.SHIP_FROM_ITEM_DEPT = 31
      and a14.SHIP_FROM_ITEM_DEPT_DSCR in ('FLORAL', 'PRODUCE', 'SEAFOOD/FISH6/9 DPT21 623', 'FROZEN SEAFOOD'))
      or (a14.SHIP_FROM_ITEM_DEPT = 15
      and a14.SHIP_FROM_ITEM_DEPT_DSCR in ('DAIRY', 'DELI', 'GM'))
      or (a14.SHIP_FROM_ITEM_DEPT = 23
      and a14.SHIP_FROM_ITEM_DEPT_DSCR in ('DELI', 'PREPARED FOODS', 'SEAFOOD'))
      or (a14.SHIP_FROM_ITEM_DEPT = 30
      and a14.SHIP_FROM_ITEM_DEPT_DSCR in ('FLORAL', 'PRODUCE'))
      or (a14.SHIP_FROM_ITEM_DEPT = 21
      and a14.SHIP_FROM_ITEM_DEPT_DSCR in ('FRESH FISH', 'FROZEN MEAT'))
      or (a14.SHIP_FROM_ITEM_DEPT = 24
      and a14.SHIP_FROM_ITEM_DEPT_DSCR in ('PREPARED FOODS', 'FROZEN SEAFOOD'))
      or (a14.SHIP_FROM_ITEM_DEPT_DSCR = 'NATURAL FOODS'
      and a14.SHIP_FROM_ITEM_DEPT in (25, 28))
      or ((a14.SHIP_FROM_ITEM_DEPT = 10 and a14.SHIP_FROM_ITEM_DEPT_DSCR = 'DAIRY') 
      or (a14.SHIP_FROM_ITEM_DEPT=8 and a14.SHIP_FROM_ITEM_DEPT_DSCR='DAIRY OTHER') 
      or (a14.SHIP_FROM_ITEM_DEPT=40 and a14.SHIP_FROM_ITEM_DEPT_DSCR='DELI') 
      or (a14.SHIP_FROM_ITEM_DEPT=3 and a14.SHIP_FROM_ITEM_DEPT_DSCR='DELI OTHER') 
      or (a14.SHIP_FROM_ITEM_DEPT=7 and a14.SHIP_FROM_ITEM_DEPT_DSCR='FLORIST OTHER') 
      or (a14.SHIP_FROM_ITEM_DEPT=2 and a14.SHIP_FROM_ITEM_DEPT_DSCR='GM OTHER') 
      or (a14.SHIP_FROM_ITEM_DEPT=45 and a14.SHIP_FROM_ITEM_DEPT_DSCR='GROCERY') 
      or (a14.SHIP_FROM_ITEM_DEPT=9 and a14.SHIP_FROM_ITEM_DEPT_DSCR='HBC OTHER') 
      or (a14.SHIP_FROM_ITEM_DEPT=4 and a14.SHIP_FROM_ITEM_DEPT_DSCR='PRODUCE OTHER') 
      or (a14.SHIP_FROM_ITEM_DEPT=46 and a14.SHIP_FROM_ITEM_DEPT_DSCR='FROZEN BAKERY') 
      or (a14.SHIP_FROM_ITEM_DEPT=41 and a14.SHIP_FROM_ITEM_DEPT_DSCR='FROZEN DELI') 
      or (a14.SHIP_FROM_ITEM_DEPT=6 and a14.SHIP_FROM_ITEM_DEPT_DSCR='FROZEN FOOD')))
      and a15.DAY_DT > To_Date('12/31/2011', 'mm-dd-yyyy'))
      group by    a11.SHIP_FROM_LOC_KEY,
          a17.LOC_DESC,
          a16.SHIP_TO_ITEM_DEPT,
          a16.SHIP_TO_ITEM_DEPT_DSCR,
          a11.SHIP_TO_ITEM_KEY,
          a16.SHIP_TO_ITEM_DSCR,
          a16.SHIP_TO_ITEM_NBR,
          a16.SHIP_TO_ITEM_NBR,
          a16.SHIP_TO_ITEM_SIZE,
          a16.SHIP_TO_STR_PACK,
          a13.CUSTOM_CM_LVL3_KEY,
          a13.CUSTOM_CM_LVL3_DESC,
          a13.CUSTOM_CM_LVL4_KEY,
          a13.CUSTOM_CM_LVL4_DESC,
          a13.CUSTOM_CM_LVL5_KEY,
          a13.CUSTOM_CM_LVL5_DESC,
          a12.LOC_HIER_LVL3_KEY,
          a12.LOC_HIER_LVL3_DESC,
          a15.FISC_YR_KEY,
          a15.FISC_YR_DESC,
          a15.wk_key,
          a15.wk_desc;
      Explain Plan:
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 3110051182
      
      ---------------------------------------------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                              | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
      ---------------------------------------------------------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                       |                 |   129K|    51M|       | 58775  (14)| 00:29:24 |       |       |        |      |            |
      |   1 |  PX COORDINATOR                        |                 |       |       |       |            |          |       |       |        |      |            |
      |   2 |   PX SEND QC (RANDOM)                  | :TQ10009        |   129K|    51M|       | 58775  (14)| 00:29:24 |       |       |  Q1,09 | P->S | QC (RAND)  |
      |   3 |    HASH GROUP BY                       |                 |   129K|    51M|   109M| 58775  (14)| 00:29:24 |       |       |  Q1,09 | PCWP |            |
      |   4 |     PX RECEIVE                         |                 |   129K|    51M|       | 58775  (14)| 00:29:24 |       |       |  Q1,09 | PCWP |            |
      |   5 |      PX SEND HASH                      | :TQ10008        |   129K|    51M|       | 58775  (14)| 00:29:24 |       |       |  Q1,08 | P->P | HASH       |
      |   6 |       HASH GROUP BY                    |                 |   129K|    51M|   109M| 58775  (14)| 00:29:24 |       |       |  Q1,08 | PCWP |            |
      |*  7 |        HASH JOIN                       |                 |   129K|    51M|       | 57201  (14)| 00:28:37 |       |       |  Q1,08 | PCWP |            |
      |   8 |         BUFFER SORT                    |                 |       |       |       |            |          |       |       |  Q1,08 | PCWC |            |
      |   9 |          PX RECEIVE                    |                 |   161K|  2369K|       |   765  (27)| 00:00:23 |       |       |  Q1,08 | PCWP |            |
      |  10 |           PX SEND BROADCAST            | :TQ10004        |   161K|  2369K|       |   765  (27)| 00:00:23 |       |       |        | S->P | BROADCAST  |
      |  11 |            PARTITION RANGE ALL         |                 |   161K|  2369K|       |   765  (27)| 00:00:23 |     1 |    11 |        |      |            |
      |* 12 |             TABLE ACCESS FULL          | D_PRODUCT_PURCH |   161K|  2369K|       |   765  (27)| 00:00:23 |     1 |    11 |        |      |            |
      |* 13 |         HASH JOIN                      |                 |   129K|    50M|       | 56435  (14)| 00:28:14 |       |       |  Q1,08 | PCWP |            |
      |  14 |          PX RECEIVE                    |                 |   129K|    28M|       | 56123  (14)| 00:28:04 |       |       |  Q1,08 | PCWP |            |
      |  15 |           PX SEND HASH                 | :TQ10006        |   129K|    28M|       | 56123  (14)| 00:28:04 |       |       |  Q1,06 | P->P | HASH       |
      |* 16 |            HASH JOIN                   |                 |   129K|    28M|       | 56123  (14)| 00:28:04 |       |       |  Q1,06 | PCWP |            |
      |  17 |             PX RECEIVE                 |                 |   460K|    69M|       | 55443  (14)| 00:27:44 |       |       |  Q1,06 | PCWP |            |
      |  18 |              PX SEND HASH              | :TQ10005        |   460K|    69M|       | 55443  (14)| 00:27:44 |       |       |  Q1,05 | P->P | HASH       |
      |* 19 |               HASH JOIN                |                 |   460K|    69M|       | 55443  (14)| 00:27:44 |       |       |  Q1,05 | PCWP |            |
      |  20 |                BUFFER SORT             |                 |       |       |       |            |          |       |       |  Q1,05 | PCWC |            |
      |  21 |                 PX RECEIVE             |                 |   491 | 14730 |       |     3   (0)| 00:00:01 |       |       |  Q1,05 | PCWP |            |
      |  22 |                  PX SEND BROADCAST     | :TQ10000        |   491 | 14730 |       |     3   (0)| 00:00:01 |       |       |        | S->P | BROADCAST  |
      |  23 |                   PARTITION RANGE ALL  |                 |   491 | 14730 |       |     3   (0)| 00:00:01 |     1 |    11 |        |      |            |
      |* 24 |                    TABLE ACCESS FULL   | D_LCTN          |   491 | 14730 |       |     3   (0)| 00:00:01 |     1 |    11 |        |      |            |
      |* 25 |                HASH JOIN               |                 |   942K|   115M|       | 55438  (14)| 00:27:44 |       |       |  Q1,05 | PCWP |            |
      |  26 |                 BUFFER SORT            |                 |       |       |       |            |          |       |       |  Q1,05 | PCWC |            |
      |  27 |                  PX RECEIVE            |                 |   409 | 22086 |       |     2   (0)| 00:00:01 |       |       |  Q1,05 | PCWP |            |
      |  28 |                   PX SEND BROADCAST    | :TQ10001        |   409 | 22086 |       |     2   (0)| 00:00:01 |       |       |        | S->P | BROADCAST  |
      |* 29 |                    TABLE ACCESS FULL   | D_DAY           |   409 | 22086 |       |     2   (0)| 00:00:01 |       |       |        |      |            |
      |* 30 |                 HASH JOIN              |                 |  1933K|   136M|       | 55434  (14)| 00:27:44 |       |       |  Q1,05 | PCWP |            |
      |  31 |                  BUFFER SORT           |                 |       |       |       |            |          |       |       |  Q1,05 | PCWC |            |
      |  32 |                   PX RECEIVE           |                 |     1 |    25 |       |     3   (0)| 00:00:01 |       |       |  Q1,05 | PCWP |            |
      |  33 |                    PX SEND BROADCAST   | :TQ10002        |     1 |    25 |       |     3   (0)| 00:00:01 |       |       |        | S->P | BROADCAST  |
      |  34 |                     PARTITION RANGE ALL|                 |     1 |    25 |       |     3   (0)| 00:00:01 |     1 |    11 |        |      |            |
      |* 35 |                      TABLE ACCESS FULL | D_LCTN          |     1 |    25 |       |     3   (0)| 00:00:01 |     1 |    11 |        |      |            |
      |  36 |                  PX BLOCK ITERATOR     |                 |   289M|    13G|       | 55106  (14)| 00:27:34 |     1 |     5 |  Q1,05 | PCWC |            |
      |* 37 |                   TABLE ACCESS FULL    | F_WHSE_SHPMT    |   289M|    13G|       | 55106  (14)| 00:27:34 |   KEY |   KEY |  Q1,05 | PCWP |            |
      |  38 |             BUFFER SORT                |                 |       |       |       |            |          |       |       |  Q1,06 | PCWC |            |
      |  39 |              PX RECEIVE                |                 |  1486K|   102M|       |   676  (18)| 00:00:21 |       |       |  Q1,06 | PCWP |            |
      |  40 |               PX SEND HASH             | :TQ10003        |  1486K|   102M|       |   676  (18)| 00:00:21 |       |       |        | S->P | HASH       |
      |  41 |                PARTITION RANGE ALL     |                 |  1486K|   102M|       |   676  (18)| 00:00:21 |     1 |    11 |        |      |            |
      |* 42 |                 TABLE ACCESS FULL      | D_PRODUCT_PURCH |  1486K|   102M|       |   676  (18)| 00:00:21 |     1 |    11 |        |      |            |
      |  43 |          PX RECEIVE                    |                 |   732K|   122M|       |   311   (2)| 00:00:10 |       |       |  Q1,08 | PCWP |            |
      |  44 |           PX SEND HASH                 | :TQ10007        |   732K|   122M|       |   311   (2)| 00:00:10 |       |       |  Q1,07 | P->P | HASH       |
      |  45 |            PX BLOCK ITERATOR           |                 |   732K|   122M|       |   311   (2)| 00:00:10 |    11 |    11 |  Q1,07 | PCWC |            |
      |* 46 |             TABLE ACCESS FULL          | D_PRODUCT_SS    |   732K|   122M|       |   311   (2)| 00:00:10 |    11 |    11 |  Q1,07 | PCWP |            |
      ---------------------------------------------------------------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         7 - access("SHIP_FROM_ITEM_KEY"="ITEM_KEY")
        12 - filter("ITEM_DEPT"=16 AND ("ITEM_DEPT_DSCR"='FLORAL' OR "ITEM_DEPT_DSCR"='GROCERY' OR "ITEM_DEPT_DSCR"='HBC' OR "ITEM_DEPT_DSCR"='MEAT' OR
                    "ITEM_DEPT_DSCR"='PRODUCE') OR "ITEM_DEPT"=1 AND ("ITEM_DEPT_DSCR"='DAIRY' OR "ITEM_DEPT_DSCR"='DELI' OR "ITEM_DEPT_DSCR"='GROCERY' OR
                    "ITEM_DEPT_DSCR"='GROCERY OTHER' OR "ITEM_DEPT_DSCR"='MEAT' OR "ITEM_DEPT_DSCR"='PRODUCE') OR "ITEM_DEPT"=15 AND ("ITEM_DEPT_DSCR"='DAIRY' OR
                    "ITEM_DEPT_DSCR"='DELI' OR "ITEM_DEPT_DSCR"='GM') OR "ITEM_DEPT"=5 AND ("ITEM_DEPT_DSCR"='DELI' OR "ITEM_DEPT_DSCR"='FROZEN' OR
                    "ITEM_DEPT_DSCR"='FROZEN GROCERY' OR "ITEM_DEPT_DSCR"='GM' OR "ITEM_DEPT_DSCR"='GROCERY' OR "ITEM_DEPT_DSCR"='MEAT' OR "ITEM_DEPT_DSCR"='MEAT OTHER'
                    OR "ITEM_DEPT_DSCR"='SEAFOOD') OR "ITEM_DEPT"=2 AND "ITEM_DEPT_DSCR"='GM OTHER' OR "ITEM_DEPT"=3 AND "ITEM_DEPT_DSCR"='DELI OTHER' OR "ITEM_DEPT"=4
                    AND "ITEM_DEPT_DSCR"='PRODUCE OTHER' OR "ITEM_DEPT"=6 AND "ITEM_DEPT_DSCR"='FROZEN FOOD' OR "ITEM_DEPT"=7 AND "ITEM_DEPT_DSCR"='FLORIST OTHER' OR
                    "ITEM_DEPT"=8 AND "ITEM_DEPT_DSCR"='DAIRY OTHER' OR "ITEM_DEPT"=9 AND "ITEM_DEPT_DSCR"='HBC OTHER' OR "ITEM_DEPT"=10 AND "ITEM_DEPT_DSCR"='DAIRY' OR
                    "ITEM_DEPT"=40 AND "ITEM_DEPT_DSCR"='DELI' OR "ITEM_DEPT"=41 AND "ITEM_DEPT_DSCR"='FROZEN DELI' OR "ITEM_DEPT"=45 AND "ITEM_DEPT_DSCR"='GROCERY' OR
                    "ITEM_DEPT"=46 AND "ITEM_DEPT_DSCR"='FROZEN BAKERY' OR "ITEM_DEPT"=20 AND ("ITEM_DEPT_DSCR"='DELI' OR "ITEM_DEPT_DSCR"='MEAT' OR
                    "ITEM_DEPT_DSCR"='SALAD BAR 6/9/10' OR "ITEM_DEPT_DSCR"='SALAD OTHER') OR "ITEM_DEPT"=30 AND ("ITEM_DEPT_DSCR"='FLORAL' OR "ITEM_DEPT_DSCR"='PRODUCE')
                    OR "ITEM_DEPT"=31 AND ("ITEM_DEPT_DSCR"='FLORAL' OR "ITEM_DEPT_DSCR"='FROZEN SEAFOOD' OR "ITEM_DEPT_DSCR"='PRODUCE' OR
                    "ITEM_DEPT_DSCR"='SEAFOOD/FISH6/9 DPT21 623') OR "ITEM_DEPT_DSCR"='NATURAL FOODS' AND ("ITEM_DEPT"=25 OR "ITEM_DEPT"=28) OR "ITEM_DEPT"=23 AND
                    ("ITEM_DEPT_DSCR"='DELI' OR "ITEM_DEPT_DSCR"='PREPARED FOODS' OR "ITEM_DEPT_DSCR"='SEAFOOD') OR "ITEM_DEPT"=24 AND ("ITEM_DEPT_DSCR"='FROZEN SEAFOOD'
                    OR "ITEM_DEPT_DSCR"='PREPARED FOODS') OR "ITEM_DEPT"=21 AND ("ITEM_DEPT_DSCR"='FRESH FISH' OR "ITEM_DEPT_DSCR"='FROZEN MEAT'))
        13 - access("PROD_KEY"="PROD_KEY")
        16 - access("SHIP_TO_ITEM_KEY"="ITEM_KEY" AND "SHIP_TO_OPCO_KEY"="OPCO_KEY" AND "CHN_CD"="CHN_CD")
        19 - access("SHIP_TO_LOC_KEY"="LOC_KEY")
        24 - filter("IA_DIM_LVL_DESC"='STORE' AND ("LOC_HIER_LVL3_KEY"=17256 OR "LOC_HIER_LVL3_KEY"=17257) AND ("CHN_CD"='010' OR "CHN_CD"='015' OR
                    "CHN_CD"='020' OR "CHN_CD"='025' OR "CHN_CD"='035' OR "CHN_CD"='040' OR "CHN_CD"='045' OR "CHN_CD"='050'))
        25 - access("TIMEFRAME_KEY"="TIMEFRAME_KEY")
        29 - filter("DAY_DT">TO_DATE('2011-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
        30 - access("SHIP_FROM_LOC_KEY"="LOC_KEY")
        35 - filter("IA_DIM_LVL_DESC"='WHSE' AND ("LOC_KEY"=15509 OR "LOC_KEY"=16910 OR "LOC_KEY"=16912))
        37 - filter("SHIP_FROM_LOC_KEY"=15509 OR "SHIP_FROM_LOC_KEY"=16910 OR "SHIP_FROM_LOC_KEY"=16912)
        42 - filter("CHN_CD"='010' OR "CHN_CD"='015' OR "CHN_CD"='020' OR "CHN_CD"='025' OR "CHN_CD"='035' OR "CHN_CD"='040' OR "CHN_CD"='045' OR
                    "CHN_CD"='050')
        46 - filter("IA_DIM_LVL_DESC"='UPC')
      Following are some additional details

      The maximum size for the temporary tablespace size is 36,500 Mbytes

      Based on the explain plan, the query should not occupy more than 109 MBytes of temporary tablespace

      The fact table has 1.2 billion rows. The table is partitioned. Stats are being collected on the partition level on daily basis. The new partitions have more recent stats, while the older partitions have not-so-up to date stats

      Following columns are indexed on the fact table

      *ship_from_loc_keyBitmap     
      ship_to_opco_key
      Bitmap     
      "invc_nbr
      " Bitmap     
      "ship_to_item_key
      " Bitmap     
      "ship_to_loc_key
      " Bitmap     
      "ord_typ_key
      " Bitmap     
      "prod_key
      " Normal
      "timeframe_key, ship_to_opco_key, ship_from_loc_key, ship_to_loc_key
      " Normal
      "ship_from_item_key
      " Bitmap     
      ship_from_opco_key Bitmap     

      The SQL developer has written this query against simple views and not against the base tables?

      I have searched the forum for this error. While I did find a few threads, but nothing that will help me to resolve the issue.

      Can somebody please let me know if they see anything wrong with the setup? and how I should go about debugging the problem?

      Thanks in advance

      rogers
        • 1. Re: Need help to debug a SQL (ORA-01652: unable to extend temp segment by 1280)
          sb92075
          01652, 00000, "unable to extend temp segment by %s in tablespace %s"
          // *Cause:  Failed to allocate an extent of the required number of blocks for
          //          a temporary segment in the tablespace indicated.
          // *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
          //          files to the tablespace indicated.
          • 2. Re: Need help to debug a SQL (ORA-01652: unable to extend temp segment by 1280)
            rogers42
            Hi,

            Thanks for the reply.

            What you have proposed is the first suggestion I had encountered while researching the problem.

            My hesitation with the proposed solution is as follows

            As mentioned in my post, the temporary tablespace is 36,500 MB. According to the explain plan, the query should not take up more than 109MB. So, why is the query still failing? Will adding more temp space really solve the problem?

            If so, how much additional space should I ask the DBAs for?

            Thanks

            rogers
            • 3. Re: Need help to debug a SQL (ORA-01652: unable to extend temp segment by 1280)
              sb92075
              rogers42 wrote:
              Hi,

              Thanks for the reply.

              What you have proposed is the first suggestion I had encountered while researching the problem.

              My hesitation with the proposed solution is as follows

              As mentioned in my post, the temporary tablespace is 36,500 MB. According to the explain plan, the query should not take up more than 109MB. So, why is the query still failing? Will adding more temp space really solve the problem?

              If so, how much additional space should I ask the DBAs for?

              Thanks

              rogers
              Unfortuately, you have not really posted the whole & complete error message;
              which includes the actual name of the tablespace containing the problem.
              I am NOT convinced that problem is with the TEMP Tablespace.
              Oracle can & does create "temp" segments in Permanent Tablespaces.
              Realize & accept as fact that Oracle error message and code reliably & accurately report reality!
              • 4. Re: Need help to debug a SQL (ORA-01652: unable to extend temp segment by 1280)
                EdStevens
                rogers42 wrote:
                Hi Folks,

                I was recently assigned to a new project to investigate a query that runs for an hour (and sometimes more) before terminating with ORA-01652 error

                Environment Details:

                Database Version: 10.2.0.2
                Platform: AIX

                Problem Description:

                The query runs for 1+ hours before terminating with the following error message

                ORA-12801: error signaled in parallel query server P020
                ORA-01652: unable to extend temp segment by 1280 in tablespace TEMP


                Query:
                ALTER SESSION SET NLS_DATE_FORMAT= 'MM/DD/YYYY'
                
                select    a11.SHIP_FROM_LOC_KEY SHIP_FROM_LOC_KEY,
                a17.LOC_DESC LOC_DESC,
                a16.SHIP_TO_ITEM_DEPT SHIP_TO_ITEM_DEPT,
                a16.SHIP_TO_ITEM_DEPT_DSCR SHIP_TO_ITEM_DEPT_DSCR,
                a11.SHIP_TO_ITEM_KEY SHIP_TO_ITEM_KEY,
                a16.SHIP_TO_ITEM_DSCR SHIP_TO_ITEM_DSCR,
                a16.SHIP_TO_ITEM_NBR SHIP_TO_ITEM_NBR,
                a16.SHIP_TO_ITEM_NBR SHIP_TO_ITEM_NBR0,
                a16.SHIP_TO_ITEM_SIZE SHIP_TO_ITEM_SIZE,
                a16.SHIP_TO_STR_PACK SHIP_TO_STR_PACK,
                a13.CUSTOM_CM_LVL3_KEY CUSTOM_CM_LVL3_KEY,
                a13.CUSTOM_CM_LVL3_DESC CUSTOM_CM_LVL3_DESC,
                a13.CUSTOM_CM_LVL4_KEY CUSTOM_CM_LVL4_KEY,
                a13.CUSTOM_CM_LVL4_DESC CUSTOM_CM_LVL4_DESC,
                a13.CUSTOM_CM_LVL5_KEY CUSTOM_CM_LVL5_KEY,
                a13.CUSTOM_CM_LVL5_DESC CUSTOM_CM_LVL5_DESC,
                a12.LOC_HIER_LVL3_KEY LOC_HIER_LVL3_KEY,
                a12.LOC_HIER_LVL3_DESC LOC_HIER_LVL3_DESC,
                a15.FISC_YR_KEY FISC_YR_KEY,
                a15.FISC_YR_DESC FISC_YR_DESC,
                a15.WK_KEY WK_KEY,
                a15.WK_DESC WK_DESC,
                sum(a11.QTY_SHIPPED) INVENTORYCASESSHIPPE
                from    F_WHSE_SHPMT_VW    a11
                join    D_LOC_STORE_VW    a12
                on     (a11.SHIP_TO_LOC_KEY = a12.LOC_KEY)
                join    D_PROD_UPC_SS_VW    a13
                on     (a11.PROD_KEY = a13.PROD_KEY)
                join    D_SHIP_FROM_PRODUCT_PURCH_VW    a14
                on     (a11.SHIP_FROM_ITEM_KEY = a14.SHIP_FROM_ITEM_KEY)
                join    D_TF_DAY_VW    a15
                on     (a11.TIMEFRAME_KEY = a15.TIMEFRAME_KEY)
                join    D_SHIP_TO_PRODUCT_PURCH_VW    a16
                on     (a11.SHIP_TO_ITEM_KEY = a16.SHIP_TO_ITEM_KEY and 
                a11.SHIP_TO_OPCO_KEY = a16.SHIP_TO_OPCO_KEY and 
                a12.CHN_CD = a16.SHIP_TO_CHN_CD)
                join    D_LOC_WHSE_VW    a17
                on     (a11.SHIP_FROM_LOC_KEY = a17.LOC_KEY)
                where    (a11.SHIP_FROM_LOC_KEY in (15509, 16910, 16912)
                and a12.LOC_HIER_LVL3_KEY in (17256, 17257)
                and ((a14.SHIP_FROM_ITEM_DEPT = 5
                and a14.SHIP_FROM_ITEM_DEPT_DSCR in ('DELI', 'GM', 'GROCERY', 'MEAT', 'MEAT OTHER', 'SEAFOOD', 'FROZEN', 'FROZEN GROCERY'))
                or (a14.SHIP_FROM_ITEM_DEPT = 1
                and a14.SHIP_FROM_ITEM_DEPT_DSCR in ('DAIRY', 'DELI', 'GROCERY', 'GROCERY OTHER', 'MEAT', 'PRODUCE'))
                or (a14.SHIP_FROM_ITEM_DEPT = 16
                and a14.SHIP_FROM_ITEM_DEPT_DSCR in ('FLORAL', 'GROCERY', 'HBC', 'MEAT', 'PRODUCE'))
                or (a14.SHIP_FROM_ITEM_DEPT = 20
                and a14.SHIP_FROM_ITEM_DEPT_DSCR in ('DELI', 'MEAT', 'SALAD BAR 6/9/10', 'SALAD OTHER'))
                or (a14.SHIP_FROM_ITEM_DEPT = 31
                and a14.SHIP_FROM_ITEM_DEPT_DSCR in ('FLORAL', 'PRODUCE', 'SEAFOOD/FISH6/9 DPT21 623', 'FROZEN SEAFOOD'))
                or (a14.SHIP_FROM_ITEM_DEPT = 15
                and a14.SHIP_FROM_ITEM_DEPT_DSCR in ('DAIRY', 'DELI', 'GM'))
                or (a14.SHIP_FROM_ITEM_DEPT = 23
                and a14.SHIP_FROM_ITEM_DEPT_DSCR in ('DELI', 'PREPARED FOODS', 'SEAFOOD'))
                or (a14.SHIP_FROM_ITEM_DEPT = 30
                and a14.SHIP_FROM_ITEM_DEPT_DSCR in ('FLORAL', 'PRODUCE'))
                or (a14.SHIP_FROM_ITEM_DEPT = 21
                and a14.SHIP_FROM_ITEM_DEPT_DSCR in ('FRESH FISH', 'FROZEN MEAT'))
                or (a14.SHIP_FROM_ITEM_DEPT = 24
                and a14.SHIP_FROM_ITEM_DEPT_DSCR in ('PREPARED FOODS', 'FROZEN SEAFOOD'))
                or (a14.SHIP_FROM_ITEM_DEPT_DSCR = 'NATURAL FOODS'
                and a14.SHIP_FROM_ITEM_DEPT in (25, 28))
                or ((a14.SHIP_FROM_ITEM_DEPT = 10 and a14.SHIP_FROM_ITEM_DEPT_DSCR = 'DAIRY') 
                or (a14.SHIP_FROM_ITEM_DEPT=8 and a14.SHIP_FROM_ITEM_DEPT_DSCR='DAIRY OTHER') 
                or (a14.SHIP_FROM_ITEM_DEPT=40 and a14.SHIP_FROM_ITEM_DEPT_DSCR='DELI') 
                or (a14.SHIP_FROM_ITEM_DEPT=3 and a14.SHIP_FROM_ITEM_DEPT_DSCR='DELI OTHER') 
                or (a14.SHIP_FROM_ITEM_DEPT=7 and a14.SHIP_FROM_ITEM_DEPT_DSCR='FLORIST OTHER') 
                or (a14.SHIP_FROM_ITEM_DEPT=2 and a14.SHIP_FROM_ITEM_DEPT_DSCR='GM OTHER') 
                or (a14.SHIP_FROM_ITEM_DEPT=45 and a14.SHIP_FROM_ITEM_DEPT_DSCR='GROCERY') 
                or (a14.SHIP_FROM_ITEM_DEPT=9 and a14.SHIP_FROM_ITEM_DEPT_DSCR='HBC OTHER') 
                or (a14.SHIP_FROM_ITEM_DEPT=4 and a14.SHIP_FROM_ITEM_DEPT_DSCR='PRODUCE OTHER') 
                or (a14.SHIP_FROM_ITEM_DEPT=46 and a14.SHIP_FROM_ITEM_DEPT_DSCR='FROZEN BAKERY') 
                or (a14.SHIP_FROM_ITEM_DEPT=41 and a14.SHIP_FROM_ITEM_DEPT_DSCR='FROZEN DELI') 
                or (a14.SHIP_FROM_ITEM_DEPT=6 and a14.SHIP_FROM_ITEM_DEPT_DSCR='FROZEN FOOD')))
                and a15.DAY_DT > To_Date('12/31/2011', 'mm-dd-yyyy'))
                group by    a11.SHIP_FROM_LOC_KEY,
                a17.LOC_DESC,
                a16.SHIP_TO_ITEM_DEPT,
                a16.SHIP_TO_ITEM_DEPT_DSCR,
                a11.SHIP_TO_ITEM_KEY,
                a16.SHIP_TO_ITEM_DSCR,
                a16.SHIP_TO_ITEM_NBR,
                a16.SHIP_TO_ITEM_NBR,
                a16.SHIP_TO_ITEM_SIZE,
                a16.SHIP_TO_STR_PACK,
                a13.CUSTOM_CM_LVL3_KEY,
                a13.CUSTOM_CM_LVL3_DESC,
                a13.CUSTOM_CM_LVL4_KEY,
                a13.CUSTOM_CM_LVL4_DESC,
                a13.CUSTOM_CM_LVL5_KEY,
                a13.CUSTOM_CM_LVL5_DESC,
                a12.LOC_HIER_LVL3_KEY,
                a12.LOC_HIER_LVL3_DESC,
                a15.FISC_YR_KEY,
                a15.FISC_YR_DESC,
                a15.wk_key,
                a15.wk_desc;
                Explain Plan:
                Execution Plan
                ----------------------------------------------------------
                Plan hash value: 3110051182
                
                ---------------------------------------------------------------------------------------------------------------------------------------------------------------
                | Id  | Operation                              | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
                ---------------------------------------------------------------------------------------------------------------------------------------------------------------
                |   0 | SELECT STATEMENT                       |                 |   129K|    51M|       | 58775  (14)| 00:29:24 |       |       |        |      |            |
                |   1 |  PX COORDINATOR                        |                 |       |       |       |            |          |       |       |        |      |            |
                |   2 |   PX SEND QC (RANDOM)                  | :TQ10009        |   129K|    51M|       | 58775  (14)| 00:29:24 |       |       |  Q1,09 | P->S | QC (RAND)  |
                |   3 |    HASH GROUP BY                       |                 |   129K|    51M|   109M| 58775  (14)| 00:29:24 |       |       |  Q1,09 | PCWP |            |
                |   4 |     PX RECEIVE                         |                 |   129K|    51M|       | 58775  (14)| 00:29:24 |       |       |  Q1,09 | PCWP |            |
                |   5 |      PX SEND HASH                      | :TQ10008        |   129K|    51M|       | 58775  (14)| 00:29:24 |       |       |  Q1,08 | P->P | HASH       |
                |   6 |       HASH GROUP BY                    |                 |   129K|    51M|   109M| 58775  (14)| 00:29:24 |       |       |  Q1,08 | PCWP |            |
                |*  7 |        HASH JOIN                       |                 |   129K|    51M|       | 57201  (14)| 00:28:37 |       |       |  Q1,08 | PCWP |            |
                |   8 |         BUFFER SORT                    |                 |       |       |       |            |          |       |       |  Q1,08 | PCWC |            |
                |   9 |          PX RECEIVE                    |                 |   161K|  2369K|       |   765  (27)| 00:00:23 |       |       |  Q1,08 | PCWP |            |
                |  10 |           PX SEND BROADCAST            | :TQ10004        |   161K|  2369K|       |   765  (27)| 00:00:23 |       |       |        | S->P | BROADCAST  |
                |  11 |            PARTITION RANGE ALL         |                 |   161K|  2369K|       |   765  (27)| 00:00:23 |     1 |    11 |        |      |            |
                |* 12 |             TABLE ACCESS FULL          | D_PRODUCT_PURCH |   161K|  2369K|       |   765  (27)| 00:00:23 |     1 |    11 |        |      |            |
                |* 13 |         HASH JOIN                      |                 |   129K|    50M|       | 56435  (14)| 00:28:14 |       |       |  Q1,08 | PCWP |            |
                |  14 |          PX RECEIVE                    |                 |   129K|    28M|       | 56123  (14)| 00:28:04 |       |       |  Q1,08 | PCWP |            |
                |  15 |           PX SEND HASH                 | :TQ10006        |   129K|    28M|       | 56123  (14)| 00:28:04 |       |       |  Q1,06 | P->P | HASH       |
                |* 16 |            HASH JOIN                   |                 |   129K|    28M|       | 56123  (14)| 00:28:04 |       |       |  Q1,06 | PCWP |            |
                |  17 |             PX RECEIVE                 |                 |   460K|    69M|       | 55443  (14)| 00:27:44 |       |       |  Q1,06 | PCWP |            |
                |  18 |              PX SEND HASH              | :TQ10005        |   460K|    69M|       | 55443  (14)| 00:27:44 |       |       |  Q1,05 | P->P | HASH       |
                |* 19 |               HASH JOIN                |                 |   460K|    69M|       | 55443  (14)| 00:27:44 |       |       |  Q1,05 | PCWP |            |
                |  20 |                BUFFER SORT             |                 |       |       |       |            |          |       |       |  Q1,05 | PCWC |            |
                |  21 |                 PX RECEIVE             |                 |   491 | 14730 |       |     3   (0)| 00:00:01 |       |       |  Q1,05 | PCWP |            |
                |  22 |                  PX SEND BROADCAST     | :TQ10000        |   491 | 14730 |       |     3   (0)| 00:00:01 |       |       |        | S->P | BROADCAST  |
                |  23 |                   PARTITION RANGE ALL  |                 |   491 | 14730 |       |     3   (0)| 00:00:01 |     1 |    11 |        |      |            |
                |* 24 |                    TABLE ACCESS FULL   | D_LCTN          |   491 | 14730 |       |     3   (0)| 00:00:01 |     1 |    11 |        |      |            |
                |* 25 |                HASH JOIN               |                 |   942K|   115M|       | 55438  (14)| 00:27:44 |       |       |  Q1,05 | PCWP |            |
                |  26 |                 BUFFER SORT            |                 |       |       |       |            |          |       |       |  Q1,05 | PCWC |            |
                |  27 |                  PX RECEIVE            |                 |   409 | 22086 |       |     2   (0)| 00:00:01 |       |       |  Q1,05 | PCWP |            |
                |  28 |                   PX SEND BROADCAST    | :TQ10001        |   409 | 22086 |       |     2   (0)| 00:00:01 |       |       |        | S->P | BROADCAST  |
                |* 29 |                    TABLE ACCESS FULL   | D_DAY           |   409 | 22086 |       |     2   (0)| 00:00:01 |       |       |        |      |            |
                |* 30 |                 HASH JOIN              |                 |  1933K|   136M|       | 55434  (14)| 00:27:44 |       |       |  Q1,05 | PCWP |            |
                |  31 |                  BUFFER SORT           |                 |       |       |       |            |          |       |       |  Q1,05 | PCWC |            |
                |  32 |                   PX RECEIVE           |                 |     1 |    25 |       |     3   (0)| 00:00:01 |       |       |  Q1,05 | PCWP |            |
                |  33 |                    PX SEND BROADCAST   | :TQ10002        |     1 |    25 |       |     3   (0)| 00:00:01 |       |       |        | S->P | BROADCAST  |
                |  34 |                     PARTITION RANGE ALL|                 |     1 |    25 |       |     3   (0)| 00:00:01 |     1 |    11 |        |      |            |
                |* 35 |                      TABLE ACCESS FULL | D_LCTN          |     1 |    25 |       |     3   (0)| 00:00:01 |     1 |    11 |        |      |            |
                |  36 |                  PX BLOCK ITERATOR     |                 |   289M|    13G|       | 55106  (14)| 00:27:34 |     1 |     5 |  Q1,05 | PCWC |            |
                |* 37 |                   TABLE ACCESS FULL    | F_WHSE_SHPMT    |   289M|    13G|       | 55106  (14)| 00:27:34 |   KEY |   KEY |  Q1,05 | PCWP |            |
                |  38 |             BUFFER SORT                |                 |       |       |       |            |          |       |       |  Q1,06 | PCWC |            |
                |  39 |              PX RECEIVE                |                 |  1486K|   102M|       |   676  (18)| 00:00:21 |       |       |  Q1,06 | PCWP |            |
                |  40 |               PX SEND HASH             | :TQ10003        |  1486K|   102M|       |   676  (18)| 00:00:21 |       |       |        | S->P | HASH       |
                |  41 |                PARTITION RANGE ALL     |                 |  1486K|   102M|       |   676  (18)| 00:00:21 |     1 |    11 |        |      |            |
                |* 42 |                 TABLE ACCESS FULL      | D_PRODUCT_PURCH |  1486K|   102M|       |   676  (18)| 00:00:21 |     1 |    11 |        |      |            |
                |  43 |          PX RECEIVE                    |                 |   732K|   122M|       |   311   (2)| 00:00:10 |       |       |  Q1,08 | PCWP |            |
                |  44 |           PX SEND HASH                 | :TQ10007        |   732K|   122M|       |   311   (2)| 00:00:10 |       |       |  Q1,07 | P->P | HASH       |
                |  45 |            PX BLOCK ITERATOR           |                 |   732K|   122M|       |   311   (2)| 00:00:10 |    11 |    11 |  Q1,07 | PCWC |            |
                |* 46 |             TABLE ACCESS FULL          | D_PRODUCT_SS    |   732K|   122M|       |   311   (2)| 00:00:10 |    11 |    11 |  Q1,07 | PCWP |            |
                ---------------------------------------------------------------------------------------------------------------------------------------------------------------
                
                Predicate Information (identified by operation id):
                ---------------------------------------------------
                
                7 - access("SHIP_FROM_ITEM_KEY"="ITEM_KEY")
                12 - filter("ITEM_DEPT"=16 AND ("ITEM_DEPT_DSCR"='FLORAL' OR "ITEM_DEPT_DSCR"='GROCERY' OR "ITEM_DEPT_DSCR"='HBC' OR "ITEM_DEPT_DSCR"='MEAT' OR
                "ITEM_DEPT_DSCR"='PRODUCE') OR "ITEM_DEPT"=1 AND ("ITEM_DEPT_DSCR"='DAIRY' OR "ITEM_DEPT_DSCR"='DELI' OR "ITEM_DEPT_DSCR"='GROCERY' OR
                "ITEM_DEPT_DSCR"='GROCERY OTHER' OR "ITEM_DEPT_DSCR"='MEAT' OR "ITEM_DEPT_DSCR"='PRODUCE') OR "ITEM_DEPT"=15 AND ("ITEM_DEPT_DSCR"='DAIRY' OR
                "ITEM_DEPT_DSCR"='DELI' OR "ITEM_DEPT_DSCR"='GM') OR "ITEM_DEPT"=5 AND ("ITEM_DEPT_DSCR"='DELI' OR "ITEM_DEPT_DSCR"='FROZEN' OR
                "ITEM_DEPT_DSCR"='FROZEN GROCERY' OR "ITEM_DEPT_DSCR"='GM' OR "ITEM_DEPT_DSCR"='GROCERY' OR "ITEM_DEPT_DSCR"='MEAT' OR "ITEM_DEPT_DSCR"='MEAT OTHER'
                OR "ITEM_DEPT_DSCR"='SEAFOOD') OR "ITEM_DEPT"=2 AND "ITEM_DEPT_DSCR"='GM OTHER' OR "ITEM_DEPT"=3 AND "ITEM_DEPT_DSCR"='DELI OTHER' OR "ITEM_DEPT"=4
                AND "ITEM_DEPT_DSCR"='PRODUCE OTHER' OR "ITEM_DEPT"=6 AND "ITEM_DEPT_DSCR"='FROZEN FOOD' OR "ITEM_DEPT"=7 AND "ITEM_DEPT_DSCR"='FLORIST OTHER' OR
                "ITEM_DEPT"=8 AND "ITEM_DEPT_DSCR"='DAIRY OTHER' OR "ITEM_DEPT"=9 AND "ITEM_DEPT_DSCR"='HBC OTHER' OR "ITEM_DEPT"=10 AND "ITEM_DEPT_DSCR"='DAIRY' OR
                "ITEM_DEPT"=40 AND "ITEM_DEPT_DSCR"='DELI' OR "ITEM_DEPT"=41 AND "ITEM_DEPT_DSCR"='FROZEN DELI' OR "ITEM_DEPT"=45 AND "ITEM_DEPT_DSCR"='GROCERY' OR
                "ITEM_DEPT"=46 AND "ITEM_DEPT_DSCR"='FROZEN BAKERY' OR "ITEM_DEPT"=20 AND ("ITEM_DEPT_DSCR"='DELI' OR "ITEM_DEPT_DSCR"='MEAT' OR
                "ITEM_DEPT_DSCR"='SALAD BAR 6/9/10' OR "ITEM_DEPT_DSCR"='SALAD OTHER') OR "ITEM_DEPT"=30 AND ("ITEM_DEPT_DSCR"='FLORAL' OR "ITEM_DEPT_DSCR"='PRODUCE')
                OR "ITEM_DEPT"=31 AND ("ITEM_DEPT_DSCR"='FLORAL' OR "ITEM_DEPT_DSCR"='FROZEN SEAFOOD' OR "ITEM_DEPT_DSCR"='PRODUCE' OR
                "ITEM_DEPT_DSCR"='SEAFOOD/FISH6/9 DPT21 623') OR "ITEM_DEPT_DSCR"='NATURAL FOODS' AND ("ITEM_DEPT"=25 OR "ITEM_DEPT"=28) OR "ITEM_DEPT"=23 AND
                ("ITEM_DEPT_DSCR"='DELI' OR "ITEM_DEPT_DSCR"='PREPARED FOODS' OR "ITEM_DEPT_DSCR"='SEAFOOD') OR "ITEM_DEPT"=24 AND ("ITEM_DEPT_DSCR"='FROZEN SEAFOOD'
                OR "ITEM_DEPT_DSCR"='PREPARED FOODS') OR "ITEM_DEPT"=21 AND ("ITEM_DEPT_DSCR"='FRESH FISH' OR "ITEM_DEPT_DSCR"='FROZEN MEAT'))
                13 - access("PROD_KEY"="PROD_KEY")
                16 - access("SHIP_TO_ITEM_KEY"="ITEM_KEY" AND "SHIP_TO_OPCO_KEY"="OPCO_KEY" AND "CHN_CD"="CHN_CD")
                19 - access("SHIP_TO_LOC_KEY"="LOC_KEY")
                24 - filter("IA_DIM_LVL_DESC"='STORE' AND ("LOC_HIER_LVL3_KEY"=17256 OR "LOC_HIER_LVL3_KEY"=17257) AND ("CHN_CD"='010' OR "CHN_CD"='015' OR
                "CHN_CD"='020' OR "CHN_CD"='025' OR "CHN_CD"='035' OR "CHN_CD"='040' OR "CHN_CD"='045' OR "CHN_CD"='050'))
                25 - access("TIMEFRAME_KEY"="TIMEFRAME_KEY")
                29 - filter("DAY_DT">TO_DATE('2011-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
                30 - access("SHIP_FROM_LOC_KEY"="LOC_KEY")
                35 - filter("IA_DIM_LVL_DESC"='WHSE' AND ("LOC_KEY"=15509 OR "LOC_KEY"=16910 OR "LOC_KEY"=16912))
                37 - filter("SHIP_FROM_LOC_KEY"=15509 OR "SHIP_FROM_LOC_KEY"=16910 OR "SHIP_FROM_LOC_KEY"=16912)
                42 - filter("CHN_CD"='010' OR "CHN_CD"='015' OR "CHN_CD"='020' OR "CHN_CD"='025' OR "CHN_CD"='035' OR "CHN_CD"='040' OR "CHN_CD"='045' OR
                "CHN_CD"='050')
                46 - filter("IA_DIM_LVL_DESC"='UPC')
                Following are some additional details

                The maximum size for the temporary tablespace size is 36,500 Mbytes

                Based on the explain plan, the query should not occupy more than 109 MBytes of temporary tablespace

                The fact table has 1.2 billion rows. The table is partitioned. Stats are being collected on the partition level on daily basis. The new partitions have more recent stats, while the older partitions have not-so-up to date stats

                Following columns are indexed on the fact table

                *ship_from_loc_keyBitmap     
                ship_to_opco_key
                Bitmap     
                "invc_nbr
                " Bitmap     
                "ship_to_item_key
                " Bitmap     
                "ship_to_loc_key
                " Bitmap     
                "ord_typ_key
                " Bitmap     
                "prod_key
                " Normal
                "timeframe_key, ship_to_opco_key, ship_from_loc_key, ship_to_loc_key
                " Normal
                "ship_from_item_key
                " Bitmap     
                ship_from_opco_key Bitmap     

                The SQL developer has written this query against simple views and not against the base tables?

                I have searched the forum for this error. While I did find a few threads, but nothing that will help me to resolve the issue.

                Can somebody please let me know if they see anything wrong with the setup? and how I should go about debugging the problem?

                Thanks in advance

                rogers
                I had a similar problem a few months back. I had grown the temp TS to the point that it was fully 50% of my TOTAL disk allocation ... the temp TS was as large as the database the all of the "real" data TS's combined. Something was obviously amiss with the query.
                Look at your explain plan. You have several steps that are each returning over 100 million rows. Does that even make sense, given your data? Unfortunately, tuning SQL is not my strong suit, but given what I see in your post, I'd say that's what you need to be focusing on. Are any of the 'tables' in your query really views that expand out into large, complex queries themselves? And views that cover a db-link? Off-hand I don't see a Cartesian join, but you might take a closer look ... perhaps your query references a view that itself has a CJ.

                And you might be better taking the problem to the SQL-PL/SQL forum, or even a non-OTN forum that specializes in query optimization.
                • 5. Re: Need help to debug a SQL (ORA-01652: unable to extend temp segment by 1280)
                  rogers42
                  Hi,

                  Couple of comments

                  1, I have requested the system DBA to send me the alert.log file. I will scan the logs just to make sure if the error message is being reported in its entirety.

                  2, Yes, there is a lot of data in some of the tables (e.g. the F_WHSE_SHPMT table has 1.2 billion rows)

                  As an experiment, I had decided to compare the actual number of rows returned from each view / base table (with all the filters applied) vs what the explain plan is reporting.

                  Following are the details

                  Object Name,              Number of rows returned by the query,                              Number of rows according to the explain plan
                  F_WHSE_SHPMT, 614M,     289M
                  D_PRODUCT_PURCH, 1,582,649,     1,486,000

                  Observations:

                  The row count for all other tables referenced in the query agree with explain plan's output
                  The above two tables have the most discrepancies
                  Coincidentally, these are the only tables that are partitioned

                  Q, Any ideas if this might be the reason for the temporary space filling up? i.e. based on the explain plan, no more than 109MBs of temporary tablespace will be used. But based on the above row counts the temporary space requirements will be much higher?

                  Q, Is it worth asking the DBA to manually refresh stats on the partition tables?


                  Thanks

                  rogers
                  • 6. Re: Need help to debug a SQL (ORA-01652: unable to extend temp segment by 1280)
                    EdStevens
                    rogers42 wrote:
                    Hi,

                    Couple of comments

                    1, I have requested the system DBA to send me the alert.log file. I will scan the logs just to make sure if the error message is being reported in its entirety.

                    2, Yes, there is a lot of data in some of the tables (e.g. the F_WHSE_SHPMT table has 1.2 billion rows)

                    As an experiment, I had decided to compare the actual number of rows returned from each view / base table (with all the filters applied) vs what the explain plan is reporting.

                    Following are the details

                    Object Name,              Number of rows returned by the query,                              Number of rows according to the explain plan
                    F_WHSE_SHPMT, 614M,     289M
                    D_PRODUCT_PURCH, 1,582,649,     1,486,000

                    Observations:

                    The row count for all other tables referenced in the query agree with explain plan's output
                    The above two tables have the most discrepancies
                    Coincidentally, these are the only tables that are partitioned

                    Q, Any ideas if this might be the reason for the temporary space filling up? i.e. based on the explain plan, no more than 109MBs of temporary tablespace will be used.
                    How did you arrive at that 109mb figure? I see ... for just two steps:
                    ---------------------------------------------------------------------------------------------------------------------------------------------------------------
                    | Id  | Operation                              | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
                    ---------------------------------------------------------------------------------------------------------------------------------------------------------------
                    
                    |  36 |                  PX BLOCK ITERATOR     |                 |   289M|    13G|       | 55106  (14)| 00:27:34 |     1 |     5 |  Q1,05 | PCWC |            |
                    |* 37 |                   TABLE ACCESS FULL    | F_WHSE_SHPMT    |   289M|    13G|       | 55106  (14)| 00:27:34 |   KEY |   KEY |  Q1,05 | PCWP |            |
                    Doesn't that say 289 million rows and 13 gigabytes of data?
                    But based on the above row counts the temporary space requirements will be much higher?

                    Q, Is it worth asking the DBA to manually refresh stats on the partition tables?


                    Thanks

                    rogers
                    1 person found this helpful
                    • 7. Re: Need help to debug a SQL (ORA-01652: unable to extend temp segment by 1280)
                      rogers42
                      Hi,

                      Correct me if I am wrong, but does the blank TempSpc column( adjacent to the 289M rows) not mean the PGA is large enough to store the data (and temporary space is not required at this stage)?

                      Secondly, is the row or memory usage column cumulative?

                      Thanks

                      rogers
                      • 8. Re: Need help to debug a SQL (ORA-01652: unable to extend temp segment by 1280)
                        EdStevens
                        rogers42 wrote:
                        Hi,

                        Correct me if I am wrong, but does the blank TempSpc column( adjacent to the 289M rows) not mean the PGA is large enough to store the data (and temporary space is not required at this stage)?
                        you could be right on that
                        Secondly, is the row or memory usage column cumulative?
                        Not sure ... as I said going in, this isn't my strong suit, but your situation looked an awful lot like one I had to address. Not being my strong suit, we never really 'solved' it. Rather, since it was a once-a-month job, we worked around it by creating an additional table to match one of the big subquery views, and pre-loaded it.
                        Thanks

                        rogers
                        • 9. Re: Need help to debug a SQL (ORA-01652: unable to extend temp segment by 1280)
                          mtefft
                          Some observations:
                          - As a general rule, if you have a query running out of TEMP in an established environment, my first suspicion falls upon the query. Missing join predicates can cause the intermediate result sets to explode. I see you have many views there - you should inspect those as well.
                          - Despite the indexes on the fact table, none of them are being used.
                          - I suggest you consider bitmap join indexes: http://docs.oracle.com/cd/E11882_01/server.112/e25554/indexes.htm#autoId6.
                          - This design may be the product of a star-schema zealot: it is done by the book, so it must be correct. It's someone else's job to make it perform. (As a test, see what happens if you suggest adding an actual DATE column like DAY_DT to the fact table. Are the discussions about space, and whether this is actually now a problem, or are they about design purity?)

                          Questions:
                          - Is there a reason why D_PRODUCT_PURCH appears twice in your plan? (observation 1 above)
                          - What is the partitioning column in your fact table?
                          - I assume your fact-table indexes are all local - correct?
                          - I see that some of your dimension tables are partitioned. What is their partitioning strategy? Do they fit with the fact-table partitioning? Are the joins between the fact and dimension tables supposed to include the partitioning key? (observation 1 above)

                          Mike
                          1 person found this helpful