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 [EMAIL PROTECTED] 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 <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] wrote:
Hi all:logfile.
I'm seeing the ora-01575 error in the alert
The article on the metalink refers to theparameter
which I think is obsolete in the ORacle version weare
running (8.1.7). What does this error refer to?Any
thoughts? references?design software
thanks
gene
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site
http://sitebuilder.yahoo.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED]
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: [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).
__________________________________ 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: [EMAIL PROTECTED]
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: [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).
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: [EMAIL PROTECTED]
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: [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).
