1 2 3 Previous Next 31 Replies Latest reply: Jan 28, 2014 8:37 PM by Suntrupth RSS

    SS Enqueue and sort segment request waits on 8.1.7.4 after trying to drop temp tablespace

    Bobby Durrett

      We have a case open with Oracle support but I thought I would throw this out there if that is ok.

       

      We have an 8.1.7.4 database running on HP-UX 11.11 PA-Risc and it looks like any session that wants to use some temp space is hanging on an SS enqueue or in a few cases is waiting on a "sort segment request" wait.

       

      Saturday we tried to move all the users over to a new temporary tablespace and drop the old one but the drop hung and we control-C'd out of it.  We put the users back to the original temp.  Note that both the old and new temp tablespaces are locally managed.

       

      Before trying to drop the old temp tablespace we killed the existing sessions including one that had been running for two weeks and was hung on SMON.

       

      Queries of v$fast_start_transactions and x$ktuxe don't indicate that SMON is rolling back some large transaction.

       

      Also, SMON appears to be running this query continually:

       

      SELECT file#, block#, LENGTH

        FROM uet$

      WHERE ts# = :1 AND segfile# = :2 AND segblock# = :3 AND ext# = :4

       

      Here are the locks held by SMON for what it's worth:

       

      ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
      ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
      C000000028C43CD8 C000000028C43CF8          8 TT         74         16          4          0      59610          0
      C000000028C43C68 C000000028C43C88          8 TS         74 -666633304          6          0      59650          0
      C000000028C1CB38 C000000028C1CB58          8 ST          0          0          6          0         13          0

       

      ST, Space Management Transaction
      TS, Temporary Segment (also TableSpace)
      TT, Temporary Table

       

      Same database had a recovery scenario about a month or so back due to some deleted data files.  Also, over the holidays we had to rebuild a huge global index and increased our temp tablespace to get that done which is why we are trying to shrink it now by creating a new smaller one.  Also, the index creation was taking forever in the existing dictionary managed tablespace so we ended up moving the index to a locally managed one.  Also, UET$ has about 33,000,000 rows and most of the data is in dictionary managed tablespaces.  Queries to dba_free_space typically take 30 minutes to return - i.e. we know it is messed up and has been this way for a long time.

       

      Fun for us.  If anyone has some insight that would be great.

       

      - Bobby

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

          Bobby,

           

          This is dredging up some old memories - but your comment about 33M rows in uet$ is significant.

           

          The query being run by smon is its attempt to find an extent belonging to a segment because it's dropping that segment - and it's dropping the extents one by one. If you've managed to create some very large segments in the past it's going to take a very long time to tidy up the mess, and while it's happening all sorts of other space management events are going to run into problems.  If you bounce the database smon will just start off doing the same again within a couple of minutes of startup - or possibly straight away,

           

          There is an event you can set that will stop smon clearing temporary segments - 10061 I think - this may let you work around some of your problems, but you'll then have problems with space that will never go away - and you'll have to get on to Oracle.  It may be an acceptable short-term fix, though - the database will be working, even if it's growing when it shouldn't be.

           

          (On one occasion I know I deleted about 400,000 rows from uet$ and fixed up all the associated rows in seg$, fet$ and somewhere else - but I can't recall the details)

           

          Regards

          Jonathan Lewis

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

            Jonathan,

             

            Thanks for your reply.  We did bounce the database and as you said it didn't help.  I think the final result of our support ticket will be something like what you describe but it is good to get some input outside of support.

             

            - Bobby

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

              Hi Bobby,

               

              As you are dealing with Temp tablespaces, SMON cleanup of segments can cause issues as it will not service sort segment requests.

               

               

              As Jonathan has already pointed out setting event 10061, you can set it and this will disable SMON from cleaning up temp extents.  This

              will leave used (unavailable) space in the tablespace in which the extents reside. You can then point users to another temporary tablespace if necessary. The database can then be restarted off-peak without 10061 and SMON will clean up the temporary extents.

               

               

              A shutdown abort will succeed, but as soon as the database has been started again, SMON will carry on where it left off. Drop the tablespace in which the extents reside. This can be done after disabling SMON with 10061. The user process performing the drop will do the cleanup. However, this appears to be no quicker than SMON.

               

               

              Just for your information, you can also check if it is performing a coalescing of free extents by running "select count(*) from dba_free_space;" a couple of time in 5-10 minute intervals. If you observe the count dropping , most likely SMON is coalescing as well.

              You can disable this with the help of 10269 event. If SMON has started to coalesce, and you wish to disable it, you will have to set 10269 in the parameter file and restart the database.

               

              Regards,

              Suntrupth

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

                Suntrupth,

                 

                Thanks for your reply.  We are getting some similar feedback from Oracle support.  My on-call coworker is working the sev 1 ticket now.  Interesting thing is that I was doing counts on uet$ and fet$ and not seeing change. If smon is freeing extents it is taking its time on each one.

                 

                Here are uet$ and fet$ counts about 13 hours apart:

                 

                10:42

                 

                select count(*) from SYS.UET$;

                 

                  COUNT(*)
                ----------
                  35881366

                Elapsed: 00:00:41.03

                select count(*) from SYS.FET$;

                  COUNT(*)
                ----------
                    934720

                 

                23:15:20

                 

                select count(*) from SYS.UET$;

                  COUNT(*)
                ----------
                  35881367

                 

                select count(*) from SYS.FET$;

                  COUNT(*)
                ----------
                    934720

                 

                So, UET$ went up by one row and FET$ stayed the same between 10:42 am and 11:15 pm CST.

                 

                - Bobby

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

                  Hi Bobby,

                   

                  Gathering an errorstack and a 10046 on the SMON session performing the cleanup should provide more details.

                   

                   

                  SMON does this - every 5 mins looks at FET$ to find any free extents to coalesce and around 2 hours checks the seg$ to see if it can cleanup any temporaty segments.

                   

                   

                  This should show how many items need cleaning:

                  select count(*) from user$ u, obj$ o where u.user#(+)=o.owner# and o.type#=10 and not exists (select p_obj# from dependency$ where p_obj# = o.obj#) ;

                   

                   

                  Best bet would be to set the events, and have them cleaned later.

                   

                   

                   

                  Regards,

                  Suntrupth

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

                    You could try a 10046 trace at level 12 for a few minutes to see how many times smon executes that query in the time, whether the input bind values are changing, and whether smon finds any rows that match the inputs.  It may be that your uet$ has become corrupted and left smon running an infinite loop as a consequence. It may be that smon is simply taking a huge amount of time on each extent because is has to read so many cluster blocks each time it runs the query.

                     

                    Regards

                    Jonathan Lewis

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

                      Suntrupth,

                       

                      Here is the output of the query you posted:

                       

                      select count(*)
                        2  from sys.user$ u, sys.obj$ o
                        3  where u.user#(+)=o.owner# and
                        4  o.type#=10 and
                        5  not exists
                        6  (select p_obj# from sys.dependency$
                        7  where p_obj# = o.obj#) ;

                        COUNT(*)
                      ----------
                               0

                       

                      Thanks again for your reply.

                       

                      - Bobby

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

                        Jonathan,

                         

                        Here is part of the 10046 trace.  It looks like this is the tablespace that we had trouble recreating the global index in.  We had dropped a number of older partitions on the base table and couldn't get the unusable index rebuilt in its dictionary managed tablespace.  According to a coworker of mine the space from the tablespace in question - about 500gig - has never been freed so that makes sense.  Oracle support made reference to a bug related to failed index builds so it may be related.

                         

                        Here is the output:

                         

                        *** 2014-01-22 02:24:47.928

                        *** SESSION ID:(8.1) 2014-01-22 02:24:47.905

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

                        PARSING IN CURSOR #2 len=94 dep=1 uid=0 oct=3 lid=0 tim=1598955320 hv=1839874543 ad='34c149b0'

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

                        END OF STMT

                        PARSE #2:c=0,e=2,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1598955320

                        BINDS #2:

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

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

                           value=74

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

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

                           value=865

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

                           bfp=80000001000e9b68 bln=24 avl=04 flg=05

                           value=261032

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

                           bfp=80000001000e9b38 bln=24 avl=04 flg=05

                           value=176590

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

                        FETCH #2:c=269,e=267,p=0,cr=52299,cu=0,mis=0,r=0,dep=1,og=4,tim=1598955588

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

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

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

                        PARSING IN CURSOR #2 len=94 dep=1 uid=0 oct=3 lid=0 tim=1598955588 hv=1839874543 ad='34c149b0'

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

                        END OF STMT

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

                        BINDS #2:

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

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

                           value=74

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

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

                           value=865

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

                           bfp=80000001000e9b68 bln=24 avl=04 flg=05

                           value=261032

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

                           bfp=80000001000e9b38 bln=24 avl=04 flg=05

                           value=176589

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

                        FETCH #2:c=269,e=269,p=0,cr=52299,cu=0,mis=0,r=0,dep=1,og=4,tim=1598955857

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

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

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

                        PARSING IN CURSOR #2 len=94 dep=1 uid=0 oct=3 lid=0 tim=1598955857 hv=1839874543 ad='34c149b0'

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

                        END OF STMT

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

                        BINDS #2:

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

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

                           value=74

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

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

                           value=865

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

                           bfp=80000001000e9b68 bln=24 avl=04 flg=05

                           value=261032

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

                           bfp=80000001000e9b38 bln=24 avl=04 flg=05

                           value=176588

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

                        FETCH #2:c=270,e=270,p=0,cr=52299,cu=0,mis=0,r=0,dep=1,og=4,tim=1598956127

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

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

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

                          I think this is the problematic temporary segment that SMON is stuggling to clean up:

                           

                          select
                            2  segment_name,
                            3  segment_type,
                            4  bytes/(1024*1024*1024) gigabytes
                            5  from dba_segments
                            6  where
                            7  tablespace_name='NOTTHEREALONE' and
                            8  header_file=865;

                          SEGMENT_NAME                                                                      SEGMENT_TYPE        GIGABYTES
                          --------------------------------------------------------------------------------- ------------------ ----------
                          865.261032                                                                        TEMPORARY          521.414948

                           

                          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.

                           

                          - Bobby

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

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

                               bfp=80000001000e9b38 bln=24 avl=04 flg=05

                               value=176588

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

                            FETCH #2:c=270,e=270,p=0,cr=52299,cu=0,mis=0,r=0,dep=1,og=4,tim=1598956127



                            Smon is deleting the extents in reverse order - since this is 8.1 the ela is centiseconds, so you've got 176,588 extents to go at 2.7 seconds per extent; total 4.77M seconds or fifty five days.


                            Check this with Oracle, but I think what I did was:


                            Take a backup of the database.

                            Set event 10061 to stop smon from trying to clear up.

                            Bounce the database

                            Query uet$ for all the extents in this segment that were adjacent.

                            Delete all the uet$ rows for the adjacent ones (couple of hundred thousand) and insert one uet$ entry that described the entire length of the set deleted.

                            All the uet$ entries for the segment may have to be renumbered - except I think I got lucky and didn't have to do this because my 200,000 turned into "the last one".

                            Update seg$ with the new number of extents.

                            This left the segment with a "sane" number of extent for smon to delete properly so flush the uet$ blocks from the cache and restart.

                             

                            Regards

                            Jonathan Lewis

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

                              Jonathan,

                               

                              Thanks again for your reply.  It sounds like you were manually coalescing the extents.  We will work it through with support.  There is only a few gigabytes of indexes in the tablespace in question so we may just rebuild them somewhere else and then drop the tablespace rather than messing with the uet$ table.  But, we will get Oracle support's direction either way.

                               

                              - Bobby

                               

                              p.s. I think I may have a grasp on why each query is taking 2.7 seconds.  It looks to me like the C_FILE#_BLOCK# cluster  that uet$ is part of has 122908 blocks on this system and the trace shows cr=52299 on the fetch so that means that the number of cr blocks scanned is about 42% of the number of blocks in the cluster.  I'm guessing that when you have segments with many extents there is a lot of chaining leading to many blocks of the cluster being visited for each query execution.  I looked at the cluster definition and it has size 250 so maybe that is too small when you have thousands of extents.

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

                                Your comment about the cluster and block visits is correct - if you look at dcore.bsq you'll see the (Oracle 5, maybe 6) assumption still recorded:

                                 

                                create cluster c_file#_block#(ts# number, segfile# number, segblock# number)

                                  size 225    /* cluster key ~ 25, sizeof(seg$) ~ 50, 5 * sizeof(uet$) ~ 150 */

                                  storage (initial 20K)               /* avoid space management during IOR I */

                                /

                                 

                                5 extents per segment on average!

                                That doesn't mean there's a limit of 5 rows for any one segment in a block, of course.

                                The numbers don't really add up - at 150 bytes per row you get about 50 rows per block, which would make 52,000 blocks (your CRs) equate to about 2.7M rows in uet$ - but the rate at which you're clearing them is to slow at present for you to have deleted down to 176,000 already so (a) you must have been deleting them much more quickly to start with and (b) the current CRs might also include a large number of undo blocks. 

                                 

                                That's all academic, of course, the real problem is how to resolve the mess as rapidly as possible - and I'm not sure you'll be able to get rid of the tablespace without getting rid of the uet$ entries. So if you can't work around deleting them by hand have you considered exporting the entire database and creating a new one ? It might be the safest, fastest thing to do.

                                 

                                Regards

                                Jonathan Lewis

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

                                  Jonathan,

                                   

                                  Thanks again for your reply.  It all makes good sense.  I'm concerned too about whether dropping the tablespace would clean up uet$ versus doing the manual update.

                                   

                                  It is a 4 terabyte database so export/import would take some time but it would definately be nice to move to a cleaner environment if we could.

                                   

                                  - Bobby

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

                                    I think I figured out why our fet$ and uet$ counts didn't go down yesterday.  It looks like after the database restart smon starts over with the last extent even though it isn't on uet$.  I did this query to find the first and last ext# for the segment that smon is trying to clean up:

                                     

                                    select
                                      2  min(ext#),
                                      3  max(ext#)
                                      4  from sys.uet$
                                      5  where
                                      6  ts#=74 and
                                      7  segfile#=865 and
                                      8  segblock#=261032;

                                    MIN(EXT#)  MAX(EXT#)
                                    ---------- ----------
                                             0      63063

                                     

                                    But, if you look at the smon trace from yesterday it is working on extent 176590:

                                     

                                    *** 2014-01-22 02:24:47.928

                                    *** SESSION ID:(8.1) 2014-01-22 02:24:47.905

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

                                    PARSING IN CURSOR #2 len=94 dep=1 uid=0 oct=3 lid=0 tim=1598955320 hv=1839874543 ad='34c149b0'

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

                                    END OF STMT

                                    PARSE #2:c=0,e=2,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1598955320

                                    BINDS #2:

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

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

                                       value=74

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

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

                                       value=865

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

                                       bfp=80000001000e9b68 bln=24 avl=04 flg=05

                                       value=261032

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

                                       bfp=80000001000e9b38 bln=24 avl=04 flg=05

                                       value=176590

                                     

                                    I got the idea of SMON starting over from this blog post:

                                     

                                    https://blogs.oracle.com/AlejandroVargas/entry/after_production_upgrade_thoug

                                     

                                    Here is a relevent quote: "Shutdown abort in this case was a bad idea, on startup it forced smon to scan all extents already set, a slow and painful process... trace of smon showed that it scanned uet$ row by row reviewing all the job that was already completed"

                                     

                                    Hopefully this means that uet$/fet$ are not corrupt and we just need to clean this up through some normal process but we will see.

                                     

                                    - Bobby

                                    1 2 3 Previous Next