ORA-1652: unable to extend temp segment

There could be only two ways to resolve this error ..

I. Add More Space in Temporary Table-space temp-file
II. Identify the Problematic Query & Tune it !

Today i came across this error , a developer reach out to me saying a package is executing for almost 30 minutes & terminating with "ORA-1652: unable to extend temp segment by 128 in tablespace TEMP" error

My first obvious Question to developer "Did You identified Query causing huge Sorting in you package ?" & Answer that i received was "No, Its wrapped package we don't even see what is inside it only vendor can access it"

Only option remain was to enable trace & identify problematic query.
I used a simple session level trace by executing "alter system sql_trace=TRUE;"
While monitoring the session using below query i captured the problematic sql text before analyzing the trace itself showing wait event "direct path write temp"

col sql_text for a80
set pages 200
set line 900
col PROGRAM for a20
col MACHINE for a20
col CPU 9999999999
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
col event for a40
select *
  from (select /*+ rule */  sa.sql_id, sa.CPU_TIME "CPU",  s.sid "SID",  s.serial# "SERIAL",
         s.program "PROGRAM",  s.machine "MACHINE",  sa.SQL_TEXT "SQL_TEXT",
         vp.spid,  sw.event, s.logon_time, s.last_call_et / 60,  s.username,  s.status
          from v$sqlarea sa, v$session s, v$process vp, v$session_wait sw
         where sa.address = s.sql_address  and sw.sid = s.sid  and s.paddr = vp.addr
         order by CPU_TIME desc)
 where rownum < 10;

Query was ..

with execution plan ..

After spending an unsuccessful hour by playing with indexes to improve the cost of joins i saw a hint /*+ ordered */  in select query

Just removed the ordered hint & re-executed the query

And the execution plan was ..

Temp Space estimate has disappeared & cost of the query came down from 874K to 4K.

The package got successfully executed without error ORA-1652 

ordered hint was the culprit forcing optimizer to use the join conditions in the same order as written rather than allowing optimizer to choose its own optimal execution plan

No comments:

Post a Comment