Re: ora 1575?

I'm very sorry. By some error I never got this message sent. So here it is, over a month too late. Fantastic...

Mogens

Ah, good to be back online with Tim Gorman on the old and wonderful 1575.

1575 was introduced in 7.1. Not as an error, because the code that creates this error has been around for many years before that. 1575 was introduced to signal an unpleasant wait situation for the ST lock/enqueue - a warning to the DBA.

Used extents (in UET$) and free extents (in FET$) are managed "together", meaning that 1) if you want to delete a record in UET$ and insert it in FET$ (that means an extent has been dropped/freed), 2) delete a record in FET$ and insert it in UET$ (extent has been allocated) or 3) delete a bunch of records in FET$ and inserting only one with the summary information in the same FET$ (coalescing extents) - you have to make sure that nobody else is messing with UET$/FET$ at the same time.

So Oracle takes out the massive ST enqueue on both UET$ and FET$ while it performs 1, 2 or 3 mentioned above (and probably some other things I don't recall). If somebody else tries to get the ST enqueue while it's still being held by another session, you'll get the 1575 signalled in the alert log - in order to simply notify you that there has been queueing on the ST lock.

As long as you have DMTs you risk getting 1575. It might be possible to get it with LMTs, too, but I haven't seen it personally (which is information without value - there are so many things I haven't seen yet, like lizards playing chess or Cary taking a quick shower).

Temporary tablespaces (in 7.3?) replaced the ST enqueue with a latch per temp tablespace (this helped a lot in OPS environments).

Management manouvres of various kind, like having standard sizes of extents, not coalescing ever (hence the 7.1 change whereby a tablespace with pctincrease=0 didn't get coalesced), etc. also helped.

But it was LMTs that finally solved it. I thought. Until this thread.

So now I'm curious as to what is happening here.

Mogens

Tim Gorman wrote:

>Tanel hit the nail on the head. In the past, ORA-01575 was usually
>associated with temporary tablespaces that were DMT and not tablespace type
>"TEMPORARY" (which started in Oracle7.3). First and foremost, please make
>sure you are using a TEMPORARY tablespace which is locally-managed and uses
>TEMPFILEs...
>
>It might be interesting to monitor V$LOCK for TYPE = 'ST' to see what
>sessions are holding this enqueue. If the activity is too transient,
>perhaps querying V$SESSION_EVENT where EVENT = 'enqueue' might indirectly
>imply which sessions have waited on an enqueue (not necessarily "ST",
>thought!) sometime in the past...
>
>
>
>on 8/13/03 7:04 AM, Tanel Poder at tanel.poder.003_at_mail.ee wrote:
>
>
>
>>Hi!
>>
>>You can always schedule alter tablespace coalesce's during low usage time.
>>But you should check whether you have adjacent free extents in your
>>tablespaces at all? If you're not doing lot's of dropping or truncating
>>objects, then you shouldn't have. Thus no need for coalesce either. Just
>>check that all of your sort segments go to the temp tablespace (which should
>>be in temporary mode, preferrably LMT as well).
>>
>>Tanel.
>>
>>
>>
>>
>>>thanks for the info. We do have a number of DMTS in
>>>the database. Three of them have pct_increase of 50%,
>>>the rest - 0. Should I consider changing the
>>>pct_increase to 0 in all tablespaces in order to get
>>>rid of this ora 1575? Wouldn't I want to have an
>>>automatic coalesce process for the DMTS though?
>>>
>>>thank you
>>>
>>>Gene
>>>--- Tim Gorman <tim_at_sagelogix.com> wrote:
>>>
>>>
>>>>Haven't seen this error since Oracle7...
>>>>
>>>>If the message is hitting the "alert.log", then
>>>>chances are good it is
>>>>coming from SMON. SMON is attempting to acquire the
>>>>"ST" (a.k.a. Space
>>>>transaction) enqueue in preparation for coalescing
>>>>free space in some
>>>>tablespaces. However, if it is unable to acquire
>>>>"ST" after a couple
>>>>seconds, it times out and issues ORA-01575 to the
>>>>alert.log.
>>>>
>>>>So, based on experiences from 6-7 years ago:
>>>>
>>>> * do you have a lot of "dictionary-managed"
>>>>tablespaces?
>>>> * do these DMT's have default PCTINCREASE
>>>>non-zero, thus attacting
>>>> SMON to do coalescing?
>>>>
>>>>If so, I'd suggest going to "locally-managed"
>>>>tablespaces if at all
>>>>possible...
>>>>
>>>>
>>>>
>>>>on 8/12/03 12:44 PM, Gurelei at gurelei_at_yahoo.com
>>>>wrote:
>>>>
>>>>
>>>>
>>>>>Hi all:
>>>>>
>>>>>I'm seeing the ora-01575 error in the alert
>>>>>
>>>>>
>>>>logfile.
>>>>
>>>>
>>>>>The article on the metalink refers to the
>>>>>
>>>>>
>>>>parameter
>>>>
>>>>
>>>>>which I think is obsolete in the ORacle version we
>>>>>
>>>>>
>>>>are
>>>>
>>>>
>>>>>running (8.1.7). What does this error refer to?
>>>>>
>>>>>
>>>>Any
>>>>
>>>>
>>>>>thoughts? references?
>>>>>
>>>>>thanks
>>>>>
>>>>>gene
>>>>>
>>>>>__________________________________
>>>>>Do you Yahoo!?
>>>>>Yahoo! SiteBuilder - Free, easy-to-use web site
>>>>>
>>>>>
>>>>design software
>>>>
>>>>
>>>>>http://sitebuilder.yahoo.com
>>>>>
>>>>>
>>>>--
>>>>Please see the official ORACLE-L FAQ:
>>>>http://www.orafaq.net
>>>>--
>>>>Author: Tim Gorman
>>>> INET: tim_at_sagelogix.com
>>>>
>>>>Fat City Network Services -- 858-538-5051
>>>>http://www.fatcity.com
>>>>San Diego, California -- Mailing list and web
>>>>hosting services
>>>>
>>>>
>>>>
>>>---------------------------------------------------------------------
>>>
>>>
>>>>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).
>>>>
>>>>
>>>__________________________________
>>>Do you Yahoo!?
>>>Yahoo! SiteBuilder - Free, easy-to-use web site design software
>>>http://sitebuilder.yahoo.com
>>>--
>>>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>>>--
>>>Author: Gurelei
>>> INET: gurelei_at_yahoo.com
>>>
>>>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>>>San Diego, California -- Mailing list and web hosting services
>>>---------------------------------------------------------------------
>>>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).
>>>
>>>
>>>
>
>
>

Hesin e-postur er kannaður fyri virus av Føroya Tele.

This e-mail was virus scanned by Faroese Telecom.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?=
  INET: mln_at_miracleas.dk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).