Monitoring TEMP Space Usage in Oracle RAC Databases

It’s a common problem encountered by many DBAs on a daily basis; developers writing queries which run out of TEMP space. It can come at the worst of times, too…

For example, you’ve run in the scripts many times overnight into a development environment and they worked fine. They are signed off and run into PROD. However, when running them into PROD they run out of TEMP space because some other processes were contending for TEMP space.
TEMP Space Usage in Oracle

I use this query to monitor TEMP space. We have an OEM job which runs every 5 minutes during selected times and then alerts us when the TEMP space reaches a certain threshold.

SELECT   S.INST_ID,S.SID || ',' || S.SERIAL# SID_SERIAL,              S.USERNAME,              S.OSUSER,              P.SPID,              S.MODULE,              S.PROGRAM,              SUM (T.BLOCKS) * TBS.BLOCK_SIZE /1024/1024 MB_USED,              T.TABLESPACE,              COUNT(*) SORT_OPS FROM  GV$SORT_USAGE T,              GV$SESSION S,              DBA_TABLESPACES TBS,              GV$PROCESS P WHERE T.SESSION_ADDR = S.SADDR AND      S.PADDR = P.ADDR AND      T.TABLESPACE = TBS.TABLESPACE_NAME AND      S.INST_ID=P.INST_ID GROUP BY S.INST_ID,S.SID,              S.SERIAL#,              S.USERNAME,              S.OSUSER,              P.SPID,              S.MODULE,              S.PROGRAM,              TBS.BLOCK_SIZE,              T.TABLESPACE ORDER BY MB_USED DESC,              SID_SERIAL;

You can also use the above query to insert into a table so that it measures how much TEMP you are using at regular intervals, say, every minute then you can see at which stages it maxes out during your run.

It can be easy to forget about TEMP space usage, but when it goes wrong it usually goes wrong badly. After all, if your job is using that much TEMP space it’s probably doing a lot of work and will have taken a long time to get to the point of failure. And if it has to rollback a load of transactions when it fails it can take a long time to complete.
Auto-extending Temp Files

If you are not sure how much temporary tablespace your job is going to use, and therefore how large you need to size your temp tablespace, you could change the tempfiles to have AUTOEXTEND on so as to prevent the dreaded “ORA-1652: unable to extend temp segment” error message. This will help you with your development runs so you can gauge how much TEMP you need.

Next I’d like to talk a little bit about the PGA because it is very closely associated with TEMP space usage in the database.

What is the PGA

Another factor that you should consider is the size of your Program Global Area (PGA). The PGA is the area of memory (RAM) in which your session sorts data before it is returned to the user. Say, for example, you have the following query:

SELECT ID, COUNT(*) FROM TABLE GROUP BY ID ORDER BY COUNT(*) DESC;

The database will have to organise this data somewhere before returning the results because the user has requested it grouped and ordered. And this is where the PGA comes in handy. In this case the data will be taken from “table” and stored in the PGA temporarily, using it to group and order the result set. If “table” is not very large it might fit into your PGA in which case the results can be totally held in memory which results in faster processing and returning of the data to the user.

However, if the amount of space required to store all of the data you need to sort is greater than the size of your PGA there is a problem. That problem is resolved by using an overspill area on disk, also known in Oracle as the TEMPORARY (TEMP) tablespace. The data can be read into and out of the PGA and TEMP tablespace in order to perform the sorting required and return a final result set to the user. You should be aware that if you have differently sized PGAs on your environments this can affect when the TEMP tablespace has to be used and cause performance degradation if it has to use TEMP on one environment and not on another because writing out to disk is always slower than using memory (RAM) for an operation.

NOTE: If you look at your AWR report you will see there is an area (at least on 11g there is) called “PGA Aggr Target Histogram” which shows the number of “Optimal Execs”, “1-Pass Execs” and “M-Pass Execs”. Optimal executions are what you want because it means that the sorts were done entirely in memory, without having to go over the data more than once. If you have a lot of passes over the data it can become a very inefficient way of sorting the data.
What size is your PGA?
So, how do you check the size of your PGA? There is an initialisation parameter called PGA_AGGREGATE_TARGET which controls the size of memory allocated for its use. You can alter the setting using the following command:

      ALTER SYSTEM SET PGA_AGGREGATE_TARGET = nG SCOPE=SPFILE;
Note: This means you will have to restart the database before the change take effect or you can specify SCOPE = BOTH to change it on the fly, although I haven’t had that much success with doing it this way.
.It should be noted that one user can rarely use all of the PGA. The reason for this is because the Oracle server is supposed to be a multi-user environment and if one process was allowed to use all of the memory allocated to the PGA there would be nothing left for anyone else. As such, there is a limit to what one user can utilise at any one time. Personally, I would monitor the usage of your PGA to see what it maxes out at while running your process.

How Much PGA are you Using
If you have set the PGA_AGGREGATE_TARGET parameter then you are using what’s called automatic PGA memory management which means that you are setting on value and then allowing the database to allocate the memory dynamically between the different sorting areas within the PGA. This has been the recommended way since Oracle 10g and I’d say is the best way to go seeing as it can be very difficult to use manual memory management correctly and it’s far less flexible for different load profiles.

To find out about what processes are using your PGA you can look in the following views:
V$PGASTAT
V$SQL_WORKAREA
V$SQL_WORKAREA_ACTIVE
V$PROCESS
If you want to have a look at the PGA size on a per process/session basis then you can use the following query:

             

SELECT  SESS.SID

      , SESS.USERNAME

      , SESS.PROGRAM

      , SESS.MACHINE

      , TRUNC(PGA_USED_MEM/1024/1024) PGA_USED_MB

      , TRUNC(PGA_ALLOC_MEM/1024/1024) PGA_ALLOC_MB

      , TRUNC(PGA_FREEABLE_MEM/1024/1024) PGA_FREEABLE_MB

      , TRUNC(PGA_MAX_MEM/1024/1024) PGA_MAX_MB

FROM V$PROCESS PROC, V$SESSION SESS

  WHERE PROC.ADDR = SESS.PADDR

AND BACKGROUND IS NULL

ORDER BY PGA_ALLOC_MB DESC;

OK, so I think we’ve covered a reasonable amount with regard to the PGA so let’s move on to look at the TEMPORARY tablespace.

How to Create a Temporary Tablespace
Under most circumstances you would create the temporary tablespace at the time of database creation and it would be called TEMP. However, sometimes there are reasons why you might want to create a different temporary tablespace, such as:

You have a large running query and want to use a separate Oracle temporary tablespace for it to use
You’d like to move the TEMP tablespace without having to restart the database in a MOUNT state to rename the existing temporary database files
A file has become corrupted in your existing temporary tablespace and you want to re-create it
To create a TEMPORARY tablespace you can use the following syntax:
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '<path>\file_name' SIZE xxG AUTOEXTEND OFF;
Obviously you could put autoextend on if you wish, but that’s just an example of one way how to create the tablespace. I mentioned above that you might like to move the TEMP files from one location to another. This is possible by creating a new temporary tablespace first, then assigning that TEMP tablespace as the default for the database and then dropping the old one. Here is an example of how you could do that, assume that you already have a temporary tablespace in your database called TEMP.

How to Assign a Default Temporary Tablespace

CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE 'D:\Oracle\DB\TEMP01.DBF' SIZE 4G AUTOEXTEND OFF;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

You have to create the new TEMP tablespace first otherwise you will hit the error “ORA-12906: cannot drop default temporary tablespace“. If you really want to you could repeat the process so that your temporary tablespace goes back to being called TEMP rather than TEMP2.
When changing the default temporary tablespace of the database you should be aware that existing users who have their temporary tablespace set to the original tablespace name will remain that way. Therefore, you should either re-assign all of the users to the new TEMP2 tablespace or change back to a temporary tablespace with the same name as the original.
Checking the Current Size of the TEMPORARY Tablespace
This is the query that I use to check what size my TEMP tablespace is within Oracle:

SET LINES 120

SET PAGES 100

COL FILE_NAME FOR A50

BREAK ON REPORT;

COMPUTE SUM LABEL "TOTAL" OF "MB" ON REPORT;

COMPUTE SUM OF "MMB" ON REPORT;

SELECT FILE_ID

     , FILE_NAME

     , TABLESPACE_NAME

     , BYTES/1024/1024 MB

     , MAXBYTES/1024/1024 MMB

     , AUTOEXTENSIBLE

FROM DBA_TEMP_FILES

  ORDER BY FILE_NAME;
How to Drop a TEMP File
You can drop an individual temporary database file from a TEMP type tablespace in your Oracle database using the following command:
ALTER DATABASE TEMPFILE '<tempfile_location>' DROP INCLUDING DATAFILES;
Note: If you drop all of the tempfiles from your TEMP tablespace you might encounter the error ORA-25153: Temporary Tablespace is Empty so watch out for that one and always make sure that you have temporary files available for use.
.If the tempfile is currently being used you will get an error stating that you cannot drop the file at that time, so you will have to wait or kill any transactions which are using it. I have found in the past that when I drop the tempfile, although it no longer shows as being part of the database in the data dictionary, it does not get dropped physically from the file system. There is a workaround which does work sometimes which is to create another TEMP file to overwrite the one you just dropped with an identical name and then drop it again. For example:

ALTER DATABASE TEMPFILE 'D:\Oracle\DB\TEMP01.dbf' DROP INCLUDING DATAFILES;

ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\Oracle\DB\TEMP01.dbf' SIZE 4G;

ALTER DATABASE TEMPFILE 'D:\Oracle\DB\TEMP01.dbf' DROP INCLUDING DATAFILES;

Hopefully this will remove the file from the file system this time.