Comments in-line
You were spot on
These batch jobs perform large sort operations and were secheduled to run
in series but have been split up to run in parallel (8 cpu's of which 7
were idle during the batch window is BAD).
This obviously meant more load on temp tablespace (and rollback but they
can handle it better)
I/O subsytem is stressed as it is but we took an hour and a half of the
total batch job.
Excellent answer THX
Jack
"Tim Gorman"
<[EMAIL PROTECTED] To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
m> cc: (bcc: Jack van
Zanen/nlzanen1/External/MEY/NL)
Sent by: Subject: Re: ORA-1575
[EMAIL PROTECTED]
13-08-2002 14:23
Please respond to
ORACLE-L
Jack,
Contention for "ST" enqueue really isn't necessary anymore, especially from
v8.1.x upwards but even from any version. Something used by those batch
jobs is allocating extents too quickly. I suspect it has to do with
sorting
and TEMP. It could be other things, but let's go with that suspicion...
A couple of guesses about this environment:
* your TEMP tablespace (or whatever tablespace is designated as TEMP in
DBA_USERS) is type PERMANENT (not TEMPORARY, which became available with
version 7.3.x)
>>> Temp tablespace is TEMPORARY
* or -- one of the users which those batch programs are connected as
are
using the wrong TEMP tablespace (check DBA_USERS)
>>> User is Using the Temp tablespace
* the INITIAL and NEXT of the TEMP tablespace are really small (i.e.
40K
or so)
>>> Initial and next is 1M (same as sort_area_size for regualr users but
batch uses 150M)
* the PCTINCREASE of the TEMP tablespace is 0 or 1
>>> PCTINCREASE is 0 (is this Bad?)
* the SORT_AREA_SIZE is much larger than the INITIAL/NEXT settings for
the TEMP tablespace
>>> Batch job uses 150M and initial/next = 1M
If most of this is true, then what we have is an environment where
concurrent activity on large sorts is guaranteed to bottleneck on the "ST"
enqueue, causing ORA-01575 warnings in the "alert.log". The reason is that
the SMON process, which is assigned to wake up and do some space-cleanup
activities every once in a while, is a real gentleman about it. If it
can't
do it's space-cleanup activities because someone else is dominating that
enqueue, it doesn't stay enqueued and just wait for the lock; it complains
to the alert.log file and gives up. As a matter of fact, SMON isn't so
much
a gentleman as a whining little brat, when you think about it... :-)
...anyway, ORA-01575 isn't so much an "error" as a "warning" from SMON that
something isn't right, and that it couldn't do its job. It is a symptom...
Resolution(s):
* go to Oracle8i and use locally-managed tablespaces, especially
TEMPORARY locally-managed tablespaces
>>> Next October hopefully
* if you can't upgrade to Oracle8i and migrate the tablespaces (most
likely), then:
** ensure that *every* account is using the correct tablespace as a
temporary tablespace (nobody should be using SYSTEM)
** ensure that temporary tablespace is created as type TEMPORARY,
not PERMANENT (note: this can be buggy in some versions of 8.0.5, so test
this first!)
** ensure that the default storage settings of INITIAL/NEXT are
equal to each other, that PCTINCREASE is 0, and that INITIAL/NEXT are at
least equal to or greater than value of SORT_AREA_SIZE
>>> I can modify this one easily thx
The first and third recommendations are the most important. The reason for
the first recommendation should be obvious, but the third is a little more
subtle. If SORT_AREA_SIZE is huge but INITIAL/NEXT in temp are small, then
every large sorting operation that exceeds SORT_AREA_SIZE is going to
result
in dozens (if not hundreds) of very rapid extent allocations, followed
inevitably (as the sort completes) by very rapid extent deallocations. If
you have one process doing this, you may not have problems. But, if you
have several processes doing this, you have a bottleneck on the "ST"
enqueue, of which there is only one. Any process performing
space-management using dictionary-managed tablespaces must first acquire
the
"ST" enqueue. If INITIAL/NEXT of temp tablespace is 2-4 times the size of
SORT_AREA_SIZE, then extent allocation (and subsequent deallocation) will
be
much more infrequent, with more time spent sorting and less time managing
sort space. Making the temp tablespace of type TEMPORARY only improves
that
situation even more (if the feature works properly in your version)...
---
Of course, I could be all wrong and the problem isn't occurring in TEMP and
sorting after all. It could be rollback segment extents sized too small;
it could also be tables and indexes affected by those batch processes with
extents sized too small. Whatever it is, the ultimate solution is
locally-managed tablespaces in Oracle8i, but in Oracle8 and below you have
to size the extents of the affected objects more carefully and
appropriately...
Hope this helps...
-Tim
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, August 13, 2002 1:48 AM
Hi All,
Oracle 8.0.5
AIX 4.3.3
We have a database that showed an ORA-1575 every minute for 30 minutes this
morning.
This was just after startup when a whole bunch of batch processes kick in
as well
Documentation just says retry the operation (smon tried every minute and
eventually succeeded)
*****************************
ORA-01575 timeout waiting for space management resource
Cause: Failed to acquire necessary resource to do space management.
Action: Retry the operation.
******************************
My feeling is that I can safely dismiss the idea that this is a serious
matter, but would like to run this one by you guys as I have not had this
before.
TIA
Jack
===================================================================
De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan
derden is, behoudens voorafgaande schriftelijke toestemming van Ernst &
Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en
volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch
voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een
verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten
worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden.
Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u
vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender
en het origineel en eventuele kopieën te verwijderen en te vernietigen.
Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene
voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De
algemene voorwaarden worden u op verzoek kosteloos toegezonden.
=====================================================================
The information contained in this communication is confidential and is
intended solely for the use of the individual or entity to whom it is
addressed. You should not copy, disclose or distribute this communication
without the authority of Ernst & Young. Ernst & Young is neither liable for
the proper and complete transmission of the information contained in this
communication nor for any delay in its receipt. Ernst & Young does not
guarantee that the integrity of this communication has been maintained nor
that the communication is free of viruses, interceptions or interference.
If you are not the intended recipient of this communication please return
the communication to the sender and delete and destroy all copies.
In carrying out its engagements, Ernst & Young applies general terms and
conditions, which contain a clause that limits its liability. A copy of
these terms and conditions is available on request free of charge.
===================================================================
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack van Zanen
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tim Gorman
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
===================================================================
De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan
derden is, behoudens voorafgaande schriftelijke toestemming van Ernst &
Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en
volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch
voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een
verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten
worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden.
Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u
vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender
en het origineel en eventuele kopieën te verwijderen en te vernietigen.
Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene
voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De
algemene voorwaarden worden u op verzoek kosteloos toegezonden.
=====================================================================
The information contained in this communication is confidential and is
intended solely for the use of the individual or entity to whom it is
addressed. You should not copy, disclose or distribute this communication
without the authority of Ernst & Young. Ernst & Young is neither liable for
the proper and complete transmission of the information contained in this
communication nor for any delay in its receipt. Ernst & Young does not
guarantee that the integrity of this communication has been maintained nor
that the communication is free of viruses, interceptions or interference.
If you are not the intended recipient of this communication please return
the communication to the sender and delete and destroy all copies.
In carrying out its engagements, Ernst & Young applies general terms and
conditions, which contain a clause that limits its liability. A copy of
these terms and conditions is available on request free of charge.
===================================================================
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack van Zanen
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
