Determining Oracle TEMP Size Usage!!

 Author: Tom Simpton

Sometimes we face a scenario where our queries that keeps on working fine for months and years someday causes a havoc on our system by generating a large TEMP files. Such huge TEMP generation puts us into trouble as each time our job fails because it is unable to extend TEMP tablespace and what happens next is that we keep on finding some effective solution that can quickly help us sort out the problem.

One such scenario was faced by me last night where a simple query that used hash join on 3 medium sized tables generated 50GB of temp. Quickly what I did was to analyze the explain plan that used ordinary data amongst which I found two database tables with no statistics at all. The next step was to gather statistics on that tables and then rechecking the explain plan. Finally I found the culprit which was that the estimated number of rows was changed and having some more data than estimated made Oracle behaving like this.

Below steps helps you determine the TEMP size generated by your query without the need to actually run the SQL.

• Log in to your database with the same user.
• Get Explain Plan. It will help you analyze the execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements.
• Run SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
• If TEMP is generated by your query then a column TempSpc is seen in the execution plan. The TempSpc shows the amount of TEMP that is expected to be generated.

In most cases this solves our problem. However sometimes luck is not with us and we are unable to find any TempSpc column in the execution plan. In general there are two reasons behind this problem.

First is that our SQL statement does not require temporary tablespace and we have enough dedicated memory (PGA) to run all the operations. In such scenario the execution plan for SELECT * FROM DBA_TABLES does not have any TempSpc column because all operation can fit in existing memory.

The second reason can be that your explain plan table is old and it does not have the column to TEMP SPACE USAGE. You can update the plan by running below commands

• DROP TABLE PLAN_TABLE
• @$ORACLE_HOME/RDBMS/ADMIN/UTLXPLAN.SQL

Related Articles:

1. DBA Tips for Oracle Temp Tablespace Growth Issue!!
2. Inside Oracle Temporary Tablespaces!!
3. Great Tips on solving Temporary Tablespace Problems!
4. Inside Oracle Temporary Tables!!

Click here for more Oracle Articles

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Inside Oracle 11g Adaptive Cursor Sharing Feature!!
   Performance boost up with Oracle row re-sequencing!!
   Oracle 11g Enhanced Optimizer Statistics Maintenance!!
   Be cautious while using JOIN Order