Forum Stats

  • 3,751,821 Users
  • 2,250,415 Discussions
  • 7,867,599 Comments

Discussions

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

rogers42
rogers42 Member Posts: 76
edited Feb 23, 2013 6:41AM in General Database Discussions
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

Answers

  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    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.
  • rogers42
    rogers42 Member Posts: 76
    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
  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    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!
  • EdStevens
    EdStevens Member Posts: 28,402 Gold Crown
    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.
  • rogers42
    rogers42 Member Posts: 76
    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
  • EdStevens
    EdStevens Member Posts: 28,402 Gold Crown
    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
    EdStevens
  • rogers42
    rogers42 Member Posts: 76
    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
  • EdStevens
    EdStevens Member Posts: 28,402 Gold Crown
    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
  • mtefft
    mtefft Member Posts: 833 Gold Badge
    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
    mtefft
This discussion has been closed.