RE: Tablespace management.
-Original Message- Sent: Monday, June 02, 2003 11:15 AM To: Multiple recipients of list ORACLE-L Yes, it is fixed in 9.2.0 Hemant At 04:25 AM 02-06-03 -0800, you wrote: Hope it's fixed in 9iR2 ! Jp. 2003/06/02 20:54:41, [EMAIL PROTECTED] wrote: Cannot create lob columns if segment space management is set to auto. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J 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). Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale 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: Bryan, Miriam 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: Tablespace management.
Oracle 9i release 2 new feature ( from the admin guide ) Removal of LOB Column Restriction: You can now create LOB columns in tablespaces that specify automatic segment-space management. Before release 2 There were some bugs with Auto Segment Space Mgmt and LOB type fields - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, June 02, 2003 17:55 _what_ ?? bulbul, can you please elaborate on your statement? Raj -- -- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, June 02, 2003 7:55 AM To: Multiple recipients of list ORACLE-L Cannot create lob columns if segment space management is set to auto. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, May 30, 2003 00:55 After reading the documents I've recommended using LOCAL, UNIFORM, AUTO as the options for tablespace management. Does anyone have any bad experiences with these? AUTOALLOCATE seems to come up with extents that are much smaller than I want and MANUAL segment management requires the use of FREELISTs (and I know that there are problems with freelists freeing up space correctly, especially in a parallel environment). I can't find any basis for making a decision between UNDO and ROLLBACK SEGMENTS. Does anyone have any experience or recommendations about UNDO usage? The database will be a materialize view replication of a transaction master that is being used for decision support and has a 15 minute update/refresh cycle. Basically, people can run queries against the snapshot without impacting the master. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: Tablespace management.
Yes, it is fixed in 9.2.0 Hemant At 04:25 AM 02-06-03 -0800, you wrote: Hope it's fixed in 9iR2 ! Jp. 2003/06/02 20:54:41, [EMAIL PROTECTED] wrote: Cannot create lob columns if segment space management is set to auto. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J 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). Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale 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: Tablespace management.
- Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, June 02, 2003 9:54 PM Cannot create lob columns if segment space management is set to auto. Why not ? SQL create tablespace new datafile 'c:\bowie\new01.dbf' reuse 2 segment space management auto; Tablespace created. SQL create table bowie2 (x clob) lob (x) store as (disable storage in row) tablespace new; Table created. Cheers Richard -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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: Tablespace management.
SQL select * from v$version; BANNER Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production PL/SQL Release 9.2.0.1.0 - Production CORE9.2.0.1.0 Production TNS for 32-bit Windows: Version 9.2.0.1.0 - Production NLSRTL Version 9.2.0.1.0 - Production SQL CREATE TABLESPACE LOCAL_UNIFORM_AUTO 2 DATAFILE 3 'C:\Oracle\oradata\ORCL\ULMTA01.DBF' SIZE 10 M 4 LOGGING 5 ONLINE 6 PERMANENT 7 EXTENT MANAGEMENT LOCAL 8 SEGMENT SPACE MANAGEMENT AUTO; Tablespace created. SQL select extent_management, allocation_type, segment_space_management 2 from dba_tablespaces 3 where tablespace_name = 'LOCAL_UNIFORM_AUTO'; EXTENT_MAN ALLOCATIO SEGMEN -- - -- LOCAL SYSTEMAUTO SQL create table ULMTA_TEST (col1 number, col2 clob, col3 blob) 2 tablespace local_uniform_auto; Table created. SQL select table_name, tablespace_name 2 from dba_tables 3 where table_name = 'ULMTA_TEST'; TABLE_NAME TABLESPACE_NAME -- -- ULMTA_TEST LOCAL_UNIFORM_AUTO Seems to work fine ;) Regards Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -Original Message- Sent: 02 June 2003 15:05 To: Multiple recipients of list ORACLE-L Bulbul, That is not correct. At least in 9.2.0.3? I am not sure what it would be in 9.2.0.1, but I doubt it would be different. SQL select extent_management, allocation_type, segment_space_management 2 from dba_tablespaces 3 where tablespace_name = 'SCL_AW_Y03Q2_DATA' 4 / EXTENT_MAN ALLOCATIO SEGMEN -- - -- LOCAL UNIFORM AUTO SQL SQL select * from v$version; BANNER Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production PL/SQL Release 9.2.0.3.0 - Production CORE9.2.0.3.0 Production TNS for Compaq Tru64 UNIX: Version 9.2.0.3.0 - Production NLSRTL Version 9.2.0.3.0 - Production SQL create table testl (col1 number, col2 clob, col3 blob) 2 tablespace scl_aw_y03q2_data 3 / Table created. Could you please let us know where you got that information. Arup Nanda --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.484 / Virus Database: 282 - Release Date: 27/05/2003 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith 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: Tablespace management.
Cannot create lob columns if segment space management is set to auto. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, May 30, 2003 00:55 After reading the documents I've recommended using LOCAL, UNIFORM, AUTO as the options for tablespace management. Does anyone have any bad experiences with these? AUTOALLOCATE seems to come up with extents that are much smaller than I want and MANUAL segment management requires the use of FREELISTs (and I know that there are problems with freelists freeing up space correctly, especially in a parallel environment). I can't find any basis for making a decision between UNDO and ROLLBACK SEGMENTS. Does anyone have any experience or recommendations about UNDO usage? The database will be a materialize view replication of a transaction master that is being used for decision support and has a 15 minute update/refresh cycle. Basically, people can run queries against the snapshot without impacting the master. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day 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: [EMAIL PROTECTED] 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: Tablespace management.
Title: RE: Tablespace management. _what_ ?? bulbul, can you please elaborate on your statement? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, June 02, 2003 7:55 AM To: Multiple recipients of list ORACLE-L Subject: Re: Tablespace management. Cannot create lob columns if segment space management is set to auto. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, May 30, 2003 00:55 After reading the documents I've recommended using LOCAL, UNIFORM, AUTO as the options for tablespace management. Does anyone have any bad experiences with these? AUTOALLOCATE seems to come up with extents that are much smaller than I want and MANUAL segment management requires the use of FREELISTs (and I know that there are problems with freelists freeing up space correctly, especially in a parallel environment). I can't find any basis for making a decision between UNDO and ROLLBACK SEGMENTS. Does anyone have any experience or recommendations about UNDO usage? The database will be a materialize view replication of a transaction master that is being used for decision support and has a 15 minute update/refresh cycle. Basically, people can run queries against the snapshot without impacting the master. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day 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: [EMAIL PROTECTED] 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). *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
Re: Tablespace management.
Hope it's fixed in 9iR2 ! Jp. 2003/06/02 20:54:41, [EMAIL PROTECTED] wrote: Cannot create lob columns if segment space management is set to auto. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J 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: Tablespace management.
This is not a comprehensive test by any means, though I think it should ally at least a few fears (and it gets me out of housework for half an hour or so. Comments with a SQL select initial_EXTENT,next_EXTENT from dba_tablespaces 2 where tablespace_name='XXX'; INITIAL_EXTENT NEXT_EXTENT -- --- 131072 131072 128k ULMT SQL SELECT ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE 2 FROM DBA_FREE_SPACE 3 where tablespace_name='XXX'; FREE_SPACE -- 4064.13 4gb or so free SQL CREATE TABLE TEST_LMT(C1 CHAR(255)) 2 TABLESPACE XXX; Table created. Create my table SQL BEGIN 2 FOR I IN 1..10 LOOP 3 EXECUTE IMMEDIATE 'ALTER TABLE TEST_LMT ALLOCATE EXTENT'; 4 END LOOP; 5 END; 6 / BEGIN * ERROR at line 1: ORA-01653: unable to extend table XXX.TEST_LMT by 8 in tablespace XXX ORA-06512: at line 3 Fill up the tablespace - generate automatic email to myself for Monday when I'm not in - oops SQL SELECT COUNT(*) 2 FROM DBA_EXTENTS 3 WHERE SEGMENT_NAME='TEST_LMT'; COUNT(*) -- 32513 Shame it wasn't 32768 but never mind SQL SET TIMING ON SQL DROP TABLE TEST_LMT; Table dropped. Elapsed: 00:00:01.04 SQL SPOOL OFF So DROP performance seems good up to at least 32k extents, and my guess is that most objects that actually eat up 4gb or more of space are good candidates for partitioning. All of the above 9.2.0.3 on Win2k. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Cary Millsap Sent: 30 May 2003 17:55 To: Multiple recipients of list ORACLE-L Subject: RE: Tablespace management. Wow. Maybe someone on the list has the time and motive to construct a test to determine how many extents for a segment in a ULMT are bad. My guess from some tests we did a couple of years ago is that it will take hundreds of thousands of extents before even DROP performance will suffer. And I can't think of *anything* that would make having even hundreds of millions of extents a bad idea for INSERTs, UPDATEs, MERGEs, or DELETEs. The only possible downsides of huge numbers of extents that I can think of are perhaps: * During the INSERT, UPDATE, or MERGE, what is the overhead of the actual allocation of the ULMT extent? (This actually may have nothing to do with how many extents are already there.) * During checkpoints on RAC systems, does the number of extents matter the way it did when Jonathan Lewis showed a problem with DMT and OPS a few years ago? * Does a huge bitmap section in the head of a data file cause any performance problems for backup and recovery? Aside from that, I can't imagine any more downside of huge numbers of ULMT extents than there is from having the Unix filesystem extents that most of us have right now and never notice. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Reykjavik, Ottawa, Dallas, Washington, Denver, Sydney - Visit www.hotsos.com for schedule details... -Original Message- Sent: Friday, May 30, 2003 8:50 AM To: Multiple recipients of list ORACLE-L Jared, It's rather simple. If you follow the rules of third normal form you have a table with a certain number of rows, a second with a certain number of rows for each row in the first table. Obviously the second table needs more space than the first. Now if you use Dictionary management you can set the storage parameters of each table individually. But if your using local management they both have the same extent sizes. This leads one to having the extent sizes smaller to accommodate the first table and large numbers of extents for the second table. True fragmentation, namely those small useless extents that land between larger used extents, is eliminated in local management but then I have not had those problems with dictionary management either, unless someone makes the case for moving a table but that's very rare. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Thursday, May 29, 2003 8:25 PM To: [EMAIL PROTECTED] Cc: Goulet, Dick Importance: High Dick, I'm trying to follow your line of thought, but I think I missed the path. Objects may not have the same storage requirements, but what does that matter? The only way I can make sense of what you say is if trying to have all objects occupy a single extent, and there's not much point in that. Jared Goulet, Dick [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/29/2003 03:51 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Tablespace management. Thomas, With the exception of temp and rollback tablespaces I have not user
FW: Tablespace management.
-Original Message- From: Niall Litchfield [mailto:[EMAIL PROTECTED] Sent: 31 May 2003 14:42 To: '[EMAIL PROTECTED]' Subject: RE: Tablespace management. Hi It might be a sobering exercise to work out how much the time and effort getting computing storage needs into an exact science has cost. Now I know that ULMTs haven't been around that long, and people may not have been on 816 or higher for that long, but your post does highlight for me just why they are a good thing. It is very difficult to get storage needs calculated correctly - especially if your business/app behaviour is unpredictable say 3 years in advance - and if you get it wrong you may well have to reorg. If You use ULMTs you won't ever have to reorg for fragmentation reasons, and probably not for performance reasons (I can't think of a single performance problem - except maybe the DBA_EXTENTS view but then a)how often and when do you query this and b) who other than the DBA does it hurt.) Niall -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Goulet, Dick Sent: 30 May 2003 18:40 To: Multiple recipients of list ORACLE-L Subject: RE: Tablespace management. Steve, I'm not sure I'd call all of the functionality that has been added over the years worth it. Way too many of them have caused more trouble than their worth, like descending indexes. And given the drivel that I've seen from many a third party vendor in the past (PeopleSoft and their damned 16K extents) this can certainly get turned into another nightmare. As far as fragmentation is concerned, I've NOT had to do any in the last few years, mainly due to spending a lot of time effort to get computing storage needs into an exact science around here. That has been due to disk storage space not being an invisible cost item, but instead a significant one that we're constantly battling with. Sure they've become cheaper, but when our buying GB's of the stuff, mirrored, from a reliable vendor those half MB's wasted begin to add up FAST. Therefore I still contend that everything inside a single tablespace does not need a uniform extent size. If one size fits all was absolutely ! true there would be a lot less problems in this world. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Friday, May 30, 2003 1:06 PM To: Multiple recipients of list ORACLE-L I think you're missing the point of the last message. What's wrong with multiple extents if the extent size is a multiple of a multiblock read? What's wrong with having two tablespaces? I'd definitely suggest reading How to Stop Defragmenting and Start Living: The Definitive Word on Fragmentation. (http://otn.oracle.com/deploy/availability/pdf/defrag.pdf) No one is suggesting *everything* should have a single extent size but everything in a tablespace should. LMT is the future and dovetails nicely with a lot of the functionality we've seen added in recent releases. What good are online table/index rebuilds if the space reclaimed is far outweighed by the space wasted by the fragmentation left behind? S- On Fri, 30 May 2003, Goulet, Dick wrote: Richard, My troubles come mainly form PeopleSoft and some in-house created applications. I'll use the in-house applications as the example since their simpler. Our CIM system has tables that contain very few rows of data, like the identification information for each robot(CELLS). Now there are only 30 robots on the longest/most complex line we have (BTW: due to the duhvelopers of this application each line needs it's own instance on it's own server, don't ask why). Now this table NEVER grows beyond 512KB is size. But each robot can have up to 1024 component slots (512 on each side) that need to be defined with what is in them (SLOTS). This table easily gets into a couple of MB but then sits there since we do tons of updates but no more inserts. If we're doing LMT's then to optimize the storage on this mess I either need 2 tablespace or else set the uniform extent size to 512K and allow the SLOTS table to have several extents. This example is one of the simpler ones, there are a lot more that get even more problematic, like those for our test data. If 10i has bad news on this front it may well become the straw that breaks the camel's back for Oracle around here. We're already toying around with DB2. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Friday, May 30, 2003 11:30 AM To: Multiple recipients of list ORACLE-L Hi Dick, What do you consider to be a large number of extents in a LMT ? At what point do you consider performance
RE: Tablespace management.
Then again, why query DBA_EXTENTS ? Pretty much anything that its got you can get from DBA_SEGMENTS, the exception being the file/block_id/blocks info. I would contend that the reason we want that level of information is when mapping segments to file position to determine I/O rates, which we can get from segment level stats in 9i anyway. And of course, everyone is already on 9i by now, as they start getting ready for a migation to 10 :-) --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: Additional downside item: * Queries against DBA_EXTENTS will take a bit longer to return. - Kirti --- Cary Millsap [EMAIL PROTECTED] wrote: Wow. Maybe someone on the list has the time and motive to construct a test to determine how many extents for a segment in a ULMT are bad. My guess from some tests we did a couple of years ago is that it will take hundreds of thousands of extents before even DROP performance will suffer. And I can't think of *anything* that would make having even hundreds of millions of extents a bad idea for INSERTs, UPDATEs, MERGEs, or DELETEs. The only possible downsides of huge numbers of extents that I can think of are perhaps: * During the INSERT, UPDATE, or MERGE, what is the overhead of the actual allocation of the ULMT extent? (This actually may have nothing to do with how many extents are already there.) * During checkpoints on RAC systems, does the number of extents matter the way it did when Jonathan Lewis showed a problem with DMT and OPS a few years ago? * Does a huge bitmap section in the head of a data file cause any performance problems for backup and recovery? Aside from that, I can't imagine any more downside of huge numbers of ULMT extents than there is from having the Unix filesystem extents that most of us have right now and never notice. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Reykjavik, Ottawa, Dallas, Washington, Denver, Sydney - Visit www.hotsos.com for schedule details... -Original Message- Sent: Friday, May 30, 2003 8:50 AM To: Multiple recipients of list ORACLE-L Jared, It's rather simple. If you follow the rules of third normal form you have a table with a certain number of rows, a second with a certain number of rows for each row in the first table. Obviously the second table needs more space than the first. Now if you use Dictionary management you can set the storage parameters of each table individually. But if your using local management they both have the same extent sizes. This leads one to having the extent sizes smaller to accommodate the first table and large numbers of extents for the second table. True fragmentation, namely those small useless extents that land between larger used extents, is eliminated in local management but then I have not had those problems with dictionary management either, unless someone makes the case for moving a table but that's very rare. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Thursday, May 29, 2003 8:25 PM To: [EMAIL PROTECTED] Cc: Goulet, Dick Importance: High Dick, I'm trying to follow your line of thought, but I think I missed the path. Objects may not have the same storage requirements, but what does that matter? The only way I can make sense of what you say is if trying to have all objects occupy a single extent, and there's not much point in that. Jared Goulet, Dick [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/29/2003 03:51 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Tablespace management. Thomas, With the exception of temp and rollback tablespaces I have not user locally managed tablespaces just because all objects must have the same sized extents. I do not see most tables sharing an equal need for storage and using dictionary management allows one to do that, at a cost I'll admit, but one that is much easier to swallow. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Thursday, May 29, 2003 3:25 PM To: Multiple recipients of list ORACLE-L After reading the documents I've recommended using LOCAL, UNIFORM, AUTO as the options for tablespace management. Does anyone have any bad experiences with these? AUTOALLOCATE seems to come up with extents that are much smaller than I want and MANUAL segment management requires the use of FREELISTs (and I know that there are problems with freelists freeing up space correctly, especially in a parallel environment). I can't find any basis for making a decision
RE: Tablespace management.
Jared, It's rather simple. If you follow the rules of third normal form you have a table with a certain number of rows, a second with a certain number of rows for each row in the first table. Obviously the second table needs more space than the first. Now if you use Dictionary management you can set the storage parameters of each table individually. But if your using local management they both have the same extent sizes. This leads one to having the extent sizes smaller to accommodate the first table and large numbers of extents for the second table. True fragmentation, namely those small useless extents that land between larger used extents, is eliminated in local management but then I have not had those problems with dictionary management either, unless someone makes the case for moving a table but that's very rare. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Thursday, May 29, 2003 8:25 PM To: [EMAIL PROTECTED] Cc: Goulet, Dick Importance: High Dick, I'm trying to follow your line of thought, but I think I missed the path. Objects may not have the same storage requirements, but what does that matter? The only way I can make sense of what you say is if trying to have all objects occupy a single extent, and there's not much point in that. Jared Goulet, Dick [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/29/2003 03:51 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Tablespace management. Thomas, With the exception of temp and rollback tablespaces I have not user locally managed tablespaces just because all objects must have the same sized extents. I do not see most tables sharing an equal need for storage and using dictionary management allows one to do that, at a cost I'll admit, but one that is much easier to swallow. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Thursday, May 29, 2003 3:25 PM To: Multiple recipients of list ORACLE-L After reading the documents I've recommended using LOCAL, UNIFORM, AUTO as the options for tablespace management. Does anyone have any bad experiences with these? AUTOALLOCATE seems to come up with extents that are much smaller than I want and MANUAL segment management requires the use of FREELISTs (and I know that there are problems with freelists freeing up space correctly, especially in a parallel environment). I can't find any basis for making a decision between UNDO and ROLLBACK SEGMENTS. Does anyone have any experience or recommendations about UNDO usage? The database will be a materialize view replication of a transaction master that is being used for decision support and has a 15 minute update/refresh cycle. Basically, people can run queries against the snapshot without impacting the master. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day 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: Goulet, Dick 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: Goulet, Dick 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: Tablespace management.
Hi Dick, What do you consider to be a large number of extents in a LMT ? At what point do you consider performance and manageability to be such that you sigh gee, I wish I had fewer extents ? What do you consider to be the ideal number of extents for a segment in a DMT vs. LMT that makes DMT so desirable ? I'm really really curious. BTW, I think 10i has some bad news in store for you ... Cheers ;) Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, May 30, 2003 11:49 PM Jared, It's rather simple. If you follow the rules of third normal form you have a table with a certain number of rows, a second with a certain number of rows for each row in the first table. Obviously the second table needs more space than the first. Now if you use Dictionary management you can set the storage parameters of each table individually. But if your using local management they both have the same extent sizes. This leads one to having the extent sizes smaller to accommodate the first table and large numbers of extents for the second table. True fragmentation, namely those small useless extents that land between larger used extents, is eliminated in local management but then I have not had those problems with dictionary management either, unless someone makes the case for moving a table but that's very rare. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Thursday, May 29, 2003 8:25 PM To: [EMAIL PROTECTED] Cc: Goulet, Dick Importance: High Dick, I'm trying to follow your line of thought, but I think I missed the path. Objects may not have the same storage requirements, but what does that matter? The only way I can make sense of what you say is if trying to have all objects occupy a single extent, and there's not much point in that. Jared Goulet, Dick [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/29/2003 03:51 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Tablespace management. Thomas, With the exception of temp and rollback tablespaces I have not user locally managed tablespaces just because all objects must have the same sized extents. I do not see most tables sharing an equal need for storage and using dictionary management allows one to do that, at a cost I'll admit, but one that is much easier to swallow. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Thursday, May 29, 2003 3:25 PM To: Multiple recipients of list ORACLE-L After reading the documents I've recommended using LOCAL, UNIFORM, AUTO as the options for tablespace management. Does anyone have any bad experiences with these? AUTOALLOCATE seems to come up with extents that are much smaller than I want and MANUAL segment management requires the use of FREELISTs (and I know that there are problems with freelists freeing up space correctly, especially in a parallel environment). I can't find any basis for making a decision between UNDO and ROLLBACK SEGMENTS. Does anyone have any experience or recommendations about UNDO usage? The database will be a materialize view replication of a transaction master that is being used for decision support and has a 15 minute update/refresh cycle. Basically, people can run queries against the snapshot without impacting the master. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day 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: Goulet, Dick 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: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538
RE: Tablespace management.
Richard, My troubles come mainly form PeopleSoft and some in-house created applications. I'll use the in-house applications as the example since their simpler. Our CIM system has tables that contain very few rows of data, like the identification information for each robot(CELLS). Now there are only 30 robots on the longest/most complex line we have (BTW: due to the duhvelopers of this application each line needs it's own instance on it's own server, don't ask why). Now this table NEVER grows beyond 512KB is size. But each robot can have up to 1024 component slots (512 on each side) that need to be defined with what is in them (SLOTS). This table easily gets into a couple of MB but then sits there since we do tons of updates but no more inserts. If we're doing LMT's then to optimize the storage on this mess I either need 2 tablespace or else set the uniform extent size to 512K and allow the SLOTS table to have several extents. This example is one of the simpler ones, there are a lot more that get even more problematic, like those for our test data. If 10i has bad news on this front it may well become the straw that breaks the camel's back for Oracle around here. We're already toying around with DB2. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Friday, May 30, 2003 11:30 AM To: Multiple recipients of list ORACLE-L Hi Dick, What do you consider to be a large number of extents in a LMT ? At what point do you consider performance and manageability to be such that you sigh gee, I wish I had fewer extents ? What do you consider to be the ideal number of extents for a segment in a DMT vs. LMT that makes DMT so desirable ? I'm really really curious. BTW, I think 10i has some bad news in store for you ... Cheers ;) Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, May 30, 2003 11:49 PM Jared, It's rather simple. If you follow the rules of third normal form you have a table with a certain number of rows, a second with a certain number of rows for each row in the first table. Obviously the second table needs more space than the first. Now if you use Dictionary management you can set the storage parameters of each table individually. But if your using local management they both have the same extent sizes. This leads one to having the extent sizes smaller to accommodate the first table and large numbers of extents for the second table. True fragmentation, namely those small useless extents that land between larger used extents, is eliminated in local management but then I have not had those problems with dictionary management either, unless someone makes the case for moving a table but that's very rare. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Thursday, May 29, 2003 8:25 PM To: [EMAIL PROTECTED] Cc: Goulet, Dick Importance: High Dick, I'm trying to follow your line of thought, but I think I missed the path. Objects may not have the same storage requirements, but what does that matter? The only way I can make sense of what you say is if trying to have all objects occupy a single extent, and there's not much point in that. Jared Goulet, Dick [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/29/2003 03:51 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Tablespace management. Thomas, With the exception of temp and rollback tablespaces I have not user locally managed tablespaces just because all objects must have the same sized extents. I do not see most tables sharing an equal need for storage and using dictionary management allows one to do that, at a cost I'll admit, but one that is much easier to swallow. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Thursday, May 29, 2003 3:25 PM To: Multiple recipients of list ORACLE-L After reading the documents I've recommended using LOCAL, UNIFORM, AUTO as the options for tablespace management. Does anyone have any bad experiences with these? AUTOALLOCATE seems to come up with extents that are much smaller than I want and MANUAL segment management requires the use of FREELISTs (and I know that there are problems with freelists freeing up space correctly, especially in a parallel environment). I can't find any basis for making a decision between UNDO and ROLLBACK SEGMENTS. Does anyone have any experience or recommendations about UNDO usage? The database will be a materialize view replication of a transaction master that is being used for decision support and has a 15 minute update/refresh cycle. Basically, people can run queries against the snapshot without impacting the master. -- Please see the official ORACLE-L FAQ: http
RE: Tablespace management.
So what is wrong with having the SLOTS table occupy several hundred extents? If it grows to 500MB it will occupy 1000 extents, so what. If it were to grow into GB I'd probably make the extents 1MB and swallow the wasted .5M in the CELL extent - what is half a meg when you're in the GB. As for Peoplesoft, I manage Peoplesoft systems as well and I have separated the tables into tiny (extent size 16K, tables do not have more than 1 block - ~90%-95% of all tables in the system, most of them even empty), small (extent size 64K), medium, large, and XXL plus one for the active _TMP, _WRK, and _TAO tables, and then the same for the indexes. Works like a charm. The only tablespaces I have to worry about are the large and xxl table and index tablespaces. Everything else is pretty much static. At 07:59 AM 5/30/2003 -0800, you wrote: Richard, My troubles come mainly form PeopleSoft and some in-house created applications. I'll use the in-house applications as the example since their simpler. Our CIM system has tables that contain very few rows of data, like the identification information for each robot(CELLS). Now there are only 30 robots on the longest/most complex line we have (BTW: due to the duhvelopers of this application each line needs it's own instance on it's own server, don't ask why). Now this table NEVER grows beyond 512KB is size. But each robot can have up to 1024 component slots (512 on each side) that need to be defined with what is in them (SLOTS). This table easily gets into a couple of MB but then sits there since we do tons of updates but no more inserts. If we're doing LMT's then to optimize the storage on this mess I either need 2 tablespace or else set the uniform extent size to 512K and allow the SLOTS table to have several extents. This example is one of the simpler ones, there are a lot more that get even more problematic, like those for our test data. If 10i has bad news on this front it may well become the straw that breaks the camel's back for Oracle around here. We're already toying around with DB2. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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: Tablespace management.
The whole point of ULMT is that you simply don't need to think about this stuff anymore. It may not seem comfortable at first if your brain has the multiple extents are bad circuitry wired into it, but it really shouldn't matter if you have gazillions of uniformly-sized extents. The test I described in the prior mail note will reveal the truth. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Reykjavik, Ottawa, Dallas, Washington, Denver, Sydney - Visit www.hotsos.com for schedule details... -Original Message- Sent: Friday, May 30, 2003 11:00 AM To: Multiple recipients of list ORACLE-L Richard, My troubles come mainly form PeopleSoft and some in-house created applications. I'll use the in-house applications as the example since their simpler. Our CIM system has tables that contain very few rows of data, like the identification information for each robot(CELLS). Now there are only 30 robots on the longest/most complex line we have (BTW: due to the duhvelopers of this application each line needs it's own instance on it's own server, don't ask why). Now this table NEVER grows beyond 512KB is size. But each robot can have up to 1024 component slots (512 on each side) that need to be defined with what is in them (SLOTS). This table easily gets into a couple of MB but then sits there since we do tons of updates but no more inserts. If we're doing LMT's then to optimize the storage on this mess I either need 2 tablespace or else set the uniform extent size to 512K and allow the SLOTS table to have several extents. This example is one of the simpler ones, there are a lot more that get even more problematic, like those for our test data. If 10i has bad news on this front it may well become the straw that breaks the camel's back for Oracle around here. We're already toying around with DB2. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Friday, May 30, 2003 11:30 AM To: Multiple recipients of list ORACLE-L Hi Dick, What do you consider to be a large number of extents in a LMT ? At what point do you consider performance and manageability to be such that you sigh gee, I wish I had fewer extents ? What do you consider to be the ideal number of extents for a segment in a DMT vs. LMT that makes DMT so desirable ? I'm really really curious. BTW, I think 10i has some bad news in store for you ... Cheers ;) Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, May 30, 2003 11:49 PM Jared, It's rather simple. If you follow the rules of third normal form you have a table with a certain number of rows, a second with a certain number of rows for each row in the first table. Obviously the second table needs more space than the first. Now if you use Dictionary management you can set the storage parameters of each table individually. But if your using local management they both have the same extent sizes. This leads one to having the extent sizes smaller to accommodate the first table and large numbers of extents for the second table. True fragmentation, namely those small useless extents that land between larger used extents, is eliminated in local management but then I have not had those problems with dictionary management either, unless someone makes the case for moving a table but that's very rare. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Thursday, May 29, 2003 8:25 PM To: [EMAIL PROTECTED] Cc: Goulet, Dick Importance: High Dick, I'm trying to follow your line of thought, but I think I missed the path. Objects may not have the same storage requirements, but what does that matter? The only way I can make sense of what you say is if trying to have all objects occupy a single extent, and there's not much point in that. Jared Goulet, Dick [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/29/2003 03:51 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Tablespace management. Thomas, With the exception of temp and rollback tablespaces I have not user locally managed tablespaces just because all objects must have the same sized extents. I do not see most tables sharing an equal need for storage and using dictionary management allows one to do that, at a cost I'll admit, but one that is much easier to swallow. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Thursday, May 29, 2003 3:25 PM To: Multiple recipients of list ORACLE-L After reading the documents I've recommended using LOCAL, UNIFORM, AUTO as the options for tablespace management. Does anyone have any bad experiences with these? AUTOALLOCATE seems to come up with extents that are much smaller than I want
RE: Tablespace management.
Wow. Maybe someone on the list has the time and motive to construct a test to determine how many extents for a segment in a ULMT are bad. My guess from some tests we did a couple of years ago is that it will take hundreds of thousands of extents before even DROP performance will suffer. And I can't think of *anything* that would make having even hundreds of millions of extents a bad idea for INSERTs, UPDATEs, MERGEs, or DELETEs. The only possible downsides of huge numbers of extents that I can think of are perhaps: * During the INSERT, UPDATE, or MERGE, what is the overhead of the actual allocation of the ULMT extent? (This actually may have nothing to do with how many extents are already there.) * During checkpoints on RAC systems, does the number of extents matter the way it did when Jonathan Lewis showed a problem with DMT and OPS a few years ago? * Does a huge bitmap section in the head of a data file cause any performance problems for backup and recovery? Aside from that, I can't imagine any more downside of huge numbers of ULMT extents than there is from having the Unix filesystem extents that most of us have right now and never notice. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Reykjavik, Ottawa, Dallas, Washington, Denver, Sydney - Visit www.hotsos.com for schedule details... -Original Message- Sent: Friday, May 30, 2003 8:50 AM To: Multiple recipients of list ORACLE-L Jared, It's rather simple. If you follow the rules of third normal form you have a table with a certain number of rows, a second with a certain number of rows for each row in the first table. Obviously the second table needs more space than the first. Now if you use Dictionary management you can set the storage parameters of each table individually. But if your using local management they both have the same extent sizes. This leads one to having the extent sizes smaller to accommodate the first table and large numbers of extents for the second table. True fragmentation, namely those small useless extents that land between larger used extents, is eliminated in local management but then I have not had those problems with dictionary management either, unless someone makes the case for moving a table but that's very rare. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Thursday, May 29, 2003 8:25 PM To: [EMAIL PROTECTED] Cc: Goulet, Dick Importance: High Dick, I'm trying to follow your line of thought, but I think I missed the path. Objects may not have the same storage requirements, but what does that matter? The only way I can make sense of what you say is if trying to have all objects occupy a single extent, and there's not much point in that. Jared Goulet, Dick [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/29/2003 03:51 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Tablespace management. Thomas, With the exception of temp and rollback tablespaces I have not user locally managed tablespaces just because all objects must have the same sized extents. I do not see most tables sharing an equal need for storage and using dictionary management allows one to do that, at a cost I'll admit, but one that is much easier to swallow. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Thursday, May 29, 2003 3:25 PM To: Multiple recipients of list ORACLE-L After reading the documents I've recommended using LOCAL, UNIFORM, AUTO as the options for tablespace management. Does anyone have any bad experiences with these? AUTOALLOCATE seems to come up with extents that are much smaller than I want and MANUAL segment management requires the use of FREELISTs (and I know that there are problems with freelists freeing up space correctly, especially in a parallel environment). I can't find any basis for making a decision between UNDO and ROLLBACK SEGMENTS. Does anyone have any experience or recommendations about UNDO usage? The database will be a materialize view replication of a transaction master that is being used for decision support and has a 15 minute update/refresh cycle. Basically, people can run queries against the snapshot without impacting the master. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day 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
RE: Tablespace management.
I think you're missing the point of the last message. What's wrong with multiple extents if the extent size is a multiple of a multiblock read? What's wrong with having two tablespaces? I'd definitely suggest reading How to Stop Defragmenting and Start Living: The Definitive Word on Fragmentation. (http://otn.oracle.com/deploy/availability/pdf/defrag.pdf) No one is suggesting *everything* should have a single extent size but everything in a tablespace should. LMT is the future and dovetails nicely with a lot of the functionality we've seen added in recent releases. What good are online table/index rebuilds if the space reclaimed is far outweighed by the space wasted by the fragmentation left behind? S- On Fri, 30 May 2003, Goulet, Dick wrote: Richard, My troubles come mainly form PeopleSoft and some in-house created applications. I'll use the in-house applications as the example since their simpler. Our CIM system has tables that contain very few rows of data, like the identification information for each robot(CELLS). Now there are only 30 robots on the longest/most complex line we have (BTW: due to the duhvelopers of this application each line needs it's own instance on it's own server, don't ask why). Now this table NEVER grows beyond 512KB is size. But each robot can have up to 1024 component slots (512 on each side) that need to be defined with what is in them (SLOTS). This table easily gets into a couple of MB but then sits there since we do tons of updates but no more inserts. If we're doing LMT's then to optimize the storage on this mess I either need 2 tablespace or else set the uniform extent size to 512K and allow the SLOTS table to have several extents. This example is one of the simpler ones, there are a lot more that get even more problematic, like those for our test data. If 10i has bad news on this front it may well become the straw that breaks the camel's back for Oracle around here. We're already toying around with DB2. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Friday, May 30, 2003 11:30 AM To: Multiple recipients of list ORACLE-L Hi Dick, What do you consider to be a large number of extents in a LMT ? At what point do you consider performance and manageability to be such that you sigh gee, I wish I had fewer extents ? What do you consider to be the ideal number of extents for a segment in a DMT vs. LMT that makes DMT so desirable ? I'm really really curious. BTW, I think 10i has some bad news in store for you ... Cheers ;) Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, May 30, 2003 11:49 PM Jared, It's rather simple. If you follow the rules of third normal form you have a table with a certain number of rows, a second with a certain number of rows for each row in the first table. Obviously the second table needs more space than the first. Now if you use Dictionary management you can set the storage parameters of each table individually. But if your using local management they both have the same extent sizes. This leads one to having the extent sizes smaller to accommodate the first table and large numbers of extents for the second table. True fragmentation, namely those small useless extents that land between larger used extents, is eliminated in local management but then I have not had those problems with dictionary management either, unless someone makes the case for moving a table but that's very rare. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Thursday, May 29, 2003 8:25 PM To: [EMAIL PROTECTED] Cc: Goulet, Dick Importance: High Dick, I'm trying to follow your line of thought, but I think I missed the path. Objects may not have the same storage requirements, but what does that matter? The only way I can make sense of what you say is if trying to have all objects occupy a single extent, and there's not much point in that. Jared Goulet, Dick [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/29/2003 03:51 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Tablespace management. Thomas, With the exception of temp and rollback tablespaces I have not user locally managed tablespaces just because all objects must have the same sized extents. I do not see most tables sharing an equal need for storage and using dictionary management allows one to do that, at a cost I'll admit, but one that is much easier to swallow. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Thursday, May 29, 2003 3:25 PM To: Multiple recipients of list ORACLE-L After reading the documents I've
Re: RE: Tablespace management.
i read some oracle documentation that recommends you keep the number of extents below 1024. do you feel that this is inaccurate in an LMT? What if Im stuck with dictionary tablespacse and am not allowed to change? Does it matter? I do keep all my extents uniform. I thought there were issuse with contention on FET$ and UET$ in dictionary managed tablespaces for a transaction database? or am I just wrong? From: Cary Millsap [EMAIL PROTECTED] Date: 2003/05/30 Fri PM 12:55:06 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Tablespace management. Wow. Maybe someone on the list has the time and motive to construct a test to determine how many extents for a segment in a ULMT are bad. My guess from some tests we did a couple of years ago is that it will take hundreds of thousands of extents before even DROP performance will suffer. And I can't think of *anything* that would make having even hundreds of millions of extents a bad idea for INSERTs, UPDATEs, MERGEs, or DELETEs. The only possible downsides of huge numbers of extents that I can think of are perhaps: * During the INSERT, UPDATE, or MERGE, what is the overhead of the actual allocation of the ULMT extent? (This actually may have nothing to do with how many extents are already there.) * During checkpoints on RAC systems, does the number of extents matter the way it did when Jonathan Lewis showed a problem with DMT and OPS a few years ago? * Does a huge bitmap section in the head of a data file cause any performance problems for backup and recovery? Aside from that, I can't imagine any more downside of huge numbers of ULMT extents than there is from having the Unix filesystem extents that most of us have right now and never notice. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Reykjavik, Ottawa, Dallas, Washington, Denver, Sydney - Visit www.hotsos.com for schedule details... -Original Message- Sent: Friday, May 30, 2003 8:50 AM To: Multiple recipients of list ORACLE-L Jared, It's rather simple. If you follow the rules of third normal form you have a table with a certain number of rows, a second with a certain number of rows for each row in the first table. Obviously the second table needs more space than the first. Now if you use Dictionary management you can set the storage parameters of each table individually. But if your using local management they both have the same extent sizes. This leads one to having the extent sizes smaller to accommodate the first table and large numbers of extents for the second table. True fragmentation, namely those small useless extents that land between larger used extents, is eliminated in local management but then I have not had those problems with dictionary management either, unless someone makes the case for moving a table but that's very rare. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Thursday, May 29, 2003 8:25 PM To: [EMAIL PROTECTED] Cc: Goulet, Dick Importance: High Dick, I'm trying to follow your line of thought, but I think I missed the path. Objects may not have the same storage requirements, but what does that matter? The only way I can make sense of what you say is if trying to have all objects occupy a single extent, and there's not much point in that. Jared Goulet, Dick [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/29/2003 03:51 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Tablespace management. Thomas, With the exception of temp and rollback tablespaces I have not user locally managed tablespaces just because all objects must have the same sized extents. I do not see most tables sharing an equal need for storage and using dictionary management allows one to do that, at a cost I'll admit, but one that is much easier to swallow. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Thursday, May 29, 2003 3:25 PM To: Multiple recipients of list ORACLE-L After reading the documents I've recommended using LOCAL, UNIFORM, AUTO as the options for tablespace management. Does anyone have any bad experiences with these? AUTOALLOCATE seems to come up with extents that are much smaller than I want and MANUAL segment management requires the use of FREELISTs (and I know that there are problems with freelists freeing up space correctly, especially in a parallel environment). I can't find any basis for making a decision between UNDO and ROLLBACK SEGMENTS. Does anyone have any experience or recommendations about UNDO usage? The database will be a materialize view replication of a transaction master that is being used for decision support
RE: Tablespace management.
: Goulet, Dick Importance: High Dick, I'm trying to follow your line of thought, but I think I missed the path. Objects may not have the same storage requirements, but what does that matter? The only way I can make sense of what you say is if trying to have all objects occupy a single extent, and there's not much point in that. Jared Goulet, Dick [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/29/2003 03:51 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Tablespace management. Thomas, With the exception of temp and rollback tablespaces I have not user locally managed tablespaces just because all objects must have the same sized extents. I do not see most tables sharing an equal need for storage and using dictionary management allows one to do that, at a cost I'll admit, but one that is much easier to swallow. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Thursday, May 29, 2003 3:25 PM To: Multiple recipients of list ORACLE-L After reading the documents I've recommended using LOCAL, UNIFORM, AUTO as the options for tablespace management. Does anyone have any bad experiences with these? AUTOALLOCATE seems to come up with extents that are much smaller than I want and MANUAL segment management requires the use of FREELISTs (and I know that there are problems with freelists freeing up space correctly, especially in a parallel environment). I can't find any basis for making a decision between UNDO and ROLLBACK SEGMENTS. Does anyone have any experience or recommendations about UNDO usage? The database will be a materialize view replication of a transaction master that is being used for decision support and has a 15 minute update/refresh cycle. Basically, people can run queries against the snapshot without impacting the master. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day 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: Goulet, Dick 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: Goulet, Dick 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: Richard Foote 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: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web
Re: RE: Tablespace management.
What Oracle documentation would that be? At 09:39 AM 5/30/2003 -0800, you wrote: i read some oracle documentation that recommends you keep the number of extents below 1024. do you feel that this is inaccurate in an LMT? What if Im stuck with dictionary tablespacse and am not allowed to change? Does it matter? I do keep all my extents uniform. I thought there were issuse with contention on FET$ and UET$ in dictionary managed tablespaces for a transaction database? or am I just wrong? Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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: Tablespace management.
Additional downside item: * Queries against DBA_EXTENTS will take a bit longer to return. - Kirti --- Cary Millsap [EMAIL PROTECTED] wrote: Wow. Maybe someone on the list has the time and motive to construct a test to determine how many extents for a segment in a ULMT are bad. My guess from some tests we did a couple of years ago is that it will take hundreds of thousands of extents before even DROP performance will suffer. And I can't think of *anything* that would make having even hundreds of millions of extents a bad idea for INSERTs, UPDATEs, MERGEs, or DELETEs. The only possible downsides of huge numbers of extents that I can think of are perhaps: * During the INSERT, UPDATE, or MERGE, what is the overhead of the actual allocation of the ULMT extent? (This actually may have nothing to do with how many extents are already there.) * During checkpoints on RAC systems, does the number of extents matter the way it did when Jonathan Lewis showed a problem with DMT and OPS a few years ago? * Does a huge bitmap section in the head of a data file cause any performance problems for backup and recovery? Aside from that, I can't imagine any more downside of huge numbers of ULMT extents than there is from having the Unix filesystem extents that most of us have right now and never notice. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Reykjavik, Ottawa, Dallas, Washington, Denver, Sydney - Visit www.hotsos.com for schedule details... -Original Message- Sent: Friday, May 30, 2003 8:50 AM To: Multiple recipients of list ORACLE-L Jared, It's rather simple. If you follow the rules of third normal form you have a table with a certain number of rows, a second with a certain number of rows for each row in the first table. Obviously the second table needs more space than the first. Now if you use Dictionary management you can set the storage parameters of each table individually. But if your using local management they both have the same extent sizes. This leads one to having the extent sizes smaller to accommodate the first table and large numbers of extents for the second table. True fragmentation, namely those small useless extents that land between larger used extents, is eliminated in local management but then I have not had those problems with dictionary management either, unless someone makes the case for moving a table but that's very rare. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Thursday, May 29, 2003 8:25 PM To: [EMAIL PROTECTED] Cc: Goulet, Dick Importance: High Dick, I'm trying to follow your line of thought, but I think I missed the path. Objects may not have the same storage requirements, but what does that matter? The only way I can make sense of what you say is if trying to have all objects occupy a single extent, and there's not much point in that. Jared Goulet, Dick [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/29/2003 03:51 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Tablespace management. Thomas, With the exception of temp and rollback tablespaces I have not user locally managed tablespaces just because all objects must have the same sized extents. I do not see most tables sharing an equal need for storage and using dictionary management allows one to do that, at a cost I'll admit, but one that is much easier to swallow. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Thursday, May 29, 2003 3:25 PM To: Multiple recipients of list ORACLE-L After reading the documents I've recommended using LOCAL, UNIFORM, AUTO as the options for tablespace management. Does anyone have any bad experiences with these? AUTOALLOCATE seems to come up with extents that are much smaller than I want and MANUAL segment management requires the use of FREELISTs (and I know that there are problems with freelists freeing up space correctly, especially in a parallel environment). I can't find any basis for making a decision between UNDO and ROLLBACK SEGMENTS. Does anyone have any experience or recommendations about UNDO usage? The database will be a materialize view replication of a transaction master that is being used for decision support and has a 15 minute update/refresh cycle. Basically, people can run queries against the snapshot without impacting the master. __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538
RE: RE: Tablespace management.
The How To Stop Defragmenting... paper says it in section 2.1.4. Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Wolfgang Breitling [mailto:[EMAIL PROTECTED] Sent: Friday, May 30, 2003 1:15 PM To: Multiple recipients of list ORACLE-L Subject: Re: RE: Tablespace management. What Oracle documentation would that be? At 09:39 AM 5/30/2003 -0800, you wrote: i read some oracle documentation that recommends you keep the number of extents below 1024. do you feel that this is inaccurate in an LMT? What if Im stuck with dictionary tablespacse and am not allowed to change? Does it matter? I do keep all my extents uniform. I thought there were issuse with contention on FET$ and UET$ in dictionary managed tablespaces for a transaction database? or am I just wrong? Wolfgang Breitling -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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: RE: Tablespace management.
To quote the paper: Oracle supports an unlimited number of extents in a segment. The performance for DML operations is largely independent of the number of extents in the segment. However, certain DDL operations such as dropping and truncating of segments are sensitive to the number of extents. Performance measures for these operations have shown that a few thousand extents can be supported by Oracle without a significant impact on performance. A reasonable maximum has been determined to be 4096. The goal of our recommended algorithm is to keep the number of extents below 1024 which is well within the range that Oracle can efficiently handle. When a segment reaches 1024 extents it is a candidate to be moved to the next larger extent size tablespace. The segment does not necessarily have to be moved immediately or at all. The segment may be near its peak steady state size, in which case even if it has a few thousand extents, it should be left where it is. It is only the segments which are growing that have to be targeted and potentially moved to tablespaces with larger extents. A few comments: - This was written in the days of DMTs, so not everything that is said applies to LMTs. The nr of extent stuff certainly does not. - Event within the confines of DMTs it clearly states that only drop and truncate are sensitive to the nr of extents (because of the necessary DML to FET$ and UET$). - And even then, 1024 is not really a limit, just a recommended comfort level: The goal of our recommended algorithm is to keep the number of extents below 1024 which is well within the range that Oracle can efficiently handle and The segment does not necessarily have to be moved immediately or at all At 11:59 AM 5/30/2003 -0800, you wrote: The How To Stop Defragmenting... paper says it in section 2.1.4. Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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: RE: Tablespace management.
Wolfgang, I agree. I wasn't arguing a point, but merely pointing out a possible source of the information you had requested. Have a weekend! :) Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Wolfgang Breitling [mailto:[EMAIL PROTECTED] Sent: Friday, May 30, 2003 4:00 PM To: Multiple recipients of list ORACLE-L Subject: RE: RE: Tablespace management. To quote the paper: Oracle supports an unlimited number of extents in a segment. The performance for DML operations is largely independent of the number of extents in the segment. However, certain DDL operations such as dropping and truncating of segments are sensitive to the number of extents. Performance measures for these operations have shown that a few thousand extents can be supported by Oracle without a significant impact on performance. A reasonable maximum has been determined to be 4096. The goal of our recommended algorithm is to keep the number of extents below 1024 which is well within the range that Oracle can efficiently handle. When a segment reaches 1024 extents it is a candidate to be moved to the next larger extent size tablespace. The segment does not necessarily have to be moved immediately or at all. The segment may be near its peak steady state size, in which case even if it has a few thousand extents, it should be left where it is. It is only the segments which are growing that have to be targeted and potentially moved to tablespaces with larger extents. A few comments: - This was written in the days of DMTs, so not everything that is said applies to LMTs. The nr of extent stuff certainly does not. - Event within the confines of DMTs it clearly states that only drop and truncate are sensitive to the nr of extents (because of the necessary DML to FET$ and UET$). - And even then, 1024 is not really a limit, just a recommended comfort level: The goal of our recommended algorithm is to keep the number of extents below 1024 which is well within the range that Oracle can efficiently handle and The segment does not necessarily have to be moved immediately or at all At 11:59 AM 5/30/2003 -0800, you wrote: The How To Stop Defragmenting... paper says it in section 2.1.4. Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA Wolfgang Breitling -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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: Tablespace management.
Personally, I think the issue of tablespace fragmentation has always been highly overrated. I'll use one of our databases as an example. It's a 3rd party app, and has had only a little maintence on the extent sizes. When I catch one growing quickly, I will increase the next_extent size. This was until recently an 8.0.4 database. I had considered reorging to make the extents more uniform, but only briefly. I'm going to try and migrate to LMT later this year when it gets upgraded to 9i. ( see numbers below ) Used_bytes is all space consumed by the schema. Free bytes is all chunks of free space greater than 8m (1024 blocks) in size. Frag_bytes is all chunks of space = 8m, and may or may not get used. Next_extent are all distinct values for NEXT_EXTENT on tables and indexes for the schema. Though there are next_extent sizes less than 8m, I'm not looking at which tablespace they are in and am just making a blanket assumption that chunks 1024 blocks will not get used. Even with this extent management free for all, the database has ~30m of unusable space. ~30m of unusable space out of 366g is 0.008% wasted space. It's up to the individual DBA to determine if s/he wants to remove all possibilty of fragmentation to avoid wasted space. I just can't see where it's really worth the effort. As as I'm concerned, the advantage of LMT's is not to reduce fragmentation, cuz frankly, I don't care. The advantages are avoiding possible contention on the ST latch, ( and that was mostly eliminated with true temporary temp tablespaces ), and eliminating the huge amount of recursive SQL that is generated by truncating or dropping an object with many extents. Jared USED_BYTES 366,830,100,480 1 row selected. FREE_BYTES 110,213,046,272 1 row selected. FRAG_BYTES 29,548,544 NEXT EXTENT -- 16,384 40,960 81,920 163,840 516,096 655,360 1,048,576 2,088,960 2,621,440 3,145,728 4,194,304 5,242,880 6,291,456 10,485,760 12,582,912 15,728,640 18,874,368 20,971,520 26,214,400 31,457,280 41,943,040 52,428,800 62,914,560 83,886,080 94,371,840 104,857,600 115,343,360 241,172,480 314,572,800 29 rows selected. Goulet, Dick [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/30/2003 10:39 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Tablespace management. Steve, I'm not sure I'd call all of the functionality that has been added over the years worth it. Way too many of them have caused more trouble than their worth, like descending indexes. And given the drivel that I've seen from many a third party vendor in the past (PeopleSoft and their damned 16K extents) this can certainly get turned into another nightmare. As far as fragmentation is concerned, I've NOT had to do any in the last few years, mainly due to spending a lot of time effort to get computing storage needs into an exact science around here. That has been due to disk storage space not being an invisible cost item, but instead a significant one that we're constantly battling with. Sure they've become cheaper, but when our buying GB's of the stuff, mirrored, from a reliable vendor those half MB's wasted begin to add up FAST. Therefore I still contend that everything inside a single tablespace does not need a uniform extent size. If one size fits all was absolutely ! true there would be a lot less problems in this world. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Friday, May 30, 2003 1:06 PM To: Multiple recipients of list ORACLE-L I think you're missing the point of the last message. What's wrong with multiple extents if the extent size is a multiple of a multiblock read? What's wrong with having two tablespaces? I'd definitely suggest reading How to Stop Defragmenting and Start Living: The Definitive Word on Fragmentation. (http://otn.oracle.com/deploy/availability/pdf/defrag.pdf) No one is suggesting *everything* should have a single extent size but everything in a tablespace should. LMT is the future and dovetails nicely with a lot of the functionality we've seen added in recent releases. What good are online table/index rebuilds if the space reclaimed is far outweighed by the space wasted by the fragmentation left behind? S- On Fri, 30 May 2003, Goulet, Dick wrote: Richard, My troubles come mainly form PeopleSoft and some in-house created applications. I'll use the in-house applications as the example since their simpler. Our CIM system has tables that contain very few rows of data, like the identification information for each robot(CELLS). Now there are only 30
Tablespace management.
After reading the documents I've recommended using LOCAL, UNIFORM, AUTO as the options for tablespace management. Does anyone have any bad experiences with these? AUTOALLOCATE seems to come up with extents that are much smaller than I want and MANUAL segment management requires the use of FREELISTs (and I know that there are problems with freelists freeing up space correctly, especially in a parallel environment). I can't find any basis for making a decision between UNDO and ROLLBACK SEGMENTS. Does anyone have any experience or recommendations about UNDO usage? The database will be a materialize view replication of a transaction master that is being used for decision support and has a 15 minute update/refresh cycle. Basically, people can run queries against the snapshot without impacting the master. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day 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: Tablespace management.
Thomas - Oracle recommended Local and Uniform, so that is what I use for everything. It has worked out great. I even use autoextend and that hasn't bitten me but a couple of times. This is on 8.1.6 and 9.2. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 29, 2003 2:25 PM To: Multiple recipients of list ORACLE-L After reading the documents I've recommended using LOCAL, UNIFORM, AUTO as the options for tablespace management. Does anyone have any bad experiences with these? AUTOALLOCATE seems to come up with extents that are much smaller than I want and MANUAL segment management requires the use of FREELISTs (and I know that there are problems with freelists freeing up space correctly, especially in a parallel environment). I can't find any basis for making a decision between UNDO and ROLLBACK SEGMENTS. Does anyone have any experience or recommendations about UNDO usage? The database will be a materialize view replication of a transaction master that is being used for decision support and has a 15 minute update/refresh cycle. Basically, people can run queries against the snapshot without impacting the master. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day 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: DENNIS WILLIAMS 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: Tablespace management.
Thomas, With the exception of temp and rollback tablespaces I have not user locally managed tablespaces just because all objects must have the same sized extents. I do not see most tables sharing an equal need for storage and using dictionary management allows one to do that, at a cost I'll admit, but one that is much easier to swallow. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Thursday, May 29, 2003 3:25 PM To: Multiple recipients of list ORACLE-L After reading the documents I've recommended using LOCAL, UNIFORM, AUTO as the options for tablespace management. Does anyone have any bad experiences with these? AUTOALLOCATE seems to come up with extents that are much smaller than I want and MANUAL segment management requires the use of FREELISTs (and I know that there are problems with freelists freeing up space correctly, especially in a parallel environment). I can't find any basis for making a decision between UNDO and ROLLBACK SEGMENTS. Does anyone have any experience or recommendations about UNDO usage? The database will be a materialize view replication of a transaction master that is being used for decision support and has a 15 minute update/refresh cycle. Basically, people can run queries against the snapshot without impacting the master. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day 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: Goulet, Dick 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: Tablespace management.
Dick, I'm trying to follow your line of thought, but I think I missed the path. Objects may not have the same storage requirements, but what does that matter? The only way I can make sense of what you say is if trying to have all objects occupy a single extent, and there's not much point in that. Jared Goulet, Dick [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/29/2003 03:51 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Tablespace management. Thomas, With the exception of temp and rollback tablespaces I have not user locally managed tablespaces just because all objects must have the same sized extents. I do not see most tables sharing an equal need for storage and using dictionary management allows one to do that, at a cost I'll admit, but one that is much easier to swallow. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Thursday, May 29, 2003 3:25 PM To: Multiple recipients of list ORACLE-L After reading the documents I've recommended using LOCAL, UNIFORM, AUTO as the options for tablespace management. Does anyone have any bad experiences with these? AUTOALLOCATE seems to come up with extents that are much smaller than I want and MANUAL segment management requires the use of FREELISTs (and I know that there are problems with freelists freeing up space correctly, especially in a parallel environment). I can't find any basis for making a decision between UNDO and ROLLBACK SEGMENTS. Does anyone have any experience or recommendations about UNDO usage? The database will be a materialize view replication of a transaction master that is being used for decision support and has a 15 minute update/refresh cycle. Basically, people can run queries against the snapshot without impacting the master. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day 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: Goulet, Dick 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).
Re: Tablespace management.
Hi Jared, I agree. By placing different segments in the same LMT doesn't mean that they are sharing an equal need for storage. One segment can grab 5 extents, another can grab 100, they grab as much storage as they require. And without causing fragmentation, without causing stress or contention on the DD My idea of the optimal number of extents for a segment is the number of extents you *plan* for the segment. You meet that criteria and within reason you can't go too far wrong. If were possible to set a maxextents for segments in a LMT, I would struggle to find disadvantages in comparison. Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, May 30, 2003 11:24 AM Dick, I'm trying to follow your line of thought, but I think I missed the path. Objects may not have the same storage requirements, but what does that matter? The only way I can make sense of what you say is if trying to have all objects occupy a single extent, and there's not much point in that. Jared Goulet, Dick [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/29/2003 03:51 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Tablespace management. Thomas, With the exception of temp and rollback tablespaces I have not user locally managed tablespaces just because all objects must have the same sized extents. I do not see most tables sharing an equal need for storage and using dictionary management allows one to do that, at a cost I'll admit, but one that is much easier to swallow. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Thursday, May 29, 2003 3:25 PM To: Multiple recipients of list ORACLE-L After reading the documents I've recommended using LOCAL, UNIFORM, AUTO as the options for tablespace management. Does anyone have any bad experiences with these? AUTOALLOCATE seems to come up with extents that are much smaller than I want and MANUAL segment management requires the use of FREELISTs (and I know that there are problems with freelists freeing up space correctly, especially in a parallel environment). I can't find any basis for making a decision between UNDO and ROLLBACK SEGMENTS. Does anyone have any experience or recommendations about UNDO usage? The database will be a materialize view replication of a transaction master that is being used for decision support and has a 15 minute update/refresh cycle. Basically, people can run queries against the snapshot without impacting the master. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day 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: Goulet, Dick 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: Tablespace management.
Dick, Have you considered the size and placement of the tables in like LMT's to allow the planned use of equal extents? I use LMT's and have set my own sizeing to the sizes of the tables and the table activity. Some LMT's are 4K for small tables and others are 40M for large tables. When I look at the free space in the tablespace there is almost always zero free. The next major insert allocates an extent and that is used up in a short time. When the year end functions are performed the partition is changed to read only and I do not have to resize to keep wasted space to a minimum. Usinf LMT's and partitioning is a combination that I find invaluable compared to the confusion created with the different next extent sizing that was used here on 7.3.4. Ron [EMAIL PROTECTED] 05/29/03 06:51PM Thomas, With the exception of temp and rollback tablespaces I have not user locally managed tablespaces just because all objects must have the same sized extents. I do not see most tables sharing an equal need for storage and using dictionary management allows one to do that, at a cost I'll admit, but one that is much easier to swallow. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Thursday, May 29, 2003 3:25 PM To: Multiple recipients of list ORACLE-L After reading the documents I've recommended using LOCAL, UNIFORM, AUTO as the options for tablespace management. Does anyone have any bad experiences with these? AUTOALLOCATE seems to come up with extents that are much smaller than I want and MANUAL segment management requires the use of FREELISTs (and I know that there are problems with freelists freeing up space correctly, especially in a parallel environment). I can't find any basis for making a decision between UNDO and ROLLBACK SEGMENTS. Does anyone have any experience or recommendations about UNDO usage? The database will be a materialize view replication of a transaction master that is being used for decision support and has a 15 minute update/refresh cycle. Basically, people can run queries against the snapshot without impacting the master. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day 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: Goulet, Dick 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: Ron Rogers 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).
