ORA-01652: unable to extend temp segment
This is a short explanation about one scenario where a SQL query can result in an ORA-01652 error, for the benefit of a database user instead of a DBA. The following has been tested on an Oracle10g (10.2.0.4) database.
This week during a routine check of a pre-production database I built, I found a couple of ORA-01652 errors in the alert log, so I proceeded to extract an AWR report over the timeframe of the error, in the hope that I could guess which SQL caused the error. But to simplify the scenario for this post, I will show a small demo case instead.
An ORA-01652 error typically means that a SQL query required more temporary segments than the temporary tablespace can provide. Temporary tablespace is typically used for sort operations, such as joins, index builds, ordering, computing aggregates (GROUP BY), and collecting optimizer statistics [source].
Let’s just show a simple example:
SQL> select * from mrt_alert order by alertname;
select * from mrt_alert order by alertname
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace FREDT_TEMP
The first thing that comes to mind might be report this error to your resident DBA, or simply extend the temporary tablespace to whatever is required. But how big do we need to extend this temporary tablespace to, so that our SQL query will work? Use Explain Plan.
SQL> explain plan for 2 select * from mrt_alert order by songname; Explained. SQL> SELECT * FROM TABLE(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 2703675187 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 320K| 153M| | 39661 (1)| 00:07:56 | | 1 | SORT ORDER BY | | 320K| 153M| 384M| 39661 (1)| 00:07:56 | | 2 | TABLE ACCESS FULL| MRT_ALERT | 320K| 153M| | 5405 (2)| 00:01:05 | ---------------------------------------------------------------------------------------- 9 rows selected.
The Explain Plan tells us the SORT ORDER BY operation requires 384MB of free temporary tablespace. The temporary tablespace is usually shared between other database users in the database. Remember to have uptodate statistics on the table, otherwise, the Explain Plan will show non-accurate information. In 10g, this is mostly done for you via the nightly dbms_stats scheduled job run.
Before shooting a request straight to our DBA to ensure the temporary tablespace have more than 384MB of free space, it’s necessary to just do a sanity check on our SQL query. For example:
1. How many rows are there in our table, so much that we need 384MB of space to sort?
2. Can we reduce the number of columns to view?
3. Can we limit our results by adding some conditions using the WHERE clause?
SQL> select count(*) from mrt_alert;
COUNT(*)
----------
320203
SQL> select bytes/1024/1024 as MB from user_segments
2 where segment_name='MRT_ALERT';
MB
----------
192
If we are only interested in one column for example, then we need less space for the SORT ORDER BY operation. As we can show below, we reduced the Temporary Segments required from 384MB to 19MB, but selecting only one column.
SQL> explain plan for 2 select alertname from mrt_alert order by alertname; Explained. SQL> SQL> SELECT * FROM TABLE(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 2703675187 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 320K| 7192K| | 7618 (2)| 00:01:32 | | 1 | SORT ORDER BY | | 320K| 7192K| 19M| 7618 (2)| 00:01:32 | | 2 | TABLE ACCESS FULL| MRT_ALERT | 320K| 7192K| | 5401 (2)| 00:01:05 | ---------------------------------------------------------------------------------------- 9 rows selected.
It is possible to further reduce space required for the SORT ORDER BY operation, by adding some conditions using a WHERE clause. First let’s start with the demonstration before explaining what’s going on.
SQL> explain plan for
2 select alertname from mrt_alert where alertname like 'A%' order by alertname;
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1920725646
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3895 | 89585 | 5308 (2)| 00:01:04 |
| 1 | SORT ORDER BY | | 3895 | 89585 | 5308 (2)| 00:01:04 |
|* 2 | TABLE ACCESS FULL| MRT_ALERT | 3895 | 89585 | 5306 (2)| 00:01:04 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ALERTNAME" LIKE 'A%')
14 rows selected.
We can see that the TempSpc column in our Explain Plan has disappeared. Shervin provided a very simple explanation here. Basically, when the disk based temporary tablespace is not required, the TempSpc column is not shown.
Metalink Note: 102339.1 Temporary Segments: What Happens When a Sort Occurs further explains “the sort area allocated inside the Program Global Area (PGA) is used first. If the sort operation needs additional memory, then the sorted rows are written to disk to free up the sort area so that it can be re-used for the remaining sort. This means that temporary segments are created.“. In simple words, if the sort can be done in memory, it won’t resort to disk.
So there, we have managed reduce the Temporary Segments usage from 384MB to 19MB, and with a WHERE clause, the SORT ORDER BY operation can be done entirely in memory, and we don’t even have to rely on Temporary Segments (i.e. 0MB). Therefore, we don’t have to talk to our DBA about adding more space to the Temporary Tablespace.
There is additionally one extra step we can do however, which can eliminate a SORT ORDER BY operation altogether. This is done by introducing a B*Tree index.
SQL> create index IX_MRT_ALERT_2 on MRT_ALERT(ALERTNAME);
Index created.
SQL> exec dbms_stats.gather_index_stats(user, 'IX_MRT_ALERT_2');
PL/SQL procedure successfully completed.
SQL> explain plan for
2 select alertname from mrt_alert where alertname like 'A%'
3 order by alertname;
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 249942460
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3895 | 89585 | 21 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IX_MRT_ALERT | 3895 | 89585 | 21 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ALERTNAME" LIKE 'A%')
filter("ALERTNAME" LIKE 'A%')
14 rows selected.
As can be observed from the Explain Plan, Oracle uses Index Range Scan to retrieve results that satisfy our WHERE clause. The result of an Index Range Scan returns results in an order – in this case, a VARCHAR2 column – alphabetical order. Therefore, Oracle can avoid a SORT ORDER BY operation as the results is already ordered.
There’s a caveat though, in Metalink Note: 67409.1 When will an ORDER BY statement use an Index, “Oracle explains that the index is only used when the column must be NOT NULL, otherwise, the index will not be considered“. It then gives an explanation for this behaviour which I won’t copy and paste here.
Conclusion
I guess I have presented an over-simplified scenario here, and the SQL query you are testing might be a lot more complex than this, but it is important to just do some sanity checks before asking a DBA to increase database resource capacity.
I have shown some steps where it is possible to reduce Temporary Segments usage, and with a clever usage of an index, to complete remove the need to perform a SORT. Having said all the above, if you do require the additional Temporary Segments, then by all means, go for it!
