1 2 3 Previous Next 31 Replies Latest reply: Jan 28, 2014 8:37 PM by Suntrupth Go to original post RSS
      • 15. Re: SS Enqueue and sort segment request waits on 8.1.7.4 after trying to drop temp tablespace
        Suntrupth

        The tablespace name I really used corresponded to ts#=74 in v$tablespace.

         

        We are thinking of rebuilding the other indexes in this tablespace in a new locally managed tablespace and then dropping the existing dictionary managed tablespace.  My only question is whether some corruption will cause the drop tablespace to fail.

         

        Hi Bobby,

         

        I am not sure about your current situation now, Hope its better .

         

        I couldn't find time to reply back yesterday. Looking at the huge amounts of temporary segments in your tablespace (74), just got me thinking if you or Oracle Support have made you aware of an event DROP_SEGMENTS which is an event users may invoke to clear temporary segments. It drops the temporary segments just like SMON does in the background. This event has the advantage of not having the CPU consumed by SMON.

        Note 47400.1 : EVENT: DROP_SEGMENTS - Forcing cleanup of TEMPORARY segments

         

        There is also a method to patch (make changes) dictionary (not recommended otherwise). You could seek help from Oracle Support

        The method is to identify the segment( which you already have) and update the segment_type from temporary to 999.

        Note 388619.1: Last resort when SMON takes 100% CPU

         

        Regards,

        Suntrupth

        • 16. Re: SS Enqueue and sort segment request waits on 8.1.7.4 after trying to drop temp tablespace
          Bobby Durrett

          Suntrupth,

           

          I'll take a look at those notes.  At this point we are running fine with 10061 and 10269 events set so there isn't a crisis.  But, of course we need to clean things up eventually so we are planning our next move.  But, no one stayed up late last night working on it since the system is running.

           

          Thanks again for your input.

           

          - Bobby

          • 17. Re: SS Enqueue and sort segment request waits on 8.1.7.4 after trying to drop temp tablespace
            Bobby Durrett

            I put this question in the SR if anyone wants to see it:

             

            Do you know  if we dropped the tablespace if it would still cause the SS enqueues? The thing about this is that the index rebuild failed around December 25 and we didn't try to drop the temporary tablespace until January 18th so it seems that SMON spinning on the cleanup didn't cause the SS enqueues until we tried to drop the temporary tablespace. But it is hard to see how  dropping the tablespace is related to the cleanup of the failed index creation's temporary segment. I think it must be that when we tried to drop the temporary tablespace smon had to cleanup the sort segment that was in it and this cleanup was hungup behind the cleanup of the temporary segment. But, then we dropped the temporary tablespace with the 10061 and 10269 events set and yet were still seeing the ss enqueues (I think) so I'm not sure why that would be. If the drop tablespace cleanup doesn't affect anything else we can just run sqlplus nohup and let it run for two months (estimate that it will take 55 days).
            Another thing I'm not sure about is why, after dropping the temporary tablespace with the 10061 and 10269 events set and removing the events and restarting, are we still having application issues?  I didn't actually observe the ss enqueues recurring this last time we brought the database up after dropping the temporary tablespace and with the events taken out of the init.ora.  But, we had complaints from the users and these went away when we put 10061 and 10269 back in and restarted the database.  Strange, especially since the temporary tablespace was locally managed anyway.
            The point here is that there are two seemingly unrelated tablespaces - the dictionary managed index tablespace with a 500 gig temporary segment with 177000 extents and a locally managed temporary tablespace that we have dropped.  The SMON cleanup of the temporary segment wasn't causing system issues until we tried to drop the temporary tablespace on Saturday but why are they related and can we cleanup the temporary segment by dropping the index tablespace and letting it take forever to clean up in the background?  I.e. Will we start having sessions hang on SS enqueues if the server process doing the drop of the index tablespace cleans up uet$ instead of SMON?
            - Bobby
            • 18. Re: SS Enqueue and sort segment request waits on 8.1.7.4 after trying to drop temp tablespace
              Suntrupth

              Hi Bobby,

               

              Well, If a process requires a sort segment, you need the SS enqueue.

               

               

              When SMON is trying to clean up your dead transaction (failed rebuild operation), it may not service other work like cleaning up temporary space, handling sort-segment requests.

               

               

              If you have removed the events, you are making SMON do your cleanup again. Why not have these events set and let the server process perform the cleanup so that you do not face any SS enqueues?

               

               

              I believe Jonathan had suggested dictionary patching by updating the seg$ as described in the note that I had referred previously.

               

               

              I don't think you would be able to drop the index tablespace as you still have temporary segments that needs a cleanup.

               

               

               

              Regards,

              Suntrupth

              • 19. Re: SS Enqueue and sort segment request waits on 8.1.7.4 after trying to drop temp tablespace
                Jonathan Lewis

                Bobby,

                 

                A thought by twitter from Mark Farnham - if you do an "alter tablespace offline", "drop tablespace included contents and data files"  Oracle may have a fast way to eliminate all the entries from uet$, and fet$ because it doesn't have to move rows from uet$ into fet$ and check adjacency - it simply has to "delete where ts# = ".

                 

                Like me, he was working from memory from a long time ago - so can't be certain of details: check with support, model on a spare database, and take a backup of production before trying it if you think it's worth trying.

                 

                Regards

                Jonathan Lewis

                • 20. Re: SS Enqueue and sort segment request waits on 8.1.7.4 after trying to drop temp tablespace
                  Bobby Durrett

                  Jonathan,

                   

                  Mark's comments about the alter offine and drop ring a bell.  I think early on researching this on MOS I got the idea of the tablespace drop from an Oracle support document.  We have a development database of the same version.  I've been thinking about trying to recreate the situation there by creating a table with millions of small extents to fill up UET$, etc.  At this point turning off the SMON cleanup isn't hurting us in production so I may have time to do some testing on our dev database.  Will definately verify a good backup of prod prior to doing anything radical.

                   

                  - Bobby

                  • 21. Re: SS Enqueue and sort segment request waits on 8.1.7.4 after trying to drop temp tablespace
                    pudge

                    After a bit of out of forum "dinosaur" crosstalk with JL, I believe you are trying to solve two problems: 1) Get rid of an old TEMP tablespace 2) Cleanup uet$/fet$ efficiently from one or more broken/crashed 999.99999 named temporary objects that are probably residue of failed completion of creation of the final image/ object rename to the index in some user tablespace.

                     

                    From the thread it seems you are prepared to recreate any and all contents of the tablespace #74. That, or saving any source of  data objects (meaning clusters, tables, and iots [which are tables, doggone it, but some people think they are indexes]) and being willing to recreate any dependent objects such as indexes and materialized views that may physically exist in that tablespace.

                     

                    IF (and only if) that is true, I believe your likely best option is to take tablespace #74 offline and then drop that tablespace including contents. This should bypass the "careful" (and pessimal) algorithm for multiuser consistency of uet$/fet$ entries that has to be done for dictionary tablespaces that are open in favor of a straight delete by the file numbers of the components of the tablespace. This should work correctly without you having to do any rogue surgery directly on uet$/fet$. Of course, at the end of this operation everything in tablespace #74 will be GONE.

                     

                    As for the temporary tablespace (if it is having any affect on uet$/fet$, being the antique permanent sort of tablespace holding temp bits), then you should be able to do the same thing for it.

                     

                    Given the luxury of an instance stop, and acquiescence to the caveats above, probably together with a backup if this is production, I'd be inclined to startup mount, alter the datafiles component of the tablespaces in question offline, startup restricted, verify that the datafiles being offline left the tablespaces in question offline, so smon is not trying to molest them, and then execute the alter tablespaces drop including contents on first the user table and then the deprecated temp.

                     

                    With the noted huge numbers of rows, this won't be instanteous, but it should operate at reasonable amount of speed. This will not shrink the cluster containing uet$/fet$, but when the file numbers are re-used at least you'll re-use that space.

                     

                    It's been a while, but that as I recall it is the optimal scenario for clearing huge numbers of rows from uet$/fet$ quickly in a supported fashion from v6.0 through hmm, still, for dictionary managed tablespaces. As far as  I know they never improved that online dictionary free extents code in favor of working on locally managed tablespaces.

                     

                    Good luck.

                    mwf

                    • 22. Re: SS Enqueue and sort segment request waits on 8.1.7.4 after trying to drop temp tablespace
                      Bobby Durrett

                      Thanks for your reply.  Your recommendation is similar to what we had in mind, but the difference is that it doesn't require any wierd events being set to accomplish it.  Our plan was to move all the objects out of the tablespace with the 10061 and 10269 events set to stop SMON from working.  Your idea of making the tablespace offline before opening the database is nice.  Cleaner for sure.  The temp tablespace is already dropped.  It was locally managed so there is no uet$/fet$ cleanup there.  But, we want a way to cleanup the index tablespace after moving all the indexes out of it - only a few gigabytes are in there out of 500 or so of space allocated so shouldn't take long to move the indexes.

                      I'm building a small test database now so I'll play with it there.

                       

                      - Bobby

                      • 23. Re: SS Enqueue and sort segment request waits on 8.1.7.4 after trying to drop temp tablespace
                        Bobby Durrett

                        From my tests it appears that dropping the tablespace is no more efficient than the smon cleanup.  It runs the same statements against uet$:

                         

                        select file#,block#,length from uet$ where ts#=:1 and segfile#=:2 and segblock#=:3 and ext#=:4

                         

                        Do the drop should take a number of weeks.

                         

                        It is possible we could run with the events set for that long and just run the drop tablespace in the background.  I'm just not sure if it would impact something else.

                         

                        - Bobby

                        • 24. Re: SS Enqueue and sort segment request waits on 8.1.7.4 after trying to drop temp tablespace
                          Bobby Durrett

                          Well, it looks like we will probably do the following:

                           

                          -- do all these steps as SYSDBA

                          -- verify that events 10061 and 10269 are set:

                          show parameter event

                          shutdown immediate

                          startup restrict

                          update seg$ set type#=999 where type#=3 and file#=865 and block#=261032 and ts#=74;

                          -- if only one row updates (it should)

                          commit;

                          shutdown abort

                          -- must be abort

                          -- take out events

                          startup

                          -- check if all seems ok

                          shutdown immediate

                          startup

                           

                          This is from Oracle's document "Last resort when SMON takes 100% CPU (Doc ID 388619.1)"

                           

                          Oracle support is encouraging us to 11.2.0.4 which makes sense.  But I guess the "last resort" is to change the segment type so SMON ignores it.

                           

                          But, I'm not sure how this will affect our issue with SS enqueues and sort segment request waits.

                           

                          - Bobby

                          • 25. Re: SS Enqueue and sort segment request waits on 8.1.7.4 after trying to drop temp tablespace
                            Bobby Durrett

                            Hopefully my last update until we make the "last resort" change.  I think I understand the sort waits we were seeing.  I believe there were two situations.

                             

                            1  - SMON spinning when we dropped a locally managed temporary tablespace

                             

                            Since smon is busy it couldn't clean up the sort segments and user sessions that needed access to a sort segment hung.

                             

                            2 - SMON spinning after bouncing database

                             

                            SMON has to do something after a database bounce to let users start using a sort segment.  Looks like a busy SMON will lock out user sessions until SMON "adopts" the sort segment.

                             

                            The document I saw relating to case 1 regarding dropping temporary tablespaces, sort segments, and a busy SMON was this:

                             

                            Diagnosing BUG 5016142 enqueue ENQ - SS contention (Doc ID 416823.1

                             

                            I'm not 100% sure on 2 but it is similar to this case where SMON is doing transaction rollback after a bounce:

                             

                            Bug 1809682 : NEED SORT SEGMENT ... SMON IS BUSY DOING ROLLBACK

                             

                            My point here is that we had SMON spinning for three weeks with no sort waits, but when we tried to drop a temporary tablespace they started.  Then after setting the 10061 and 10269 events we were able to drop the temporary tablespace completely.  But, when we took the events back out we saw sort waits again!  But, the second set of waits was because we restarted the database and SMON was spinning from the beginning and never opened up the sort segment to the users.

                             

                            - Bobby

                            • 26. Re: SS Enqueue and sort segment request waits on 8.1.7.4 after trying to drop temp tablespace
                              pudge

                              Was your drop tablespace attempt with the datafiles and tablespace offline? If so I am surprised.

                              • 27. Re: SS Enqueue and sort segment request waits on 8.1.7.4 after trying to drop temp tablespace
                                Bobby Durrett

                                The tablespace was offline.  Here is my script:

                                 

                                alter session set max_dump_file_size = unlimited;
                                ALTER SESSION SET tracefile_identifier = 'bobbydurrett';
                                ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

                                alter tablespace table_ts offline;
                                drop tablespace table_ts including contents;

                                ALTER SESSION SET EVENTS '10046 trace name context OFF';

                                 

                                I created a table in the tablespace with 5391 extents and just like the SMON trace the drop starts from the last extent querying uet$ for each one:

                                 

                                PARSING IN CURSOR #13 len=94 dep=1 uid=0 oct=3 lid=0 tim=1619173977 hv=1839874543 ad='d264a20'

                                select file#,block#,length from uet$ where ts#=:1 and segfile#=:2 and segblock#=:3 and ext#=:4

                                END OF STMT

                                PARSE #13:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1619173977

                                BINDS #13:

                                bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=8000000100000001 size=24 offset=0

                                   bfp=80000001000d80f0 bln=22 avl=02 flg=05

                                   value=4

                                bind 1: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=8000000100000001 size=24 offset=0

                                   bfp=80000001000d80c0 bln=24 avl=02 flg=05

                                   value=5

                                bind 2: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=8000000100000001 size=24 offset=0

                                   bfp=80000001000d8090 bln=24 avl=02 flg=05

                                   value=2

                                bind 3: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=8000000100000001 size=24 offset=0

                                   bfp=80000001000d8060 bln=24 avl=03 flg=05

                                   value=5390

                                EXEC #13:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1619173978

                                FETCH #13:c=0,e=0,p=0,cr=32,cu=0,mis=0,r=0,dep=1,og=4,tim=1619173978

                                STAT #13 id=1 cnt=0 pid=0 pos=0 obj=13 op='TABLE ACCESS CLUSTER UET$ '

                                STAT #13 id=2 cnt=2 pid=1 pos=1 obj=9 op='INDEX UNIQUE SCAN '

                                =====================

                                PARSING IN CURSOR #13 len=94 dep=1 uid=0 oct=3 lid=0 tim=1619173978 hv=1839874543 ad='d264a20'

                                select file#,block#,length from uet$ where ts#=:1 and segfile#=:2 and segblock#=:3 and ext#=:4

                                END OF STMT

                                PARSE #13:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1619173978

                                BINDS #13:

                                bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=8000000100000001 size=24 offset=0

                                   bfp=80000001000d80f0 bln=22 avl=02 flg=05

                                   value=4

                                bind 1: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=8000000100000001 size=24 offset=0

                                   bfp=80000001000d80c0 bln=24 avl=02 flg=05

                                   value=5

                                bind 2: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=8000000100000001 size=24 offset=0

                                   bfp=80000001000d8090 bln=24 avl=02 flg=05

                                   value=2

                                bind 3: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=8000000100000001 size=24 offset=0

                                   bfp=80000001000d8060 bln=24 avl=03 flg=05

                                   value=5389

                                EXEC #13:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1619173978

                                FETCH #13:c=1,e=1,p=0,cr=32,cu=0,mis=0,r=0,dep=1,og=4,tim=1619173979

                                STAT #13 id=1 cnt=0 pid=0 pos=0 obj=13 op='TABLE ACCESS CLUSTER UET$ '

                                STAT #13 id=2 cnt=2 pid=1 pos=1 obj=9 op='INDEX UNIQUE SCAN '

                                 

                                So, it looks like this is the way a segment gets cleaned up in a dictionary managed tablespace if you use the normal Oracle sql commands.

                                 

                                Jonathan's idea of doing our own coalescing of the uet$ rows makes sense but for us it seems safer just to change the segment_type so smon won't try to clean up the uet$ rows for the temporary segment.

                                 

                                - Bobby

                                • 28. Re: SS Enqueue and sort segment request waits on 8.1.7.4 after trying to drop temp tablespace
                                  pudge

                                  Yes, it will still do the selects. But I do not believe it will attempt the coalesce or do the inserts into uet$ from fet$. It should simply to deletes from both. If you let it run (perhaps on something smaller) until you get to a delete you will be able to tell whether my memory is correct.

                                   

                                  If you do the bit about startup mount and taking the datafiles offline in the first place so the tablespace never comes online in the duration of the run of the instance it *might* skip the select, knowing it does not have to possibly flush any dirty blocks. I simply don't remember that bit.

                                   

                                  Once you've let your test run to the actual delete to verify whether I am correct it does the "quick" thing instead of the long complicated thing, this IS a case where the tracing probably is a significant component of the load.

                                   

                                  The unsupported way to do this is of course to do the deletes yourself, which will prune the select. I am not recommending you do that on a production database.

                                  • 29. Re: SS Enqueue and sort segment request waits on 8.1.7.4 after trying to drop temp tablespace
                                    Bobby Durrett

                                    I've uploaded the trace and tkprof output if you want to see it:

                                     

                                    http://www.bobbydurrettdba.com/uploads/dropdmtablespace.zip

                                     

                                    What I did to mimic the situation on our production system was create a table with 5391 extents, drop the table, and kill the server process doing the drop.  The database was up with events 10061 and 10269 set so SMON wouldn't do the cleanup.  It appears that there were about 638 selects from uet$ which ended when it found an existing extent.  Then there were about 4761 deletes from uet$.  It doesn't add up to 5391 exactly but it's close.

                                     

                                    I'd have to do another test to try it with the datafiles offline and do the startup mount but it could be done.

                                     

                                    Thanks for your help with this.

                                     

                                    - Bobby