Oracle Database Discussions

Hi,

My procedure give me following error at particular insert statement on ORACLE 10g 10.2.0.30.0 db but it works fine on
ORACLE 9i 9.2.0.8.0 db.
Temporary Tablespace TEMPTBS2 size is same on ORACLE 10G and ORACLE 9i.

ORA-12801: error signaled in parallel query server P017
ORA-01652: unable to extend temp segment by 64 in tablespace TEMPTBS2
ORA-06512: at line 1

Here is my query which gives me error and Explain Plan.

Please guide me to resolve the error
i hv to increase the tablespace size or tune the query for oracle 10g.


INSERT /*+ APPEND PARALLEL(MFIDATA.TEMP_PROMO_CALC0_0,4) */INTO mfidata.temp_promo_calc0_0 NOLOGGING
(SELECT eve.ctry_code, eve.co_code, cust.cust_nbr, cust.cust_nm,
cust.rt_nbr, eve.item_id, eve.event_nbr, AUTO.pkg_nbr,
AUTO.pkg_short_descp, eve.event_short_descp, AUTO.promo_seq_nbr,
eve.event_allow_pct, eve.event_allow_amt, cha.lvl_1_node_nbr,
cha.lvl_2_node_nbr, cha.lvl_3_node_nbr, cha.lvl_4_node_nbr,
cha.lvl_5_node_nbr, cha.lvl_6_node_nbr, loc.targt_list_nbr,
loc.lock_in_eff_dt, prod.hhc_code, loc.lock_in_end_dt,
AUTO.grp_nbr, rtaut.base_sdv_amt, prod.prod_descp_txt,
cust.lvl1_node_nbr geo_lvl_1, cust.lvl2_node_nbr geo_lvl_2,
cust.lvl3_node_nbr geo_lvl_3, cust.lvl4_node_nbr geo_lvl_4
FROM mfidata.autogrant AUTO,
mfidata.event eve,
mfidata.cust_perf cust,
cusl.chain_struc cha,
gpm.lock_in loc,
mfidata.rt_auth_perf rtaut,
mfidata.prod_perf_xref prod,
gpm.grp_event gev
WHERE rtaut.ctry_code = eve.ctry_code
AND rtaut.co_code = eve.co_code
AND rtaut.item_id = eve.item_id
AND eve.event_type_code = 1
AND rate_type_code IN (1, 2)
AND TRUNC (SYSDATE) BETWEEN rtaut.rt_auth_eff_dt AND rtaut.rt_auth_end_dt
AND TRUNC (SYSDATE) BETWEEN parmr_list_eff_dt AND prod_list_end_dt
AND AUTO.ctry_code = eve.ctry_code
AND AUTO.co_code = eve.co_code
AND AUTO.event_nbr = eve.event_nbr
AND TRUNC (SYSDATE) BETWEEN AUTO.lock_in_eff_dt AND AUTO.lock_in_end_dt
AND AUTO.ctry_code = cust.ctry_code
AND AUTO.co_code = cust.co_code
AND AUTO.cust_nbr = cust.cust_nbr
AND rtaut.rt_nbr = cust.rt_nbr
AND TRUNC (SYSDATE) BETWEEN cust.eff_dt AND cust.end_dt
AND AUTO.ctry_code = loc.ctry_code
AND AUTO.co_code = loc.co_code
AND AUTO.pkg_nbr = loc.pkg_nbr
AND AUTO.grp_nbr = loc.grp_nbr
AND AUTO.event_nbr = loc.event_nbr
AND TRUNC (SYSDATE) BETWEEN loc.lock_in_eff_dt AND loc.lock_in_end_dt
AND cha.ctry_code(+) = cust.ctry_code
AND cha.co_code(+) = cust.co_code
AND cha.chain_node_sys_nbr(+) = cust.chain_node_sys_nbr
AND TRUNC (SYSDATE) BETWEEN NVL (cha.chain_node_eff_dt,
TRUNC (SYSDATE))
AND NVL (cha.chain_node_end_dt,
TRUNC (SYSDATE))
AND rtaut.ctry_code = prod.ctry_code
AND rtaut.co_code = prod.co_code
AND rtaut.item_id = prod.item_id
AND TRUNC (SYSDATE) BETWEEN xref_eff_dt AND xref_end_dt
AND gev.appv_flg = 'A'
AND ( (gev.appv_flg = 'A')
OR ( (gev.appv_flg = 'D' AND gev.update_code = 'D')
AND ( gev.grp_event_eff_dt ^= gev.grp_event_end_dt
AND gev.grp_event_lead_da > 0
)
)
OR (gev.appv_flg = 'D' AND gev.update_code = 'R')
)
AND ( (loc.logic_delte_flg = '0')
OR ( loc.logic_delte_flg IN ('1', '3')
AND loc.lock_in_eff_dt <> loc.lock_in_end_dt
AND loc.lock_in_updte_dt > loc.lock_in_eff_dt
)
)
AND loc.ctry_code = gev.ctry_code
AND loc.co_code = gev.co_code
AND loc.pkg_nbr = gev.pkg_nbr
AND loc.grp_nbr = gev.grp_nbr
AND loc.event_nbr = gev.event_nbr
AND loc.grp_event_rev_nbr = gev.grp_event_rev_nbr
AND loc.auto_grant_flg = '1'
AND loc.targt_list_type = 'C')



Access plan for ORACLE 10G
==================
INSERT STATEMENT CHOOSE
Cost: 85,899 Bytes: 3.354.054 Cardinality: 6,281
17 LOAD AS SELECT MFIDATA.TEMP_PROMO_CALC0_0
16 FILTER
15 HASH JOIN RIGHT OUTER Cost: 85,899 Bytes: 3.354.054 Cardinality: 6,281
1 TABLE ACCESS FULL TABLE CUSL.CHAIN_STRUC Cost: 33 Bytes: 589,713 Cardinality: 11,563
14 HASH JOIN Cost: 85,865 Bytes: 3.033.723 Cardinality: 6,281
2 TABLE ACCESS FULL TABLE MFIDATA.PROD_PERF_XREF_T Cost: 590 Bytes: 505,02 Cardinality: 6,645
13 HASH JOIN Cost: 85,273 Bytes: 9.781.838 Cardinality: 24,034
11 HASH JOIN Cost: 35,275 Bytes: 81.570.996 Cardinality: 232,396
9 HASH JOIN Cost: 32,335 Bytes: 3.832.262 Cardinality: 14,407
7 HASH JOIN Cost: 20,457 Bytes: 3.257.208 Cardinality: 19,048
5 HASH JOIN Cost: 3,342 Bytes: 2.728.546 Cardinality: 25,741
3 TABLE ACCESS FULL TABLE GPM.LOCK_IN Cost: 1,565 Bytes: 2.751.760 Cardinality: 46,64
4 TABLE ACCESS FULL TABLE GPM.GRP_EVENT Cost: 1,196 Bytes: 8.133.397 Cardinality: 173,051
6 TABLE ACCESS FULL TABLE MFIDATA.AUTOGRANT_T Cost: 9,912 Bytes: 140.312.770 Cardinality: 2.158.658
8 TABLE ACCESS FULL TABLE MFIDATA.CUST_PERF_T Cost: 8,485 Bytes: 68.465.170 Cardinality: 720,686
10 TABLE ACCESS FULL TABLE MFIDATA.EVENT_T Cost: 1,609 Bytes: 24.153.600 Cardinality: 284,16
12 TABLE ACCESS FULL TABLE MFIDATA.RT_AUTH_PERF_T Cost: 30,595 Bytes: 306.671.736 Cardinality: 5.476.281

==============================
SAME QUERY ON ORACLE 9i's Explain Plan
===============================

Plan
INSERT STATEMENT CHOOSECost: 2,876 Bytes: 534 Cardinality: 1
23 LOAD AS SELECT
22 TABLE ACCESS BY INDEX ROWID MFIDATA.PROD_PERF_XREF_T Cost: 2 Bytes: 76 Cardinality: 1
21 NESTED LOOPS Cost: 2,876 Bytes: 534 Cardinality: 1
19 FILTER
18 NESTED LOOPS OUTER
15 NESTED LOOPS Cost: 2,873 Bytes: 407 Cardinality: 1
12 NESTED LOOPS Cost: 2,858 Bytes: 1,755 Cardinality: 5
9 NESTED LOOPS Cost: 2,825 Bytes: 798 Cardinality: 3
6 NESTED LOOPS Cost: 2,810 Bytes: 855 Cardinality: 5
3 HASH JOIN Cost: 2,776 Bytes: 2,108 Cardinality: 17
1 TABLE ACCESS FULL MFIDATA.AUTOGRANT_T Cost: 2,163 Bytes: 7,742,995 Cardinality: 119,123
2 TABLE ACCESS FULL GPM.LOCK_IN Cost: 365 Bytes: 9,123,288 Cardinality: 154,632
5 TABLE ACCESS BY INDEX ROWID GPM.GRP_EVENT Cost: 2 Bytes: 47 Cardinality: 1
4 INDEX UNIQUE SCAN UNIQUE GPM.PKGRP_EVENT Cost: 1 Cardinality: 1
8 TABLE ACCESS BY INDEX ROWID MFIDATA.CUST_PERF_T Cost: 3 Bytes: 95 Cardinality: 1
7 INDEX RANGE SCAN UNIQUE MFIDATA.XPKCUST_PERF_T Cost: 2 Cardinality: 1
11 TABLE ACCESS BY INDEX ROWID MFIDATA.EVENT_T Cost: 11 Bytes: 170 Cardinality: 2
10 INDEX RANGE SCAN UNIQUE MFIDATA.XPKEVENT_T Cost: 2 Cardinality: 16
14 TABLE ACCESS BY INDEX ROWID MFIDATA.RT_AUTH_PERF_T Cost: 3 Bytes: 56 Cardinality: 1
13 INDEX RANGE SCAN NON-UNIQUE DRP.BJ_RT_AUTH_PERF_T_IDX1 Cost: 2 Cardinality: 1
17 TABLE ACCESS BY INDEX ROWID CUSL.CHAIN_STRUC Cost: 1 Bytes: 51 Cardinality: 1
16 INDEX UNIQUE SCAN UNIQUE CUSL.CUSL_CHAINSTRUC_PK Cardinality: 1
20 INDEX RANGE SCAN UNIQUE MFIDATA.IND_PROD_PERF_XREF_INDEX1 Cost: 1 Cardinality: 1


Thanks.