Oracle Database Discussions

Hello,

Can somebody please share your suggestion regarding the temporary tablespace keep getting filled up.

I tried to keep on increasing the temp tablespace to fit the sorts for the query and i end up increasing temp tablespace to 60 GB and the query still fails "unable to extent temp tablespace".

I looked at the query which was using the temp tablespace, looked at its explain plan too everything looks good to me but still not able to figure it out what is using this much temp space.

Queried v$sort_usage and just for your info SEGTYPE shown was HASH for that particular session.

Here's the query and its explain plan.

SQL> explain plan for
2 SELECT a.pol_nbr AS "Policy Number",
3 a.pol_orig_incpn_dt_nbr AS "Inception Date",
4 b.pltm_trm_eff_dt_nbr AS "Term Effective Date",
5 c.drv_nbr AS "Driver Number",
6 d.drv_lic_iss_st_cd AS "License Issue St", e.st_cd AS "Mail St Cd",
7 f.grg_st_cd AS "Garaging St Cd",
8 g.pltm_tr_var_tier_dt_nbr AS "Tier Dt",
9 g.cr_scr_actn_cd AS "Credit Action Code",
10 g.fru_rept_entps_srvc_id AS "FRU Id"
11 FROM wpl_pol_s a,
12 wpl_pltm_dtl_s b,
13 wpl_pltm_drdtl_s c,
14 wpl_pltm_drv_drv_lic_s d,
15 wpl_cust_addr_s e,
16 wpl_polveh_locn_s f,
17 wpl_pltm_tr_var_s g,
18 wpl_fru_rept h
19 WHERE a.dbid_cd = '22'
20 AND a.rt_st_cd = 'WA'
21 AND a.dbid_cd = b.dbid_cd
22 AND a.cust_nbr = b.cust_nbr
23 AND a.pol_nbr = b.pol_nbr
24 AND c.reltn_to_ni_cd = 'I'
25 AND a.dbid_cd = c.dbid_cd
26 AND a.cust_nbr = c.cust_nbr
27 AND c.dbid_cd = d.dbid_cd
28 AND c.drv_sgmt_typ_cd = d.drv_sgmt_typ_cd
29 AND c.isn = d.isn
30 AND a.dbid_cd = e.dbid_cd
31 AND a.cust_nbr = e.cust_nbr
32 AND a.dbid_cd = f.dbid_cd
33 AND a.cust_nbr = f.cust_nbr
34 AND a.pol_nbr = f.pol_nbr
35 AND a.dbid_cd = g.dbid_cd
36 AND a.cust_nbr = g.cust_nbr
37 AND a.pol_nbr = g.pol_nbr
38 AND UPPER (g.fru_rept_entps_srvc_id) = UPPER (h.fru_rept_entps_srvc_id)
39 AND ( h.fru_rept_frst_fctr = '056'
40 OR h.fru_rept_scnd_fctr = '056'
41 OR h.fru_rept_thrd_fctr = '056'
42 OR h.fru_rept_4th_fctr = '056'
43 )
44 AND ROWNUM < 1000;

Explained.

SQL> @C:\oracle\product\10.2.0\client_1\RDBMS\ADMIN\utlxpls.sql

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 298 | 4376 | | |
|* 1 | COUNT STOPKEY | | | | | | |
|* 2 | HASH JOIN | | 1 | 298 | 4376 | | |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID | WPL_CUST_ADDR_S | 3 | 54 | 1 | | |
| 4 | NESTED LOOPS | | 1 | 253 | 900 | | |
| 5 | NESTED LOOPS | | 1 | 235 | 899 | | |
| 6 | NESTED LOOPS | | 1 | 216 | 898 | | |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 7 | NESTED LOOPS | | 1 | 183 | 897 | | |
| 8 | NESTED LOOPS | | 1 | 151 | 895 | | |
| 9 | NESTED LOOPS | | 1 | 79 | 894 | | |
|* 10 | TABLE ACCESS BY LOCAL INDEX ROWID| WPL_POL_S | 4907 | 215K| 403 | 4 | 4 |
|* 11 | INDEX RANGE SCAN | WPL_POL_S_IX01 | 490K| | 546 | 4 | 4 |
|* 12 | INDEX RANGE SCAN | WPL_PLTM_DTL_S_PK | 1 | 34 | 1 | 4 | 4 |
| 13 | TABLE ACCESS BY LOCAL INDEX ROWID | WPL_PLTM_TR_VAR_S | 1 | 72 | 1 | 4 |
|* 14 | INDEX RANGE SCAN | WPL_PLTM_TR_VAR_S_PK | 1 | | 1 | 4 | 4 |
| 15 | PARTITION HASH ITERATOR | | | | | KEY | KEY |
|* 16 | TABLE ACCESS BY LOCAL INDEX ROWID | WPL_POLVEH_LOCN_S | 1 | 32 | 2 | KEY |
|* 17 | INDEX RANGE SCAN | WPL_POLVEH_LOCN_S_PK | 36 | | 1 | KEY | KEY |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 18 | TABLE ACCESS BY LOCAL INDEX ROWID | WPL_PLTM_DRDTL_S | 1 | 33 | 1 |
|* 19 | INDEX RANGE SCAN | WPL_PLTM_DRDTL_S_PK | 8 | | 1 | 4 | 4 |
| 20 | TABLE ACCESS BY LOCAL INDEX ROWID | WPL_PLTM_DRV_DRV_LIC_S | 1 | 19 | 1
|* 21 | INDEX UNIQUE SCAN | WPL_PLTM_DRV_DRV_LIC_S_PK | 1 | | | 4 | 4 |
| 22 | PARTITION HASH ITERATOR | | | | | KEY | KEY |
|* 23 | INDEX RANGE SCAN | WPL_CUST_ADDR_S_PK | 3 | | 1 | KEY | KEY |
|* 24 | TABLE ACCESS FULL | WPL_FRU_REPT | 91317 | 4012K| 3475 | | |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

1 - filter(ROWNUM<1000)
2 - access(UPPER("G"."FRU_REPT_ENTPS_SRVC_ID")=UPPER("H"."FRU_REPT_ENTPS_SRVC_ID"))
10 - filter("A"."RT_ST_CD"='WA')
11 - access("A"."DBID_CD"='22')
12 - access("B"."DBID_CD"='22' AND "A"."CUST_NBR"="B"."CUST_NBR" AND "A"."POL_NBR"="B"."POL_NBR")
14 - access("G"."DBID_CD"='22' AND "A"."CUST_NBR"="G"."CUST_NBR" AND "A"."POL_NBR"="G"."POL_NBR")
16 - filter("A"."POL_NBR"="F"."POL_NBR")
17 - access("F"."DBID_CD"='22' AND "A"."CUST_NBR"="F"."CUST_NBR")
18 - filter("C"."RELTN_TO_NI_CD"='I')
19 - access("C"."DBID_CD"='22' AND "A"."CUST_NBR"="C"."CUST_NBR")

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
21 - access("D"."DBID_CD"='22' AND "C"."DRV_SGMT_TYP_CD"="D"."DRV_SGMT_TYP_CD" AND "C"."ISN"="D"."
23 - access("E"."DBID_CD"='22' AND "A"."CUST_NBR"="E"."CUST_NBR")
24 - filter("H"."FRU_REPT_FRST_FCTR"='056' OR "H"."FRU_REPT_SCND_FCTR"='056' OR "H"."FRU_REPT_THRD
"H"."FRU_REPT_4TH_FCTR"='056')

Note: cpu costing is off

50 rows selected.

SQL>

Oracle 9i, Sun Solaris.

Any help will be great.

Thanks -