Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: ORA-1595 / ORA-1575 Errors

Re: ORA-1595 / ORA-1575 Errors

From: Pete Sharman <psharman_at_us.oracle.com>
Date: Mon, 10 May 1999 09:35:24 -0700
Message-ID: <37370ACB.EB5B8650@us.oracle.com>


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 are
using.

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US