You Asked

Hi Team,

We have facing the issue with 1 query is consuming 331GB of TEMP tablespace. WE have configured 340GB of TEMP TS and have some limitation to add more temp files on DB server. due to it the other session get starved with TEMP tablespace and getting this error "ORA-01652: Unable to extend temp segment by 2048 in table space TEMP"

The query is having right outer join with 2 global temp tables and insert into another GTT (Global Temp Table).

QRY:

INSERT INTO GTT3 
SELECT /*+ merge */ P.N1,P.N2,C.* 
FROM GTT1 P, GTT2 C 
WHERE P.ID(+) = C.CSID 
AND P.PID(+) = C.CEID 
AND P.RID(+) = C.RID;

Explan plan
-------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |                            |  8168 |  8997K|     8   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | GTT3         |       |       |            |
|*  2 |   HASH JOIN RIGHT OUTER  |                            |  8168 |  8997K|     8   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | GTT1         |  8168 |  1124K|     4   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL     | GTT2         |  8168 |  7872K|     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Any suggestions to improve the query .

All GTT contains millions of records.

and Chris said...

Global temporary tables store data in the temporary tablespace.

So you're taking data from the temporary tablespace and inserting it back there. While (almost certainly) also storing the hash table for the join there too.

For millions of rows.

No wonder you're blowing out your temp tablespace!

So the real question is:

Why are you using temp tables? Why stage it the results to a third temp table? Can't you go to the final, permanent table?

PS I suspect you've misunderstood the point of the merge hint. This instructs the optimizer to merge views. Not do a merge join:

https://docs.oracle.com/database/121/SQLRF/sql_elements006.htm#BABHIFDA

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Ishan Joshi, March 06, 2018 - 2:26 pm UTC

Thanks Chris for prompt Response.

We have mechanism to store the data in global temp table and this is the final insertion for GTT table. This mechanism was working fine for 2 yrs but it is getting issue from last 1 month onward.

1. can we use the nested loop for join as hash join will utilize memory and temp tablespace?

2. gather stats would help on GTT?

for merge hint, it is old code and we can neglect/remove the hint from the query as it is not impacting much to the execution plan if I remove the hint.

March 06, 2018 - 5:52 pm UTC

Rather than twiddling with the plan for this query, rethink the whole process.

Why are you staging the data in two GTTs before inserting to a third? Can you eliminate any of these GTTs? Why are you even using these at all?

divide and conquer

Racer I., March 07, 2018 - 10:55 am UTC

Hi,

guessing :
- the result should tell which N1 goes with which N2
- the current triple-join is hopefully unique
- the gtt2-table is quite big, so not so good to duplicate fully

so just insert N1/N2 (no c.*) combinations into gtt3 and join gtt2 and gtt3 (ON N2) again for the presumed fourth step.

or do everything in one SQL (maybe using WITH for good readability) as Chris suggested.

regards,

March 07, 2018 - 11:26 am UTC

Yep, breaking it up could help if this is possible.

Why not

Gh, March 07, 2018 - 6:36 pm UTC

Gtt3 is a specific copy of gtt2 within a N2 null from gtt1 when doesn't matches .

So a simple force view suffice for the need
Create or replace view gtt3 as ..

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database

Breadcrumb

Question and Answer