| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Error freeing extent of rollback segment
There are a couple of published articles from Support that may be of use in
diagnosing the problem. I've pasted them below.
HTH. Pete
Article-ID: <Note:18973.1>
Alias: OERR:ORA.1575
Circulation: PUBLISHED (EXTERNAL)
Folder: server.Rdbms.Reference.ErrorMessages
Topic: ORA Errors (Oracle7 Server)
Platform: GENERIC Generic issue
Subject: OERR: ORA 1575 "timeout waiting for space management
resource"
Author: AUTO
Creation-Date: 05-FEB-1998 10:23:41
Modified-Date: 10-JUN-1999 11:02:44
Document-Type: REFERENCE
Impact: MEDIUM
Skill-Level: ACCOMPLISHED
Component: RDBMS Server 07 - 08
Content-Type: TEXT/PLAIN
Attachments: NONE
Error: ORA 1575
Text: timeout waiting for space management resource
Cause: Failed to acquire necessary resource to do space management. Action: Retry the operation.
Explanation:
This is just a timeout error when waiting for the ST (Space Management Transaction) enqueue.
Another process is holding the ST enqueue and the session reporting the ORA 1575 has timed out waiting for it to be released.
Further investigation is required to determine what is holding the ST enqueue and why it has not been released.
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 aquires 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 dependant 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 minimise 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 behaviour 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.
Known Bugs:
~~~~~~~~~~~ Introduced Fixed
Bug:261328 SQL*Loader holding ST too long 7.1.3 7.2.2
Bug:209328 SMON only waits 5 seconds after 1575 error 7.2.2
Articles:
<Note:31116.1> Manually Coalescing free space in Oracle7
<OLS:9869022.61> Diagnosing Database Hanging Issues
<PR: 1012431.6> Overview of Database Fragmentation
<Note:16752.1> Oracle7 Tuning with utlbstat/utlestat
Lee Ming Fai wrote:
> We found the following error message in the SMON trace log at our database > peak hour: > > SMON: following errors trapped and ignored: > ORA-01595: error freeing extent (10476948) of rollback segment (8)) > ORA-01575: timeout waiting for space management resource > > After several times of the above error messages, then a PROCESS STATE is > displayed. > > What's the problem with our database? > How to solve the above problem?
--
Regards
Pete
Received on Fri Jul 23 1999 - 11:06:02 CDT
 |
 |