Database News You Can Use
A monthly newsletter for Oracle usersJuly 2006
From Database Specialists
Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 • Toll-Free: 1-888-648-0500
Ask DB Specialists: Queries that Run Out of Temp Space
You’re invited to submit your Oracle-related questions to us at firstname.lastname@example.org. Please include your name and telephone number in your email.
This month’s question comes to us from David in Florida: Every now and then a bunch of users will complain all at once that their queries are erroring out, and I’ll see a message in the instance alert log that says, “ORA-1652: unable to extend temp segment by 128 in tablespace TEMP.” I have increased the size of the TEMP tablespace by a factor of ten and this situation still happens sometimes. I can have 100 users on the system with no problem, and other times I will see the ORA-1652 error when there are only a few users on the system. How can I troubleshoot this?
Roger Schrag of the Database Specialists team responds: Oracle uses space in the temporary tablespace for sorting data sets too large to be sorted in memory. Sorting occurs in queries with ORDER BY or GROUP BY clauses, during index creation, and at other times as well. The temporary tablespace is a resource shared by all database users. If one user submits an unreasonable query that uses up all of the space in the temporary tablespace, other users can be adversely affected.
Sometimes a user will submit an ad-hoc query with a missing join condition, resulting in an enormous Cartesian product. In other situations an application can generate a dynamic SQL statement that is missing part of its WHERE clause. In these cases Oracle can be stuck in an impossible situation where the query will fail almost no matter how large you make the temporary tablespace. Enlarging the temporary tablespace in these cases will just cause Oracle to use up more CPU cycles before returning an ORA-1652 error.
Queries that use an unreasonable amount of temporary space can be hard to track down, especially in large complex systems with many concurrent users or lots of dynamically generated queries. However, Oracle’s event-based tracing facility can be very helpful for identifying these queries. Not a well-known feature, Oracle lets you designate that a trace file should be written when a specific error occurs. The following statement directs Oracle to write a trace file whenever an ORA-1652 error occurs:
ALTER SYSTEM SET EVENTS '1652 trace name ERRORSTACK level 3';
With this event set, Oracle server processes will write a trace file any time they find insufficient temporary space for a statement being processed. The trace file will contain the text of the statement that failed, along with a lot of other session state information. The trace file will be located on the database server in the directory specified by the user_dump_dest parameter.
You can learn more about Oracle’s event-based tracing facility by reading Metalink bulletin 77343.1. This bulletin primarily discusses SQL tracing, but does give an example of event-based tracing in order to debug an ORA-942 error.
Using Oracle’s event-based tracing facility, you can get the full text of every SQL statement that fails due to lack of temporary space. This can help the Oracle DBA to track down the source of unreasonable queries that attempt to use an unmanageable amount of temporary space.
An Australian fellow named Howard Rogers has quite a nice website dedicated to Oracle experts and newbies alike. The site is called “Dizwell Informatics,” and it contains some informative and thorough articles by Rogers as well as other contributors such as author/educator Jonathan Lewis. In addition, to Rogers’ blog musings, there are beefy topics such as:
Each of the above areas includes plenty of sub-topics.
Mark your calendar for the upcoming Northern California Oracle Users Group event taking place in San Ramon, California on Thursday, August 17. Steven Feuerstein will give a keynote presentation, along with a technical session on the testing process. Additional great technical sessions will be taking place throughout the day, including:
For more information and registration information, check out http://www.nocoug.org/next.html. If you can’t attend, keep an eye on the site for postings of white papers and presentations after the meeting here.
If you haven’t reviewed the Orafaq website (http://www.orafaq.com) in a while, it may be time to take a fresh look. You’ll find white papers, scripts, tools overviews, and active forums on a range of topics.
Plus, there is a new listing of recent articles on subjects such as:
You can access these articles and more at: http://www.orafaq.com/articles.