1 2 Previous Next 17 Replies Latest reply on Jan 16, 2009 6:26 PM by 3530

    Temp space is not deallocating

      The database version is There is only one application schema running with a size < 1G. We have allocated 2G initially for temp space. The temp tablespace was full and increased to 5G, 8G and now 13G. The temp space is not
      deallocating after the sorts. We are keep on increasing the space when there is an error ORA-01652: unable to extend temp segment by 128 in tablespace.
      I planned to create a new temp tablespace and assign that as default temp tablespace and can drop the old temp space for reclaiming space. But, the problem may repeat again with the new temp space. I have the same problem on one of the database also. All these databases will run for 24/7. Is there any solution for this instead of restarting the database?.
        • 1. Re: Temp space is not deallocating

          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.

          Dilip Patel.

          Edited by: Dilip Patel on Jan 14, 2009 3:34 PM
          • 2. Re: Temp space is not deallocating
            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.

            Good luck
            HTH -- Mark D Powell --
            • 3. Re: Temp space is not deallocating
              Following is the output for your query. There is only one temp tablespace (default). I have allocated 50G of temp space.

              ---------- ---------- ---------- ---------- ---------- --------
              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;

              ---------- ---------- ----------- ---------- -----------
              2 2.4066E+10 2937736 4925095936 601208
              1 2.4156E+10 2948704 0 0
              • 4. Re: Temp space is not deallocating

                Looks like at present you have only 8 blocks used for sorting. You need to keep monitoring usage of TEMP tablespace.

                Dilip Patel.
                • 5. Re: Temp space is not deallocating
                  Yes. There are no long running and complex joins in the queries. But, almost 47G of temp space is used and not deallocated. It is just keep on increasing. The used blocks are not released. Is there any solution for this to avoid.
                  • 6. Re: Temp space is not deallocating

                    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 Patel.
                    • 7. Re: Temp space is not deallocating
                      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 --
                      • 8. Re: Temp space is not deallocating
                        I have checked the v$sort_usage again. It's still using 8 blocks only. If so, why 47G of already used temp space is not released?
                        • 9. Re: Temp space is not deallocating
                          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 --
                          • 10. Re: Temp space is not deallocating
                            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.
                            • 11. Re: Temp space is not deallocating
                              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.
                              • 12. Re: Temp space is not deallocating
                                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 --
                                • 13. Re: Temp space is not deallocating
                                  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.
                                  • 14. Re: Temp space is not deallocating
                                    I have the same problem on another 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;

                                    ----------- ----------- ------------- ------------- --------------- -----------
                                    128 588032 0 0 588160 12353
                                    1 2 Previous Next