Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: ORA-1595 / ORA-1575 Errors
Roy
This is not a hardware issue, but rather a timeout for the ST enqueue. See details below on what you need to do to find the root cause.
HTH Pete
rspeaker_at_my-dejanews.com wrote:
> Hi,
>
> can anybody shed any light onto what might cause these 2 errors:
>
> Thu May 6 09:38:27 1999
> SMON: following errors trapped and ignored:
> ORA-01595: error freeing extent (536871660) of rollback segment (18))
> ORA-01575: timeout waiting for space management resource
>
> There have been no hardware errors reported by the OS. We are running
> Oracle 7.3.4 on AIX 4.2.1. I see these errors repeated over and over
> and over again.
>
> Thanks, any help is appreciated.
>
> Roy
>
> --== Sent via Deja.com http://www.deja.com/ ==--
> ---Share what you know. Learn what you don't.---
Article-ID: <Note:33567.1> Circulation: PUBLISHED (EXTERNAL) Folder: server.Rdbms.DBA.Locking Topic: ** Resolving Locking / Hanging Scenarios Platform: GENERIC Generic issue Subject: TECH: ORA-1575 Timeout waiting for Space Management Enqueue Author: RPOWELL.UK Creation-Date: 05-FEB-1996 16:32:44 Modified-Date: 18-JUN-1997 18:15:01 Document-Type: BULLETIN Impact: MEDIUM Skill-Level: ACCOMPLISHED Component: RDBMS Content-Type: TEXT/PLAIN Attachments: NONE
Introduction
When Oracle needs to perform a space management operation (such as
allocating temporary segments for a sort) the user session acquires a
special enqueue called the 'ST' enqueue. The ORA 1575 error indicates
that the session timed out waiting for this enqueue.
The 1575 error is usually reported by the SMON background process if
there is heavy demand for the 'ST' enqueue. The error is NOT a problem
in itself especially if it only occurs occasionally. However, if there are a number of 1575 errors being reported this implies that the system is spending a lot of time performing space management operations and the reason should be investigated. This article lists the main causes of contention for the ST enqueue.
Special Note
Information here is for Oracle7 only. Where an 'event' parameter is mentioned in this text then its use is version dependent so may not work in certain Oracle releases. You should place any such parameter alongside any other 'event' parameters in your init.ora file and contact Oracle support for confirmation that the event is valid for the release you areusing.
Unnecessary Sorting
If your application performs lots of disk sorts these can cause contention for the ST enqueue. It is generally advisable to eliminate on-disk sorts wherever possible. Use the "Sorts (disk)" figure from the utlbstat/utlestat report to determine if you are performing a lot of disk sorts. If so tune your application to perform less disk sorts. Eg: Eliminate any expensive 'sort-merge-join' operations from application SQL. Try to minimize storage allocation / freeing through minimal use of CREATE and DROP of tables and indexes. You should also consider other tuning measures that may reduce contention for the ST enqueue. These may include: - Increasing the <parameter:sort_area_size> - Using the TEMPORARY TABLESPACE feature introduced in Oracle 7.3 - Tuning your temporary tablespace to have a default storage that uses fewer 'large' extents for sorting rather than lots of small extents. - Set PCTINCREASE to zero for your temporary tablespaces. - Ensuring tables and indexes have sensible extent sizes and do not frequently need to extend by allocating additional extents.
Forward Coalescing
When a process frees up temporary extents then by default a forward
coalesce occurs. This can affect applications which frequently free
up space. Eg: Applications which do frequently perform lots of sorts.
You can inhibit this behavior in Oracle releases 7.1 onwards thus:
Add the line:
event="10268 trace name context forever, level 10" into your init.ora file for the instance. This must be placed next to any other 'event=' lines in the init.ora file if present.
General TEMP tablespace advise
SMON wakes up frequently to try and clean up free space and to coalesce contiguous free extents. From Oracle 7.1 onwards you can set the PCTINCREASE on a tablespace to ZERO to prevent SMON from attempting to coalesce free space for that tablespace. This is advisable for temporary tablespaces as there should be no need to coalesce as each request for space will ask for the same size extent. You should also ensure the default extent size is sensible for the sort operations you are likely to perform. You can change PCTINCREASE with the ALTER TABLESPACE command. From Oracle 7.3 onwards it is possible to mark a tablespace as 'TEMPORARY'. This uses a much improved method of handling sort space.
SMON in Parallel Server Environment
In Oracle 7.0 releases SMON performed 'cleanup' operations every 3 minutes. In a parallel server environment this is magnified as each instance has an SMON process attempting for perform cleanup operations. To help eliminate this problem: a) In Oracle 7.0.x you can add the line: event="10061 trace name context forever, level 10" into your init.ora file for all but one instance. This will stop SMON from cleaning up TEMP segments and from coalescing extents on all instances except one hence reducing contention. b) In Oracle 7.1.x you can add the lines: event="10061 trace name context forever, level 10" event="10269 trace name context forever, level 10" into your init.ora file for all but one instance. The first stops TEMP segment cleanup and the second stops automatic coalescing of free space by SMON.
Hanging or Slow System
If the database is running very slowly or hanging it is possible to
see repeated 1575 errors as a side effect of a different process holding the ST enqueue for a long period of time. Eg: It may be blocked from doing some operation while it is holding this enqueue. If this is the case see <OLS:9869022.61> for details of how to determine the cause of a hanging or slow database.Received on Mon May 10 1999 - 11:35:24 CDT