How Can Temporary Segment Usage Be Monitored Over Time? (Doc ID 364417.1)

Goal

Suggest a method by which temporary segment usage be monitored over time

Such a process would aid greatly in investigating

ORA-1652 "unable to extend temp segment by %s in tablespace %s"

EXAMPLE

   Suppose we have a 90gb temporary tablespace (only one temp tablespace for this example)
    We also have enabled tracing for error 1652 using ALTER SYSTEM SET EVENTS '1652 TRACE NAME ERRORSTACK LEVEL 3';

       At time 1 ... a long ... complicated query starts running
         after several hours ... it has consumed 85gb of space ... and will continue running for a long time

       At time 2 (time 1 + several hours) another query runs and quickly consumes 5gb of space ... and generates an ORA-1652
          this session then crashes and releases the 5gb of space ... This will not cause the query started at time 1 .. or any other query to fail
          unless they also needed MORE temp space (ie a new extent) .. at exactly the same moment

          At this point .. the errorstack for 1652 will show the query started at time 2 ... not the BIG temp space consumer ... started at time 1

       At time 3 ... This note is used to setup the monitoring job to watch the temp segment usage over time ...
            the first update should show our query that has been running since time 1 ... which has consumed 85gbgb of space

           Now .. suppose while setting up the job ... the query from time 1 ... ends ... and frees up its 85gb of space ... then our job will not show
             the consumption ... as it is now freed

       At time 4 .. the same query that consumed the 85gb of space is run again

       At time 5 .. our job setup to monitor temporary space usage runs ... and it is noted that the Time 4 query is using 5gb of temp space

       At time 6 .. our job setup to monitor temporary space usage runs ... and it is noted that the Time 4 query is using 50gb of temp space

       At time 7 .. our job setup to monitor temporary space usage runs ... and it is noted that the Time 4 query is using 85gb of temp space

       At time 8 ... another query is started that quickly consumes the remaining 5gb of space ... and as a result .. an ORA-1652 occurs

            Again .. our errorstack trace will point to this query .. an not the query started at Time 4 ...

            The job that is running ... WILL show the session that consumed the 85gb of space

Once the 'top consumers' of space have been determined .. those query can be investigated for possible tuning to use less space ... and if they cannot be tuned then more space needs to be added to the temporary tablespace that is running out of space

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.