| 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: Oracle7.1 space management problem.
Piero
1575 is a timeout for the ST enqueue. Here's a note published by Support on it.
HTH Pete
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 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.
piero stanchi wrote:
> Hi everybody.
> I have an oracle 7.1.6 on HPUX B.10.01. I found in smon log files the
> following error:
> ORA-01575: timeout for space management resource
> I do not know what to start with: recreate tables, indexes, change
> storage clauses... Can it be a problem with HPUX?
> Thank you for any help.
> Piero
>
--
Regards
Pete
Peter Sharman Email: psharman_at_us.oracle.com WISE Course Development Manager Phone: +1.650.607.0109 (int'l) Worldwide Internal Services Education (650)607 0109 (local)San Francisco
SQL> select standard_disclaimer, witty_remark 2 from company_requirements;
Opinions are mine and do not necessarily reflect those of Oracle Corporation
"Controlling application developers is like herding cats."
Kevin Loney, ORACLE DBA Handbook
"Oh no it's not! It's much harder than that!"
Bruce Pihlamae, long term ORACLE DBA
Received on Thu Mar 25 1999 - 11:46:08 CST
 |
 |