1 2 3 Previous Next 30 Replies Latest reply: Sep 15, 2008 9:17 PM by OracleGuy777 RSS

    smon not clearing my temp tablespace

    OracleGuy777
      hi guys,

      even after I shutdown and restart my instance, my temp tablespace is showing as having 27.5 out of 30G used. why is this happening?

      thanks
        • 1. Re: smon not clearing my temp tablespace
          639907
          Hi OracleGuy777,

          It is "normal" that temp tablespaces appear full, as the segments are not de-allocated. To know how much space is being used by active queries and to know if your are about to run out of temp tablespace use v$sort_usage and v$sort_segment.

          E.g.:

          These were for 8i/9i - should be OK in 10:
          select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,
          FREE_BLOCKS, round((used_blocks/total_blocks), 2) "PERC_USED"
          from v$sort_segment;
          Or:
          SELECT A.tablespace_name tablespace, D.mb_total,
          SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
          D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
          FROM v$sort_segment A,
          (SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
          FROM v$tablespace B, v$tempfile C
          WHERE B.ts#= C.ts#
          GROUP BY B.name, C.block_size) D
          WHERE A.tablespace_name = D.name
          GROUP by A.tablespace_name, D.mb_total;
          or for user details:
          SELECT a.sid, a.username, a.osuser, a.program, a.tablespace_name, 
          a.bytes "BYTES_USED", round(a.blocks / b.total_blocks,3) "PERC_USED" 
          from (select sor.tablespace_name, ses.sid, ses.username, 
          ses.osuser, ses.program, sor.blocks, sor.bytes 
          from (select /*+ optimizer rule */ u.tablespace "TABLESPACE_NAME", u.session_addr,
          sum(u.blocks) blocks, sum(u.blocks) * &blksize bytes 
          FROM v$sort_usage u GROUP BY u.session_addr, u.tablespace) sor, 
          (select /*+ optimizer rule */ saddr, sid, username, osuser, program from v$session) ses 
          where ses.saddr=sor.session_addr) a, (select tablespace_name, sum(decode(maxblocks,0,blocks,maxblocks)) "TOTAL_BLOCKS"
           from dba_temp_files group by tablespace_name) b 
          where a.tablespace_name=b.tablespace_name order by a.bytes
          Cheers,


          Francisco Munoz Alvarez
          http://www.oraclenz.com
          • 2. Re: smon not clearing my temp tablespace
            639907
            Dear OracleGuy777,

            Also take a Look on these Metalink Notes:

            - [Temporary Segments: What Happens When a Sort Occurs - Note:102339.1|https://metalink.oracle.com/metalink/plsql/f?p=130:14:259560272126650771::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,102339.1,1,1,1,helvetica]
            - [Temporary Segments Are Not Being De-Allocated After a Sort - Note:1039341.6|https://metalink.oracle.com/metalink/plsql/f?p=130:14:259560272126650771::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,1039341.6,1,0,1,helvetica]
            - [SMON - Temporary Segment Cleanup and Free Space Coalescing - Note:61997.1|https://metalink.oracle.com/metalink/plsql/f?p=130:14:259560272126650771::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,61997.1,1,0,1,helvetica]
            - [EVENT: DROP_SEGMENTS - Forcing cleanup of TEMPORARY segments - Note:47400.1|https://metalink.oracle.com/metalink/plsql/f?p=130:14:259560272126650771::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,47400.1,1,0,1,helvetica]

            Cheers,

            Francisco Munoz Alvarez
            http://www.oraclenz.com
            • 3. Re: smon not clearing my temp tablespace
              Aman....
              Oracleguy,
              This is a normal problem that SMON. SMON should do it but this is a fairly large cpu consuming process.
              From JL's excellent note,
              www.jlcomp.demon.co.uk/smon.doc
              read here,
              Why won’t my TEMP disappear?+

              There are three possible reasons:

              Smon only tries once every 125 minutes, so a temp segment can hang around for quite a long time.
              To do any space management a process needs to hold the single ST lock - if some other process has got it and hangs on to it as smon cuts in to clear up temporary segments you get an ORA-01575 error, and smon will have to try again later.
              alter tablespace temp

              default storage

              +(minextents 1);+

              Figure 3   Getting rid of ‘real’ TEMP segments

              Temporary segments in proper TEMPORARY tablespaces (a new feature for v7.3) are supposed to exist for the lifetime of the instance to cut down the number of recursive transactions on the data dictionary.  These segments will be recorded in the view v$sort_segment.  They get dropped (but not coalesced) when the instance shuts down.
              SMON should do it but if it doesn't than you can use this event to clear it,
              alter session set events 'immediate trace name DROP_SEGMENTS level TS#1';+

              where TS#=TS# from ts$ table. But I wont recommend for this. The only way to drop the segment is drop and recreate teh temp tablespace.
              HTH
              Aman....
              • 4. Re: smon not clearing my temp tablespace
                247514
                I think OracleGuy has a valid point here.

                The temporary segment should be released (de-allocated) at database startup.

                Oracleguy,

                What's the Oracle version. You might want to check the tablespace type make sure it's not permanent type.
                • 5. Re: smon not clearing my temp tablespace
                  639907
                  Hi,

                  One more link to you:

                  - http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1384603628260

                  Cheers,

                  Francisco Munoz Alvarez
                  http://www.oraclenz.com
                  • 6. Re: smon not clearing my temp tablespace
                    Aman....
                    Ying,
                    I believe that untill 817 when Oracle was having the option to use Permanent tablespaces as temporary,this point stands correct. But from 9i onwards, when we have an explicit temporary tablespace available,I don't think that there should be any issue.But I have seen temporary tablespaces usage shooting high and its not being cleared with SMON process at startup. The only way that is followed is to shift to another temp.I am not really sure that what can be the reason internally for it.
                    Cheers
                    Aman....
                    • 7. Re: smon not clearing my temp tablespace
                      Aman....
                      I am not sure that how far this is correct but from the link that Francisco has given, there is only thing that I found "interesting".
                      SMON doesn't clean up temporary tablespaces.  It does not work like that anymore.  It worked like
                      that years and years ago with permanent tablespaces used to hold temporary data.

                      There is no cleaning up by SMON

                      There is no coalescing

                      And sys and system should probably need pretty much 0 sort space as you aren't using them to do
                      things and recursive sql run as sys should not be doing sorts that hit disk.
                      So you are using a "true" temporary tablespace created with Create temporary tablespace or a tablespace created like Create tablespace temp...tempfile?
                      Or as Ying pointed, a permanent tablespace used as temporary(I really doubt about it though).
                      Aman....
                      • 8. Re: smon not clearing my temp tablespace
                        Aman....
                        Francisco,
                        There is not much relevant talk in this link unfortunately about the situation of oracleguy. The only crux is that one must use "always" true temporary tablespace with LMT and just forget about everything else. Why SMON doesn't clear it, there is nothing that I could find from that link.
                        Regards
                        Aman....
                        • 9. Re: smon not clearing my temp tablespace
                          247514
                          Aman.... wrote:
                          Ying,
                          I believe that untill 817 when Oracle was having the option to use Permanent tablespaces as temporary,this point stands correct. But from 9i onwards, when we have an explicit temporary tablespace available,I don't think that there should be any issue.But I have seen temporary tablespaces usage shooting high and its not being cleared with SMON process at startup. The only way that is followed is to shift to another temp.I am not really sure that what can be the reason internally for it.
                          Cheers
                          Aman....
                          I understand that part. But sometime if the database was upgraded from older version DBAs might forget to drop the outdated temporary tablespace. It's just a precaution and sanity check.

                          Cleanup temp segment will be performed by SMON at startup. Please check this metalink doc

                          SMON - Temporary Segment Cleanup and Free Space Coalescing
                          Doc ID: Note:61997.1

                          also note
                          Temporary Tablespace, the Sort Extent Pool, and OPS
                          Doc ID: Note:65973.1

                          In 8 and above, Oracle use Sort Extent Pool (SEP) in the SGA to describe temp segment. Subsequent sort operations will then be allocated extents from this pool via a memory lookup. This is no doubt much more efficient and fast than regular space allocation.
                          And because of sort extent is tracked in SGA at instance shutdown they will be marked to cleanup and SMON will do it at next startup.
                          • 10. Re: smon not clearing my temp tablespace
                            639907
                            Dear Aman,

                            I saw your post in the Ask Tom Thread I posted (Nice One), This thread have some nice information to the OP like:
                            "You will never see an extent freed in this tablespace until you shutdown (or alter the tablespace in some way)".
                            Is it means that if I have in such tablespace 1G used space, new transaction that need 'temp' space will allocate new extend or will reuse existing?

                            Tom Respond: reuse existing. As I said:

                            "This is normal and is OK -- we
                            are not losing extents, we are just managing them internally -- in memory,
                            instead of in the data dictionary. "
                            Also I found another Metalink Note that have some excellent information about this topic:

                            - [Temporary Segments: What Happens When a Sort Occurs - Note:102339.1|https://metalink.oracle.com/metalink/plsql/f?p=130:14:1844795641583344823::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,102339.1,1,1,1,helvetica]

                            This mention some possible situations like:

                            - If you set the initial and next extent size too small for a temporary tablespace,then will be possible for this Temp Tablespace to have thousands or tens of thousands temporary extents which can take oracle some days to clean up after shutdown abort and startup.

                            Best Regards,

                            Francisco Munoz Alvarez
                            http://www.oraclenz.com
                            • 11. Re: smon not clearing my temp tablespace
                              Aman....
                              Ying,
                              Thanks alot for the article about the SEP.Its really good.I am not still sure that why SMON doesn't remove the temp segments after instance startup after some time.
                              Anyways I shall read these two docs now.
                              Cheers
                              Aman....
                              • 12. Re: smon not clearing my temp tablespace
                                Aman....
                                Francisco,
                                I guess this note 61997.1 describes the best nature of SMON's work.Yours also was a good one.Thanks for it.
                                Regards
                                Aman....
                                • 13. Re: smon not clearing my temp tablespace
                                  Aman....
                                  Oracleguy,
                                  See the notes mentioned by yingkuan. Probably you won't need anything else to know about SMON/Temp's behavior.
                                  Aman....
                                  • 14. Re: smon not clearing my temp tablespace
                                    639907
                                    Dear Aman,

                                    Thank you, the Note you refer (61997.1) was included in my first post ;)

                                    Cheers,

                                    Francisco Munoz Alvarez
                                    http://www.oraclenz.com
                                    1 2 3 Previous Next