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 [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: 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: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http
Re: ora 1575?
information without value - there are so many things I haven't seen yet, like lizards playing chess or Cary taking a quick shower). oh the questions and thoughts this brings to mind! as in: has Mogens SEEN Cary taking a shower? or does he infer that Cary takes long showers by the amount of time Cary is absent from a room and the degree of wetness of Cary's hair when he returns? How quick is a quick shower? and, Cary is so definitely the epitome of the cute American boy next door -- too bad that I've met his wife and like her, the thoughts of him taking a shower could be interesting! --- Mogens_Nørgaard [EMAIL PROTECTED] wrote: 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 __ 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: Rachel Carmichael 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).
Re: ora 1575?
Well, no I haven't seen him actually take a shower, but one must hope that the sound of running water for 42 minutes followed by silence for another 21 minutes must mean that water has run down Cary and not just down the drain. And although I haven't seen him take the shower, we're many OakTable members who have seen him go into the bathroom and come out of bathroom - because we were all waiting for him to finish so the other 15 people could get their 42 seconds showers that they were entitled to. Cary is, indeed, a clean guy. Mogens Rachel Carmichael wrote: information without value - there are so many things I haven't seen yet, like lizards playing chess or Cary taking a quick shower). oh the questions and thoughts this brings to mind! as in: has Mogens SEEN Cary taking a shower? or does he infer that Cary takes long showers by the amount of time Cary is absent from a room and the degree of wetness of Cary's hair when he returns? How quick is a quick shower? and, Cary is so definitely the epitome of the cute American boy next door -- too bad that I've met his wife and like her, the thoughts of him taking a shower could be interesting! --- Mogens_Nørgaard [EMAIL PROTECTED] wrote: 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 __ 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: =?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).
Re: ora 1575?
Well, regardless of Mr. Millsap's hygiene and the details of how he maintains it, you *have* seen an unretouched photograph of a lizard playing chess, with my son... ...and the lizard was cheating by taking too much time to make his first move and eventually forfeited. Never turn your back on a reptile... on 9/13/03 4:19 PM, Mogens Nørgaard at [EMAIL PROTECTED] wrote: Well, no I haven't seen him actually take a shower, but one must hope that the sound of running water for 42 minutes followed by silence for another 21 minutes must mean that water has run down Cary and not just down the drain. And although I haven't seen him take the shower, we're many OakTable members who have seen him go into the bathroom and come out of bathroom - because we were all waiting for him to finish so the other 15 people could get their 42 seconds showers that they were entitled to. Cary is, indeed, a clean guy. Mogens Rachel Carmichael wrote: information without value - there are so many things I haven't seen yet, like lizards playing chess or Cary taking a quick shower). oh the questions and thoughts this brings to mind! as in: has Mogens SEEN Cary taking a shower? or does he infer that Cary takes long showers by the amount of time Cary is absent from a room and the degree of wetness of Cary's hair when he returns? How quick is a quick shower? and, Cary is so definitely the epitome of the cute American boy next door -- too bad that I've met his wife and like her, the thoughts of him taking a shower could be interesting! --- Mogens_Nørgaard [EMAIL PROTECTED] wrote: 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 __ 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: [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).
Re: ora 1575?
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: 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: [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). -- 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).
Re: ora 1575?
Tim, 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: 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: [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).
Re: ora 1575?
Check that the less than 1% of sorts aren't huge ones and not done by user whose temporary_tablespace is pointed to permanent one.. system for example. (Just a note that you can get temporary segments to non-temporary tablespaces due failed index (re)creations, and create table as select) Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, August 13, 2003 8:54 PM Tim and Tanel: thanks for your help. The temp tablespace is LMTS and is TEMPORARY and less than 1% of all sorts has been done on disk. I will make sure to check the v$locks when this issue appears again. I have changed the pcticrease for all the tablespaces to 0% so may be this will take care of the issue. Gene --- Tim Gorman [EMAIL PROTECTED] 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: 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: [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
Re: ora 1575?
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: 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: [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).
ora 1575?
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: 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).
Re: ora 1575?
Chris, The query is showing 99.19% of the sorts done in memory. thank you Gene --- [EMAIL PROTECTED] wrote: Are you doing a lot of sorting with a small sort_area_size set? __ 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).
Re: ora 1575?
Tim and Tanel: thanks for your help. The temp tablespace is LMTS and is TEMPORARY and less than 1% of all sorts has been done on disk. I will make sure to check the v$locks when this issue appears again. I have changed the pcticrease for all the tablespaces to 0% so may be this will take care of the issue. Gene --- Tim Gorman [EMAIL PROTECTED] 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: 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: [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). -- 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
Re: ora 1575?
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: 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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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).
Re: ora 1575?
Are you doing a lot of sorting with a small sort_area_size set? That's what Note:33567.1 in Metalink seems to point to. Since this involves the ST enqueue it is probably also a good idea to see if you can switch to locally managed tablespaces. Switch temp over first. You might want to run a query like this to see what proportion of your sorts are in memory: SELECT a.value Disk Sorts, b.value Memory Sorts, round((100*b.value) / decode((a.value + b.value),0,1, (a.value + b.value)),2) Pct Memory Sorts FROM v$sysstat a, v$sysstat b WHERE a.name = 'sorts (disk)' and b.name = 'sorts (memory)'; Regards, Chris Gait on 8/12/03 12:44 PM, Gurelei at [EMAIL PROTECTED] 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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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).
ORA-1575
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).
Re: ORA-1575
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 [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
Re: ORA-1575
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
