How Can Temporary Segment Usage Be Monitored Over Time?
Last updated on JULY 21, 2017
Applies to:Oracle Database - Enterprise Edition - Version 22.214.171.124 and later
Information in this document applies to any platform.
***Checked for relevance on 21-Jul-2017***
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"
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
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms