Re: Coalescing tablespace
Ross, Yup. I don't recall ever excluding SYSTEM, though I generally work hard to minimize I/O in that tablespace (i.e. make sure not being used as temporary, no schemas except SYS, move AUD$ table to another TS if necessary, cache sequences, etc). Most folks put that statement into a loop, to go through all the tablespaces, excluding tablespaces where COUNT(*) comes back as 0... Good luck! -Tim on 10/26/03 9:54 PM, Ross Collado at [EMAIL PROTECTED] wrote: Thanks Tim. Yes, for some it is a blast from the past! Unfortunately for me, for one of our systems still using this database, I am sort of stuck in the past. On the subject of coalescing, is the SYSTEM tablespace safe to be coalesced as well? I was thinking of putting this SQL statement you've suggested in a loop for all tablespaces. Thanks again and to others for the prompt reply. Rgds, Ross -Original Message- From: Tim Gorman [mailto:[EMAIL PROTECTED] Sent: Monday, 27 October 2003 2:44 PM To: Multiple recipients of list ORACLE-L Subject: Re: Coalescing tablespace Ross, Wow! That's a blast from the past! ALTER TABLESPACE ... COALESCE didn't arrive on the scene until v7.3, so prior to that version you had to do something like the following: alter session set events 'immediate trace name coalesce level NNN'; where the value of the level NNN could be set one of two ways. First and simplest way to set NNN is to use the value of the column TS# in the table SYS.TS$ where NAME is the name of the tablespace. So, a query like: select ts# from sys.ts$ where name = 'TOOLS' would yield the NNN for the level in the ALTER SESSION statement, or: alter session set events 'immediate trace name coalesce level 23'; if the TS# of the TOOLS tablespace was 23. However, setting the level this way would cause the ALTER SESSION command to only coalesce a certain, predefined number of extents and then stop. At this time, 6-7 years after the last time I used it, I forget what the default was, but it was probably a very low number (maybe 1?)... So, if you wanted to specify to the ALTER SESSION command how many extents to coalesce before quitting, you had to specify the TS# number in the lower-order 16 bits of the NNN and the number of extents to coalesce in the higher-order 16 bits. This implied an upper limit of 65535 extents to be coalesced at a time. So, generating a full ALTER SESSION statement, complete with a fully-qualified LEVEL parameter, could be performed as follows: select 'alter session set events ' || '''immediate trace name coalesce level ' || to_char((65536*least(count(*),65535))+t.ts#)|| from sys.fet$ a, sys.fet$ b, sys.ts$t wheret.name = 'tablespace-name' and a.ts# = t.ts# and a.ts# = b.ts# and a.file# = b.file# and (a.block# + a.length) = b.block# group by t.ts#; The purpose of the LEAST(COUNT(*),65535) phrase is to prevent an overflow, due to max of 16 bits in which to specify the count... Hope this helps... Thanks! -Tim on 10/26/03 6:04 PM, Ross Collado at [EMAIL PROTECTED] wrote: Hi, For those like me still working on an Oracle 716 (hold the laughs), how do we coalesce a tablespace? Rgds, Ross -- 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: 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: Coalescing tablespace
Yes, you are right, of course. I messed up. On 10/27/2003 01:34:25 AM, Cary Millsap wrote: You mean make your PCTINCREASE 0 so that SMON will *not* coalesce them. Right? Some comments: - You don't want SMON coalescing your tablespaces. It is a complete waste of time. Oracle coalesces on demand during a CREATE statement. - The SMON coalesce feature was added under protest by the smart people in Server Technologies development, in response to pressure from Marketing and Support so that the Oracle RDBMS would look better in brochures. - Thank goodness the developers made SMON smart enough to *not* coalesce freespace in any tablespace with PCTINCREASE set to 0 (which is what it *should* be set to almost everywhere). - You can turn off SMON's ridiculous CPU-consuming, lock-holding behavior by setting event 10269. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Mladen Gogala Sent: Sunday, October 26, 2003 8:09 PM To: Multiple recipients of list ORACLE-L Well, having in mind that you're working on version 7 (chuckle) , you should make your tablespaces have PCTINCREASE 0 (alter tablespace default storage...) so SMON will coalesce them. You may also try (I'm not sure whether this has existed before 7.3) with alter tablespace coalesce, which should mark the tablespace to be coalesced by SMON at he first opportunity. I'm afraid that PCTINCREASE 0 is your best option. On 2003.10.26 20:04, Ross Collado wrote: Hi, For those like me still working on an Oracle 716 (hold the laughs), how do we coalesce a tablespace? Rgds, Ross -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ross Collado 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). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: Cary Millsap 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). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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
RE: Coalescing tablespace
I always saw this note (and hated it): http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab ase_id=NOTp_id=31116.1 Hope it helps, Waleed -Original Message- Sent: Sunday, October 26, 2003 8:04 PM To: Multiple recipients of list ORACLE-L Hi, For those like me still working on an Oracle 716 (hold the laughs), how do we coalesce a tablespace? Rgds, Ross -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ross Collado 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: Khedr, Waleed 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: Coalescing tablespace
Ah yes, the squashed squirrel demo... Connor --- Cary Millsap [EMAIL PROTECTED] wrote: You mean make your PCTINCREASE 0 so that SMON will *not* coalesce them. Right? Some comments: - You don't want SMON coalescing your tablespaces. It is a complete waste of time. Oracle coalesces on demand during a CREATE statement. - The SMON coalesce feature was added under protest by the smart people in Server Technologies development, in response to pressure from Marketing and Support so that the Oracle RDBMS would look better in brochures. - Thank goodness the developers made SMON smart enough to *not* coalesce freespace in any tablespace with PCTINCREASE set to 0 (which is what it *should* be set to almost everywhere). - You can turn off SMON's ridiculous CPU-consuming, lock-holding behavior by setting event 10269. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Mladen Gogala Sent: Sunday, October 26, 2003 8:09 PM To: Multiple recipients of list ORACLE-L Well, having in mind that you're working on version 7 (chuckle) , you should make your tablespaces have PCTINCREASE 0 (alter tablespace default storage...) so SMON will coalesce them. You may also try (I'm not sure whether this has existed before 7.3) with alter tablespace coalesce, which should mark the tablespace to be coalesced by SMON at he first opportunity. I'm afraid that PCTINCREASE 0 is your best option. On 2003.10.26 20:04, Ross Collado wrote: Hi, For those like me still working on an Oracle 716 (hold the laughs), how do we coalesce a tablespace? Rgds, Ross -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ross Collado 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). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: Cary Millsap 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). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://mail.messenger.yahoo.co.uk -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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
Re: Coalescing tablespace
I wasn't feeling quite well when I was replying which made me confuse things. You, of course are right. SMON was locking fet$ table during those coalesce session, which was exceptionally dangerous in the OPS configurations. Coalescing doesn't buy much,the coalesce operation just combines two neighboring free extents into a single free extent, if those exist. This event was much more rare then expected and if the database was properly configured (user data,temp, rbs, data dictionary separated from each other) it was highly unlikely that coalescing will be useful. Locally managed (bit mapped) tablespaces took care of the problem. Now, we can start defragmenting and stop living. On 10/27/2003 01:34:25 AM, Cary Millsap wrote: You mean make your PCTINCREASE 0 so that SMON will *not* coalesce them. Right? Some comments: - You don't want SMON coalescing your tablespaces. It is a complete waste of time. Oracle coalesces on demand during a CREATE statement. - The SMON coalesce feature was added under protest by the smart people in Server Technologies development, in response to pressure from Marketing and Support so that the Oracle RDBMS would look better in brochures. - Thank goodness the developers made SMON smart enough to *not* coalesce freespace in any tablespace with PCTINCREASE set to 0 (which is what it *should* be set to almost everywhere). - You can turn off SMON's ridiculous CPU-consuming, lock-holding behavior by setting event 10269. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Mladen Gogala Sent: Sunday, October 26, 2003 8:09 PM To: Multiple recipients of list ORACLE-L Well, having in mind that you're working on version 7 (chuckle) , you should make your tablespaces have PCTINCREASE 0 (alter tablespace default storage...) so SMON will coalesce them. You may also try (I'm not sure whether this has existed before 7.3) with alter tablespace coalesce, which should mark the tablespace to be coalesced by SMON at he first opportunity. I'm afraid that PCTINCREASE 0 is your best option. On 2003.10.26 20:04, Ross Collado wrote: Hi, For those like me still working on an Oracle 716 (hold the laughs), how do we coalesce a tablespace? Rgds, Ross -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ross Collado 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). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: Cary Millsap 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). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading
RE: Coalescing tablespace
Bingo. I've included a few more details in a separate note sent just prior to my receiving this one. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Mladen Gogala Sent: Monday, October 27, 2003 8:14 AM To: Multiple recipients of list ORACLE-L I wasn't feeling quite well when I was replying which made me confuse things. You, of course are right. SMON was locking fet$ table during those coalesce session, which was exceptionally dangerous in the OPS configurations. Coalescing doesn't buy much,the coalesce operation just combines two neighboring free extents into a single free extent, if those exist. This event was much more rare then expected and if the database was properly configured (user data,temp, rbs, data dictionary separated from each other) it was highly unlikely that coalescing will be useful. Locally managed (bit mapped) tablespaces took care of the problem. Now, we can start defragmenting and stop living. On 10/27/2003 01:34:25 AM, Cary Millsap wrote: You mean make your PCTINCREASE 0 so that SMON will *not* coalesce them. Right? Some comments: - You don't want SMON coalescing your tablespaces. It is a complete waste of time. Oracle coalesces on demand during a CREATE statement. - The SMON coalesce feature was added under protest by the smart people in Server Technologies development, in response to pressure from Marketing and Support so that the Oracle RDBMS would look better in brochures. - Thank goodness the developers made SMON smart enough to *not* coalesce freespace in any tablespace with PCTINCREASE set to 0 (which is what it *should* be set to almost everywhere). - You can turn off SMON's ridiculous CPU-consuming, lock-holding behavior by setting event 10269. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Mladen Gogala Sent: Sunday, October 26, 2003 8:09 PM To: Multiple recipients of list ORACLE-L Well, having in mind that you're working on version 7 (chuckle) , you should make your tablespaces have PCTINCREASE 0 (alter tablespace default storage...) so SMON will coalesce them. You may also try (I'm not sure whether this has existed before 7.3) with alter tablespace coalesce, which should mark the tablespace to be coalesced by SMON at he first opportunity. I'm afraid that PCTINCREASE 0 is your best option. On 2003.10.26 20:04, Ross Collado wrote: Hi, For those like me still working on an Oracle 716 (hold the laughs), how do we coalesce a tablespace? Rgds, Ross -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ross Collado 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). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: Cary Millsap 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
RE: Coalescing tablespace
It's a note created in response to hundreds of customers shouting about how VMS and DOS need defragmenting; therefore, Oracle must need defragmenting, too. There *was* a cluster bug way back in release 6 that caused CREATE statements to take a really long time if you ever let a tablespace get more than a certain number of freed extents in it. Oracle (Jonathan Klein) fixed it by 6.0.36. I described that problem to the list already, some months ago. This bug drove the perception that Oracle tablespaces needed periodic defragmentation. But not exactly. Unless you defragmented *before* so many extents were created, even defragmentation didn't do any good. If I recall correctly, the magic number was about 61 extents or something like that, with a 2KB Oracle database block size. So, if you got to 50 free extents and coalesced them to 1, got to 50 again and coalesced again, and so on, then you'd be okay. But if you ever dropped a whole schema and produced 2,000 free extents (or even just 70), then no amount of defragmenting would help you, automatic or not. Let me repeat: THIS PROBLEM WAS FIXED FIFTEEN YEARS AGO. The bottom line is that manual coalescing just wastes time and system resources. SMON's automatic coalescing wastes even more. Always did; still does. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Khedr, Waleed Sent: Monday, October 27, 2003 8:04 AM To: Multiple recipients of list ORACLE-L I always saw this note (and hated it): http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_d atab ase_id=NOTp_id=31116.1 Hope it helps, Waleed -Original Message- Sent: Sunday, October 26, 2003 8:04 PM To: Multiple recipients of list ORACLE-L Hi, For those like me still working on an Oracle 716 (hold the laughs), how do we coalesce a tablespace? Rgds, Ross -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ross Collado 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: Khedr, Waleed 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: Cary Millsap 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: Coalescing tablespace
Cary, Hang on a second. Oracle will not always coalesce on create to the extent needed. It will go out either 10 or 20 extents (rusty memory here) and then give up. In this case it will either create an extent of the size requested above all of the other objects in the tablespace or return an error. I've found the coalesce command extremely useful since you really don't want pctincrease 0 due to it's obscene behavior. It is the only way you can build a decent sized table in a tablespace that's broken up into tiny adjacent chunks. Case in point, we were trying to clean up a PeopleSoft mess where PSINDEX was busted up into millions of 16K extents. Part of the exercise was to relocate several indexes into their own tablespaces then try to reclaim some unused space. Well, no problem, Oracle coalesces tablespaces on create, right. NAW, every index create statement failed just like the tablespace was full when we knew it was totally empty. Coalesced the tablespace they all succeeded. PS: I think the Server Technologies folks need to spend a little more time with SMON. It certainly does not appear to pull it's due. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Monday, October 27, 2003 1:34 AM To: Multiple recipients of list ORACLE-L You mean make your PCTINCREASE 0 so that SMON will *not* coalesce them. Right? Some comments: - You don't want SMON coalescing your tablespaces. It is a complete waste of time. Oracle coalesces on demand during a CREATE statement. - The SMON coalesce feature was added under protest by the smart people in Server Technologies development, in response to pressure from Marketing and Support so that the Oracle RDBMS would look better in brochures. - Thank goodness the developers made SMON smart enough to *not* coalesce freespace in any tablespace with PCTINCREASE set to 0 (which is what it *should* be set to almost everywhere). - You can turn off SMON's ridiculous CPU-consuming, lock-holding behavior by setting event 10269. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Mladen Gogala Sent: Sunday, October 26, 2003 8:09 PM To: Multiple recipients of list ORACLE-L Well, having in mind that you're working on version 7 (chuckle) , you should make your tablespaces have PCTINCREASE 0 (alter tablespace default storage...) so SMON will coalesce them. You may also try (I'm not sure whether this has existed before 7.3) with alter tablespace coalesce, which should mark the tablespace to be coalesced by SMON at he first opportunity. I'm afraid that PCTINCREASE 0 is your best option. On 2003.10.26 20:04, Ross Collado wrote: Hi, For those like me still working on an Oracle 716 (hold the laughs), how do we coalesce a tablespace? Rgds, Ross -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ross Collado 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). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: Cary Millsap 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
RE: Coalescing tablespace
Dick, You said: I think the Server Technologies folks need to spend a little more time with SMON. It certainly does not appear to pull it's due. I think they avoided the issue with Locally Managed Tablespaces. All of these problems go away with standardized extent sizes. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, October 27, 2003 10:19 AM To: Multiple recipients of list ORACLE-L Cary, Hang on a second. Oracle will not always coalesce on create to the extent needed. It will go out either 10 or 20 extents (rusty memory here) and then give up. In this case it will either create an extent of the size requested above all of the other objects in the tablespace or return an error. I've found the coalesce command extremely useful since you really don't want pctincrease 0 due to it's obscene behavior. It is the only way you can build a decent sized table in a tablespace that's broken up into tiny adjacent chunks. Case in point, we were trying to clean up a PeopleSoft mess where PSINDEX was busted up into millions of 16K extents. Part of the exercise was to relocate several indexes into their own tablespaces then try to reclaim some unused space. Well, no problem, Oracle coalesces tablespaces on create, right. NAW, every index create statement failed just like the tablespace was full when we knew it was totally empty. Coalesced the tablespace they all succeeded. PS: I think the Server Technologies folks need to spend a little more time with SMON. It certainly does not appear to pull it's due. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Monday, October 27, 2003 1:34 AM To: Multiple recipients of list ORACLE-L You mean make your PCTINCREASE 0 so that SMON will *not* coalesce them. Right? Some comments: - You don't want SMON coalescing your tablespaces. It is a complete waste of time. Oracle coalesces on demand during a CREATE statement. - The SMON coalesce feature was added under protest by the smart people in Server Technologies development, in response to pressure from Marketing and Support so that the Oracle RDBMS would look better in brochures. - Thank goodness the developers made SMON smart enough to *not* coalesce freespace in any tablespace with PCTINCREASE set to 0 (which is what it *should* be set to almost everywhere). - You can turn off SMON's ridiculous CPU-consuming, lock-holding behavior by setting event 10269. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Mladen Gogala Sent: Sunday, October 26, 2003 8:09 PM To: Multiple recipients of list ORACLE-L Well, having in mind that you're working on version 7 (chuckle) , you should make your tablespaces have PCTINCREASE 0 (alter tablespace default storage...) so SMON will coalesce them. You may also try (I'm not sure whether this has existed before 7.3) with alter tablespace coalesce, which should mark the tablespace to be coalesced by SMON at he first opportunity. I'm afraid that PCTINCREASE 0 is your best option. On 2003.10.26 20:04, Ross Collado wrote: Hi, For those like me still working on an Oracle 716 (hold the laughs), how do we coalesce a tablespace? Rgds, Ross -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ross Collado 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). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051
RE: Coalescing tablespace
Tom, Well if that was the only place SMON seems to be weak, I'd wholeheartedly agree. But it leave something to be desired when a user terminates their session ungracefully as well. Yes I know that's primarily PMON's job, but I've been told by OTS that some of it is also SMON's job and it isn't very efficient at that either. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Monday, October 27, 2003 10:44 AM To: Multiple recipients of list ORACLE-L Dick, You said: I think the Server Technologies folks need to spend a little more time with SMON. It certainly does not appear to pull it's due. I think they avoided the issue with Locally Managed Tablespaces. All of these problems go away with standardized extent sizes. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, October 27, 2003 10:19 AM To: Multiple recipients of list ORACLE-L Cary, Hang on a second. Oracle will not always coalesce on create to the extent needed. It will go out either 10 or 20 extents (rusty memory here) and then give up. In this case it will either create an extent of the size requested above all of the other objects in the tablespace or return an error. I've found the coalesce command extremely useful since you really don't want pctincrease 0 due to it's obscene behavior. It is the only way you can build a decent sized table in a tablespace that's broken up into tiny adjacent chunks. Case in point, we were trying to clean up a PeopleSoft mess where PSINDEX was busted up into millions of 16K extents. Part of the exercise was to relocate several indexes into their own tablespaces then try to reclaim some unused space. Well, no problem, Oracle coalesces tablespaces on create, right. NAW, every index create statement failed just like the tablespace was full when we knew it was totally empty. Coalesced the tablespace they all succeeded. PS: I think the Server Technologies folks need to spend a little more time with SMON. It certainly does not appear to pull it's due. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Monday, October 27, 2003 1:34 AM To: Multiple recipients of list ORACLE-L You mean make your PCTINCREASE 0 so that SMON will *not* coalesce them. Right? Some comments: - You don't want SMON coalescing your tablespaces. It is a complete waste of time. Oracle coalesces on demand during a CREATE statement. - The SMON coalesce feature was added under protest by the smart people in Server Technologies development, in response to pressure from Marketing and Support so that the Oracle RDBMS would look better in brochures. - Thank goodness the developers made SMON smart enough to *not* coalesce freespace in any tablespace with PCTINCREASE set to 0 (which is what it *should* be set to almost everywhere). - You can turn off SMON's ridiculous CPU-consuming, lock-holding behavior by setting event 10269. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Mladen Gogala Sent: Sunday, October 26, 2003 8:09 PM To: Multiple recipients of list ORACLE-L Well, having in mind that you're working on version 7 (chuckle) , you should make your tablespaces have PCTINCREASE 0 (alter tablespace default storage...) so SMON will coalesce them. You may also try (I'm not sure whether this has existed before 7.3) with alter tablespace coalesce, which should mark the tablespace to be coalesced by SMON at he first opportunity. I'm afraid that PCTINCREASE 0 is your best option. On 2003.10.26 20:04, Ross Collado wrote: Hi, For those like me still working on an Oracle 716 (hold the laughs), how do we coalesce a tablespace? Rgds, Ross -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ross Collado 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). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services -
Re: Coalescing tablespace
Cary, Under 7.1, 7.2, and 7.3, there were some very real situations where even the most careful space management strategies came to naught. Specifically, the habit of parallel direct-path operations on tables and indexes to trim the last extent to avoid leaving wasted space above the high-water mark in the temporary segment before it was merged into the final segment. Event 10901 could be set to disable this extent trimming and allow the space to be wasted in order to maintain desired extent sizing. The reason for this trim operation was not to avoid wasting space per se, but rather to resolve the dilemma of merging multiple segments into one, each with it's own high-water mark to maintain. The reason this became a problem was due to an apparent failure to coalesce on subsequent CREATE; it was not unusual for a series of DROPs followed by CREATEs in 7.1 and 7.2 to result in an out-of-space message, even when DBA_SEGMENTS clearly showed no segments in the tablespace in question. A coalesce operation using the fully-qualified coalesce event level that I replied in an earlier email would fix the problem directly. The 10901 event became available in 7.3.3 (due to problems documented in 7.1 and 7.2) and became largely useless in v8.x. In v9.x, it is apparently a no-op. I agree that coalesce operations as a proactive database maintenance measure are generally a waste of time, and are generally the product of the fragmentation nonsense that has been flying around for over a decade. It didn't help that some large companies had de-frag products and thus had financial incentive to propagate the myths of problems, including performance problems (which is utter bunk). Some years ago I put together a paper on the myths surrounding extent management (http://www.EvDBT.com/papers.htm)... From 8i onwards, of course, the use of LMTs completely negates the discussion altogether. Thanks! -Tim on 10/27/03 7:49 AM, Cary Millsap at [EMAIL PROTECTED] wrote: It's a note created in response to hundreds of customers shouting about how VMS and DOS need defragmenting; therefore, Oracle must need defragmenting, too. There *was* a cluster bug way back in release 6 that caused CREATE statements to take a really long time if you ever let a tablespace get more than a certain number of freed extents in it. Oracle (Jonathan Klein) fixed it by 6.0.36. I described that problem to the list already, some months ago. This bug drove the perception that Oracle tablespaces needed periodic defragmentation. But not exactly. Unless you defragmented *before* so many extents were created, even defragmentation didn't do any good. If I recall correctly, the magic number was about 61 extents or something like that, with a 2KB Oracle database block size. So, if you got to 50 free extents and coalesced them to 1, got to 50 again and coalesced again, and so on, then you'd be okay. But if you ever dropped a whole schema and produced 2,000 free extents (or even just 70), then no amount of defragmenting would help you, automatic or not. Let me repeat: THIS PROBLEM WAS FIXED FIFTEEN YEARS AGO. The bottom line is that manual coalescing just wastes time and system resources. SMON's automatic coalescing wastes even more. Always did; still does. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Khedr, Waleed Sent: Monday, October 27, 2003 8:04 AM To: Multiple recipients of list ORACLE-L I always saw this note (and hated it): http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_d atab ase_id=NOTp_id=31116.1 Hope it helps, Waleed -Original Message- Sent: Sunday, October 26, 2003 8:04 PM To: Multiple recipients of list ORACLE-L Hi, For those like me still working on an Oracle 716 (hold the laughs), how do we coalesce a tablespace? Rgds, Ross -- 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).
Coalescing tablespace
Hi, For those like me still working on an Oracle 716 (hold the laughs), how do we coalesce a tablespace? Rgds, Ross -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ross Collado 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: Coalescing tablespace
Well, having in mind that you're working on version 7 (chuckle) , you should make your tablespaces have PCTINCREASE 0 (alter tablespace default storage...) so SMON will coalesce them. You may also try (I'm not sure whether this has existed before 7.3) with alter tablespace coalesce, which should mark the tablespace to be coalesced by SMON at he first opportunity. I'm afraid that PCTINCREASE 0 is your best option. On 2003.10.26 20:04, Ross Collado wrote: Hi, For those like me still working on an Oracle 716 (hold the laughs), how do we coalesce a tablespace? Rgds, Ross -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ross Collado 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). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: Coalescing tablespace
Ross, Wow! That's a blast from the past! ALTER TABLESPACE ... COALESCE didn't arrive on the scene until v7.3, so prior to that version you had to do something like the following: alter session set events 'immediate trace name coalesce level NNN'; where the value of the level NNN could be set one of two ways. First and simplest way to set NNN is to use the value of the column TS# in the table SYS.TS$ where NAME is the name of the tablespace. So, a query like: select ts# from sys.ts$ where name = 'TOOLS' would yield the NNN for the level in the ALTER SESSION statement, or: alter session set events 'immediate trace name coalesce level 23'; if the TS# of the TOOLS tablespace was 23. However, setting the level this way would cause the ALTER SESSION command to only coalesce a certain, predefined number of extents and then stop. At this time, 6-7 years after the last time I used it, I forget what the default was, but it was probably a very low number (maybe 1?)... So, if you wanted to specify to the ALTER SESSION command how many extents to coalesce before quitting, you had to specify the TS# number in the lower-order 16 bits of the NNN and the number of extents to coalesce in the higher-order 16 bits. This implied an upper limit of 65535 extents to be coalesced at a time. So, generating a full ALTER SESSION statement, complete with a fully-qualified LEVEL parameter, could be performed as follows: select 'alter session set events ' || '''immediate trace name coalesce level ' || to_char((65536*least(count(*),65535))+t.ts#)|| from sys.fet$ a, sys.fet$ b, sys.ts$t wheret.name = 'tablespace-name' and a.ts# = t.ts# and a.ts# = b.ts# and a.file# = b.file# and (a.block# + a.length) = b.block# group by t.ts#; The purpose of the LEAST(COUNT(*),65535) phrase is to prevent an overflow, due to max of 16 bits in which to specify the count... Hope this helps... Thanks! -Tim on 10/26/03 6:04 PM, Ross Collado at [EMAIL PROTECTED] wrote: Hi, For those like me still working on an Oracle 716 (hold the laughs), how do we coalesce a tablespace? Rgds, Ross -- 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: Coalescing tablespace
Thanks Tim. Yes, for some it is a blast from the past! Unfortunately for me, for one of our systems still using this database, I am sort of stuck in the past. On the subject of coalescing, is the SYSTEM tablespace safe to be coalesced as well? I was thinking of putting this SQL statement you've suggested in a loop for all tablespaces. Thanks again and to others for the prompt reply. Rgds, Ross -Original Message- From: Tim Gorman [mailto:[EMAIL PROTECTED] Sent: Monday, 27 October 2003 2:44 PM To: Multiple recipients of list ORACLE-L Subject: Re: Coalescing tablespace Ross, Wow! That's a blast from the past! ALTER TABLESPACE ... COALESCE didn't arrive on the scene until v7.3, so prior to that version you had to do something like the following: alter session set events 'immediate trace name coalesce level NNN'; where the value of the level NNN could be set one of two ways. First and simplest way to set NNN is to use the value of the column TS# in the table SYS.TS$ where NAME is the name of the tablespace. So, a query like: select ts# from sys.ts$ where name = 'TOOLS' would yield the NNN for the level in the ALTER SESSION statement, or: alter session set events 'immediate trace name coalesce level 23'; if the TS# of the TOOLS tablespace was 23. However, setting the level this way would cause the ALTER SESSION command to only coalesce a certain, predefined number of extents and then stop. At this time, 6-7 years after the last time I used it, I forget what the default was, but it was probably a very low number (maybe 1?)... So, if you wanted to specify to the ALTER SESSION command how many extents to coalesce before quitting, you had to specify the TS# number in the lower-order 16 bits of the NNN and the number of extents to coalesce in the higher-order 16 bits. This implied an upper limit of 65535 extents to be coalesced at a time. So, generating a full ALTER SESSION statement, complete with a fully-qualified LEVEL parameter, could be performed as follows: select 'alter session set events ' || '''immediate trace name coalesce level ' || to_char((65536*least(count(*),65535))+t.ts#)|| from sys.fet$ a, sys.fet$ b, sys.ts$t wheret.name = 'tablespace-name' and a.ts# = t.ts# and a.ts# = b.ts# and a.file# = b.file# and (a.block# + a.length) = b.block# group by t.ts#; The purpose of the LEAST(COUNT(*),65535) phrase is to prevent an overflow, due to max of 16 bits in which to specify the count... Hope this helps... Thanks! -Tim on 10/26/03 6:04 PM, Ross Collado at [EMAIL PROTECTED] wrote: Hi, For those like me still working on an Oracle 716 (hold the laughs), how do we coalesce a tablespace? Rgds, Ross -- 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: Ross Collado 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: Coalescing tablespace
You mean make your PCTINCREASE 0 so that SMON will *not* coalesce them. Right? Some comments: - You don't want SMON coalescing your tablespaces. It is a complete waste of time. Oracle coalesces on demand during a CREATE statement. - The SMON coalesce feature was added under protest by the smart people in Server Technologies development, in response to pressure from Marketing and Support so that the Oracle RDBMS would look better in brochures. - Thank goodness the developers made SMON smart enough to *not* coalesce freespace in any tablespace with PCTINCREASE set to 0 (which is what it *should* be set to almost everywhere). - You can turn off SMON's ridiculous CPU-consuming, lock-holding behavior by setting event 10269. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Mladen Gogala Sent: Sunday, October 26, 2003 8:09 PM To: Multiple recipients of list ORACLE-L Well, having in mind that you're working on version 7 (chuckle) , you should make your tablespaces have PCTINCREASE 0 (alter tablespace default storage...) so SMON will coalesce them. You may also try (I'm not sure whether this has existed before 7.3) with alter tablespace coalesce, which should mark the tablespace to be coalesced by SMON at he first opportunity. I'm afraid that PCTINCREASE 0 is your best option. On 2003.10.26 20:04, Ross Collado wrote: Hi, For those like me still working on an Oracle 716 (hold the laughs), how do we coalesce a tablespace? Rgds, Ross -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ross Collado 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). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: Cary Millsap 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).
