Oracle-L: Re: ORA-1575

From: Tim Gorman <Tim_at_SageLogix.com>
Date: Tue, 13 Aug 2002 04:23:23 -0800
Message-ID: <F001.004B2856.20020813042323@fatcity.com>

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)
or -- one of the users which those batch programs are connected as are using the wrong TEMP tablespace (check DBA_USERS)
the INITIAL and NEXT of the TEMP tablespace are really small (i.e. 40K or so)
the PCTINCREASE of the TEMP tablespace is 0 or 1
the SORT_AREA_SIZE is much larger than the INITIAL/NEXT settings for the TEMP tablespace

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
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

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" <ORACLE-L_at_fatcity.com>
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: nlzanen1_at_EY.NL

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: ListGuru_at_fatcity.com (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: Tim_at_SageLogix.com

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: ListGuru_at_fatcity.com (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).

Received on Tue Aug 13 2002 - 07:23:23 CDT