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: <rspeaker_at_my-dejanews.com>
Date: Wed, 12 May 1999 18:10:56 GMT
Message-ID: <7hcg7d$h27$1@nnrp1.deja.com>


Pete,

thanks ... this is good information.

-Roy

In article <37370ACB.EB5B8650_at_us.oracle.com>,   Pete Sharman <psharman_at_us.oracle.com> wrote:
> This is a multi-part message in MIME format.
> --------------175BF1F1AAC1661FA667965D
> Content-Type: text/plain; charset=us-ascii
> Content-Transfer-Encoding: 7bit
>
> 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.
>
> --------------175BF1F1AAC1661FA667965D
> Content-Type: text/x-vcard; charset=us-ascii;
> name="psharman.vcf"
> Content-Transfer-Encoding: 7bit
> Content-Description: Card for Pete Sharman
> Content-Disposition: attachment;
> filename="psharman.vcf"
>
> begin:vcard
> n:Sharman;Peter
> tel;fax:+1.650.633.1669
> tel;work:+1.650.607.0109
> x-mozilla-html:FALSE
> url:http://www.oracle.com
> org:Worldwide Internal Services Education;Oracle Corporation
> adr:;;500 Oracle Parkway M/S OPL-B1024;Redwood
Shores;California;94065;USA
> version:2.1
> email;internet:psharman_at_us.oracle.com
> title:Project Manager
> note;quoted-printable:=0D=0A=0D=0A **** The statements and opinions
expressed here are my **** =0D=0A **** own and do not necessarily

represent those of          **** =0D=0A **** Oracle
Corporation.                                             =20
****=0D=0A=0D=0A"Controlling application developers is like herding cats." =0D=0AKevin Loney, ORACLE DBA Handbook =0D=0A"Oh no it's not! It's much harder than that!" =0D=0ABruce Pihlamae, long term ORACLE DBA
> x-mozilla-cpt:;0
> fn:Pete Sharman
> end:vcard
>
> --------------175BF1F1AAC1661FA667965D--
>
>

--== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.--- Received on Wed May 12 1999 - 13:10:56 CDT

Original text of this message

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