This content has been marked as final. Show 17 replies
Try to find out which sessions are using TEMP tablespace. Try to nail down query which is causing the huge TEMP tablespace usage.
SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,
a.username, a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;
It can be possible that several different sessions are creating TEMP segments for sorting activities, so you are not able to see it deallocting.
Edited by: Dilip Patel on Jan 14, 2009 3:34 PM
I agree with Dilip. You need to track down the session/SQL using sort cause your problem description indicates some process is using large amounts of sort area. If your system supports adhoc queries you could have a user selecting all the data in your largest table in a specified order or you may have SQL being solved via a plan that uses too much temp space. A single SQL statement might need sort space to support a hash join, a group by, and an order by all at once.
Poor statistics could be responsible but the only way to know is to search out the problem session(s) and see what they are doing.
HTH -- Mark D Powell --
Following is the output for your query. There is only one temp tablespace (default). I have allocated 50G of temp space.
SEGFILE# SEGBLK# BLOCKS SID SERIAL# STATUS
---------- ---------- ---------- ---------- ---------- --------
1025 2168633 8 159 16819 INACTIVE
1025 2168753 8 159 16819 INACTIVE
SQL> select FILE_ID,BYTES_USED,BLOCKS_USED,BYTES_FREE,BLOCKS_FREE from v$temp_space_header;
FILE_ID BYTES_USED BLOCKS_USED BYTES_FREE BLOCKS_FREE
---------- ---------- ----------- ---------- -----------
2 2.4066E+10 2937736 4925095936 601208
1 2.4156E+10 2948704 0 0
I used v$sort_usage only to track free space on temp tablespace.
On my databases v$tempfile column bytes is matching with v$temp_space_header column bytes_used.
I am not convinced with use of v$temp_space_header. If someone can interpret use of this view please contribute.
You need to keep track of v$sort_usage while you have major queries running or about to receive ora-6512 error.
Dilip, yes there are known issues with information in v$temp_space_heade not being correct.
I agree the OP needs to capture the information in v$sort_usage on a regular basis for sessions using large amounts of temp as previously stated in the thread.
I would not be using expandable data files for temp. I would give it a set number of fixed data files. If the space is exhaused Oracle will return errors and I would try to grap the v$sort_usage information.
This might be a case where a database event trigger on server error might be of use though I think i would just try capturing the sort usage first.
HTH -- Mark D Powell --
Ok, once temporary tablespace extents are allocated to a sort segment the extents are not released for the life of the instance. See v$sort_segment
Once OS file space is allocated to a datafile that space is not released by Oracle for the life of the datafile. True tempfiles only live for the life of the database instance.
The above is one reason I recommended you not allow your temporay tablespaces to grow or at least set a maximum size per file.
HTH -- Mark D Powell --
There are actually two parts of problem,
1. Why your application need so much of temporary tablespace? With few G of data running 47G temp tablespace that usually spell wrong queries. I have seen that happening in our dev environment, it's a result of a cartesian join. You need to use your v$sort_usage to capture the query that using most temp segment at time of peak usage. Or run statspack report during busy hour to start with.
2. Temp tablespace is not released. That's not a problem by itself. To more efficient manage the temporary tablespace usage. The temporary (sort) segment will not be de-allocated after being used first time. It will be re-used. That save Oracle a lot of time to keep allocate/deallocate temp segments. It's expected behavior. Check this metalink doc for more info.
Temporary Segments: What Happens When a Sort Occurs
Doc ID: 102339.1
So in your case, that's bit of problem because you know you don't need such big size of temporary tablespace. That's a result of a runaway query. If you need to claim these space back, you need to create a new temporary tablespace switch default temporary tablespace to new one and drop old one. Or if you can restart instance, you can shutdown and start in restrict mode to resize your temporary tablespace datafile. Oracle will deallocate temp segment at next startup.
Of course you need to deal with the culprit query first, otherwise it will keep increase your temp tablespace. Or just cap your temp tablespace at 10G and let it fail.
I don't understand the point..."+Once OS file space is allocated to a datafile that space is not released by Oracle for the life of the datafile. True tempfiles only live for the life of the database instance+.". This may be correct. But, it has to display the temp space as free blocks once sorting is complete, eventhough it's not deallocted the OS space.
My point is in your inital post it is not clear that you do not expect unused temporary file space to be release at the OS level. I was making sure that thread readers see that point that once an Oracle file takes space at the OS level it keeps it barring DBA action.
My first point noted that once allocated to a sort an extent is never released. It is available, free, for reuse, but it is allocated to the sort segment. The sort segment(s) are visible in v$sort_segment and the contents of the sort segment are visible in v$sort_usage.
But your problem is you need to find those sessions consuming the temporary tablespace. Set up a query against v$sort_usage to capture the users where blocks or extents are over some threshold value. Eventually your problem sessions should appear. Any temporay lob usage in your application? Huge hash joins?
HTH -- Mark D Powell --
I can understand your point. But, my question is:
There are no changes in my application code since moved to production. We have initially allocated 5G. This space was filled-up after 2 or 3 months. I have added additional space and this is also filled up and raised ORA-01652 error. I can't say how frequent the space gets full. That may be depends on the how much additional space I have added.
If there are no changes in the SQLs, it has to use same temp space every time, ofcourse that depend on the number of sessions. If I monitor for a month, it has to work in a similar way in the second month also.
I have the same problem on another 10.2.0.3 database. I'm receiving ORA-01652 errors.
SQL> select USED_BLOCKS,FREE_BLOCKS,FREED_EXTENTS,FREE_REQUESTS,MAX_USED_BLOCKS,EXTENT_HITS from v$sort_segment;
USED_BLOCKS FREE_BLOCKS FREED_EXTENTS FREE_REQUESTS MAX_USED_BLOCKS EXTENT_HITS
----------- ----------- ------------- ------------- --------------- -----------
128 588032 0 0 588160 12353