CODEWITHSUNDEEP
oracle-databaseoracle12cdatabase-administration
Pavan Kumar K
Pavan Kumar K

Reputation: 1376

How to resolve temp table space issue in Oracle

I need help from DBAs here. I have a query that fetches around 1800 records from DB. However, it is observed that oracle's temp table space is getting filled up and makes oracle to respond too slow.

I have identified the query that is causing the issue and the query is something like this.

SELECT * FROM A a, B b WHERE a.id = b.fieldId AND b.col1 = :1 AND b.col2 = :2 ORDER BY TO_NUMBER(b.col3) ASC

This is query is returning around 1800 records and DBA segments show that 44 GB out of 50 GB of data is occupied. I am not sure what could be solution for this.

I am using Oracle 12.1

Please look into this and suggest if i have to rewrite the query. Thanks in Advance.

Upvotes: 1

Views: 1856

Answers (1)

ekochergin
ekochergin

Reputation: 4129

It is hard to how resource intensive query is without checking query plan at least.

This might be not your query at all who "ate" all the TEMP space. Here is how to get top 20 session with highest TEMP usage.

select round(u.blocks*8192/1024/1024,2) "TEMP usage, Mb",
       s.sid, s.osuser, s.machine, s.module, s.action, s.status, s.event, s.LAST_CALL_ET, s.WAIT_TIME, s.sql_id, s.sql_child_number
  from v$session s,
       v$sort_usage u
 where s.saddr = u.session_addr
 order by u.blocks desc
 fetch first 20 rows with ties

Upvotes: 1

Related Questions