Discussions
Categories
- 195.9K All Categories
- 2.1K Data
- 208 Big Data Appliance
- 1.9K Data Science
- 447.2K Databases
- 220.7K General Database Discussions
- 3.7K Java and JavaScript in the Database
- 23 Multilingual Engine
- 516 MySQL Community Space
- 463 NoSQL Database
- 7.7K Oracle Database Express Edition (XE)
- 2.9K ORDS, SODA & JSON in the Database
- 461 SQLcl
- 3.9K SQL Developer Data Modeler
- 185.8K SQL & PL/SQL
- 20.9K SQL Developer
- 292.4K Development
- 7 Developer Projects
- 120 Programming Languages
- 289.1K Development Tools
- 94 DevOps
- 3K QA/Testing
- 645.4K Java
- 21 Java Learning Subscription
- 36.9K Database Connectivity
- 150 Java Community Process
- 104 Java 25
- 22.1K Java APIs
- 137.8K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 14 Java Essentials
- 142 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 197 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 219 LiveLabs
- 34 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.8K Other Languages
- 2.3K Chinese
- 166 Deutsche Oracle Community
- 1.2K Español
- 1.9K Japanese
- 225 Portuguese
Need help to debug a SQL (ORA-01652: unable to extend temp segment by 1280)
rogers42
Member Posts: 76
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:
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 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
Tagged:
Answers
-
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.
-
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 -
rogers42 wrote:Unfortuately, you have not really posted the whole & complete error message;
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
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! -
rogers42 wrote: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.
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
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. -
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 -
rogers42 wrote:How did you arrive at that 109mb figure? I see ... for just two steps:
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.--------------------------------------------------------------------------------------------------------------------------------------------------------------- | 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 -
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 -
rogers42 wrote:you could be right on that
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?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 -
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
This discussion has been closed.