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