RE: datafile sizing question
What if we need Extent SIZES Greater than 20 MB to Check Fragmentation ? -Original Message- Sent: Thursday, September 26, 2002 2:33 PM To: Multiple recipients of list ORACLE-L We tend to use multiples of 1Gb and add 1 Mb to the file so that we get 2001, 10001 Mb etc Solaris 2.8 LMT uniform extents range from 64K to 20Mb John -Original Message- Sent: 25 September 2002 19:44 To: Multiple recipients of list ORACLE-L We created two datafiles of 16GB+64K all LMT autoallocate ... never gave a problem. A basic testing concluded that fixed size allocation of 128M caused unnecessary delays whereas autoallocate was much faster. I don't know the full details yet, but I'll know soon. Of course this is undergoing lot of testing (the whole application, no problems with datafiles yet), but we will probably settle for about 4GB+64K. This is AIX5L 64 bit running Oracle 9iR2. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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.com -- Author: VIVEK_SHARMA 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: datafile sizing question
with evenly sized extents, there is no such thing as fragmentation anymore and Oracle can deal with objects with numbers of extents up to about 4000 before it starts to slow down a bit. --- VIVEK_SHARMA [EMAIL PROTECTED] wrote: What if we need Extent SIZES Greater than 20 MB to Check Fragmentation ? -Original Message- Sent: Thursday, September 26, 2002 2:33 PM To: Multiple recipients of list ORACLE-L We tend to use multiples of 1Gb and add 1 Mb to the file so that we get 2001, 10001 Mb etc Solaris 2.8 LMT uniform extents range from 64K to 20Mb John -Original Message- Sent: 25 September 2002 19:44 To: Multiple recipients of list ORACLE-L We created two datafiles of 16GB+64K all LMT autoallocate ... never gave a problem. A basic testing concluded that fixed size allocation of 128M caused unnecessary delays whereas autoallocate was much faster. I don't know the full details yet, but I'll know soon. Of course this is undergoing lot of testing (the whole application, no problems with datafiles yet), but we will probably settle for about 4GB+64K. This is AIX5L 64 bit running Oracle 9iR2. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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.com -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: datafile sizing question
Title: RE: datafile sizing question Rachel, Are there any studies or papers that test and explain this new magic 4000 extents number? My manager is excited about LMT, but no so excited about number of extents. So, if there is a good paper, I can make him feel happy about this ... Thanks in advance Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Monday, September 30, 2002 7:03 AM To: Multiple recipients of list ORACLE-L Subject: RE: datafile sizing question with evenly sized extents, there is no such thing as fragmentation anymore and Oracle can deal with objects with numbers of extents up to about 4000 before it starts to slow down a bit. *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: datafile sizing question
Title: RE: datafile sizing question Do your own testing. Don't rely on papers. Prove it yourself. It's easy. There are two types of "performance" implied in this discussion about extent allocation and deallocation: performance of SQL statements like SELECT, INSERT, UPDATE, DELETE (i.e. DML) performance of statements like CREATE, ALTER, DROP, and TRUNCATE (i.e. DDL) There is no reason to suggest that the performance of DML might be affected by the number of extents, whether 1 extent or 500,000 extents. Think about it. Random, single-block reads (i.e. indexed scans) are completely unaffected by Oracle extent size and number; they are block-level accesses, after all. They care nothing about the concept of extent. Sequential, multi-block reads (i.e. full table scans, fast full index scans) can only be affected if the extent size is extremely small but is completely unaffected by the number of extents.Extremely small extents can obviously affect a multi-block read if theyconsistently limit thenumber of blocks that can be read. Since testingthis requires some non-trivial resources (i.e. test data and disk space)to prove, I'll leave the proving to those who have both (in addition totime). This leaves DDL, which is mercifully easy to test on any environment using locally-managed tablespaces. Do *not* do this type of testing in dictionary-managed tablespaces, as there is no point. LMTs were created to alleviate the problems you'd be experiencing with DMTs... Try an exercise like the following in SQL*Plus: set timing on create table bumpf (xxx number) tablespace LMT-tsname; begin for i in 1..COUNTER loop execute immediate 'alter table bumpf allocate extent'; end loop; end loop; / drop table bumpf; Re-run the test for different values of COUNTER, all the way up to values like 250,000 or 500,000, if you like. The timings for CREATE TABLE should be consistent, of course, as it is the exact same command each time. The time spent in the PL/SQL loop should be roughly linear with the value of COUNTER, the point being that each ALLOCATE EXTENT takes roughly the same amount of time. You might observe an "elbow" in the plotted curve of timings at some point which Rachel suggested at 4000 but I think will vary depending on your environment. On my laptop, I've seen the curve stay linear up into the 100,000s. The time spent in DROPmay not vary a great deal; it should be roughly linear with the value of COUNTER but I find that it is much better than linear, which leads me to believe that some parts of a DROP/TRUNCATE operation are asynchronous. Try it out! - Original Message - From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Monday, September 30, 2002 6:33 AM Subject: RE: datafile sizing question Rachel, Are there any studies or papers that test and explain this new magic 4000 extents number? My manager is excited about LMT, but no so excited about number of extents. So, if there is a good paper, I can make him feel happy about this ... Thanks in advance Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Monday, September 30, 2002 7:03 AM To: Multiple recipients of list ORACLE-L Subject: RE: datafile sizing question with evenly sized extents, there is no such thing as fragmentation anymore and Oracle can deal with objects with numbers of extents up to about 4000 before it starts to slow down a bit.
Re: datafile sizing question
Fragmentation or tablespace fragmentation does not simply mean more than one extent, as it appears you are assuming. Also, it is an obsolete concept where LMTs are involved, in all but a few difficult-to-imagine situations. Please read one or more of the following: Craig Shallahamer's All about Oracle database Fragmentation on www.orapub.com, Cary Millsap's Oracle7 Space Management or Juan Loaiza et al's How to Stop Defragmenting and Start Living both on www.hotsos.com, or Tim Gorman's Myths about Extents and Performance at www.evdbt.com/papers.htm... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, September 30, 2002 2:43 AM What if we need Extent SIZES Greater than 20 MB to Check Fragmentation ? -Original Message- Sent: Thursday, September 26, 2002 2:33 PM To: Multiple recipients of list ORACLE-L We tend to use multiples of 1Gb and add 1 Mb to the file so that we get 2001, 10001 Mb etc Solaris 2.8 LMT uniform extents range from 64K to 20Mb John -Original Message- Sent: 25 September 2002 19:44 To: Multiple recipients of list ORACLE-L We created two datafiles of 16GB+64K all LMT autoallocate ... never gave a problem. A basic testing concluded that fixed size allocation of 128M caused unnecessary delays whereas autoallocate was much faster. I don't know the full details yet, but I'll know soon. Of course this is undergoing lot of testing (the whole application, no problems with datafiles yet), but we will probably settle for about 4GB+64K. This is AIX5L 64 bit running Oracle 9iR2. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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.com -- Author: VIVEK_SHARMA 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.com -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: datafile sizing question
Raj Print http://otn.oracle.com/deploy/availability/pdf/defrag.pdf http://otn.oracle.com/deploy/availability/pdf/defrag.pdf - very well-written, direct from Oracle's site, so he will accept it as official. BTW - In this paper, for Oracle 8 and above, the correct extent sizes are 120-k, 4-m, and 128-m. The reasons you want to use these specific sizes are explained in the paper. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, September 30, 2002 7:33 AM To: Multiple recipients of list ORACLE-L Rachel, Are there any studies or papers that test and explain this new magic 4000 extents number? My manager is excited about LMT, but no so excited about number of extents. So, if there is a good paper, I can make him feel happy about this ... Thanks in advance Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Monday, September 30, 2002 7:03 AM To: Multiple recipients of list ORACLE-L with evenly sized extents, there is no such thing as fragmentation anymore and Oracle can deal with objects with numbers of extents up to about 4000 before it starts to slow down a bit. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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: datafile sizing question
I haven't seen any papers.. I was told this (4096 is the exact number) in an Internals class. However, there are lots of papers out there saying multiple extents are not a problem, and you should be able to find them on the web. --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Rachel, Are there any studies or papers that test and explain this new magic 4000 extents number? My manager is excited about LMT, but no so excited about number of extents. So, if there is a good paper, I can make him feel happy about this ... Thanks in advance Raj __ Rajendra JamadagniMIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Monday, September 30, 2002 7:03 AM To: Multiple recipients of list ORACLE-L with evenly sized extents, there is no such thing as fragmentation anymore and Oracle can deal with objects with numbers of extents up to about 4000 before it starts to slow down a bit. *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 __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: datafile sizing question
Title: RE: datafile sizing question Thanks you Dennis, Rachel, Tim for the pointers. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Sent: Monday, September 30, 2002 9:58 AM To: Multiple recipients of list ORACLE-L Subject: RE: datafile sizing question Raj Print http://otn.oracle.com/deploy/availability/pdf/defrag.pdf http://otn.oracle.com/deploy/availability/pdf/defrag.pdf - very well-written, direct from Oracle's site, so he will accept it as official. BTW - In this paper, for Oracle 8 and above, the correct extent sizes are 120-k, 4-m, and 128-m. The reasons you want to use these specific sizes are explained in the paper. *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: datafile sizing question
Title: RE: datafile sizing question If Next Extent Sizing is NON-Uniform for an LMT , will the Larger Number of Extents cause Fragmentation Performance Degradation ? If so What Number of Extents may be Considered as a Candidate for DE-Fragmentation ? NOTE - We have been Manually Specifyingthe Size of the NEXT_EXTENT of Objects in LMTs by Converting ALLOCATION_TYPE (sys.dba_tablespaces) from "SYSTEM" to "USER" for respective Tablespaces to Check Growth to Larger Numbers of Extents 100-200 Extents we Consider as a Candidate for DE-Fragmentation using exp/imp OR ALTER TABLE/INDEX ... MOVE with Bigger Extent Sizes Oracle 8.1.7 -Original Message-From: Tim Gorman [mailto:[EMAIL PROTECTED]]Sent: Monday, September 30, 2002 7:48 PMTo: Multiple recipients of list ORACLE-LSubject: Re: datafile sizing question Do your own testing. Don't rely on papers. Prove it yourself. It's easy. There are two types of "performance" implied in this discussion about extent allocation and deallocation: performance of SQL statements like SELECT, INSERT, UPDATE, DELETE (i.e. DML) performance of statements like CREATE, ALTER, DROP, and TRUNCATE (i.e. DDL) There is no reason to suggest that the performance of DML might be affected by the number of extents, whether 1 extent or 500,000 extents. Think about it. Random, single-block reads (i.e. indexed scans) are completely unaffected by Oracle extent size and number; they are block-level accesses, after all. They care nothing about the concept of extent. Sequential, multi-block reads (i.e. full table scans, fast full index scans) can only be affected if the extent size is extremely small but is completely unaffected by the number of extents.Extremely small extents can obviously affect a multi-block read if theyconsistently limit thenumber of blocks that can be read. Since testingthis requires some non-trivial resources (i.e. test data and disk space)to prove, I'll leave the proving to those who have both (in addition totime). This leaves DDL, which is mercifully easy to test on any environment using locally-managed tablespaces. Do *not* do this type of testing in dictionary-managed tablespaces, as there is no point. LMTs were created to alleviate the problems you'd be experiencing with DMTs... Try an exercise like the following in SQL*Plus: set timing on create table bumpf (xxx number) tablespace LMT-tsname; begin for i in 1..COUNTER loop execute immediate 'alter table bumpf allocate extent'; end loop; end loop; / drop table bumpf; Re-run the test for different values of COUNTER, all the way up to values like 250,000 or 500,000, if you like. The timings for CREATE TABLE should be consistent, of course, as it is the exact same command each time. The time spent in the PL/SQL loop should be roughly linear with the value of COUNTER, the point being that each ALLOCATE EXTENT takes roughly the same amount of time. You might observe an "elbow" in the plotted curve of timings at some point which Rachel suggested at 4000 but I think will vary depending on your environment. On my laptop, I've seen the curve stay linear up into the 100,000s. The time spent in DROPmay not vary a great deal; it should be roughly linear with the value of COUNTER but I find that it is much better than linear, which leads me to believe that some parts of a DROP/TRUNCATE operation are asynchronous. Try it out! - Original Message - From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Monday, September 30, 2002 6:33 AM Subject: RE: datafile sizing question Rachel, Are there any studies or papers that test and explain this new magic 4000 extents number? My manager is excited about LMT, but no so excited about number of extents. So, if there is a good paper, I can make him feel happy about this ... Thanks in advance Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Monday, September 30, 2002 7:03 AM To: Multiple recipients of list ORACLE-L Subject: RE: datafile sizing question with evenly sized extents, there is no such thing as fragmentation anymore and Oracle can deal with objects with numbers of extents up to about 4000 before it starts to slow down a bit.
Re: datafile sizing question
Title: RE: datafile sizing question I don't know if you intended to "shout", but using color for your reply certainly does so... Can you prove any benefit from the extraordinaryactions of overriding LMT extent control and using EXP/IMP, ALTER TABLE ... MOVE, ALTER INDEX ... REBUILD,or whatever, when number of extents is the only criteria? If so, was it DML or DDL statementswhich demonstratedimproved performance? Also, if there was any testing performed, then how did you isolate the issue of "number/size of extents" away from the issues of "row-migration" and 'blocks made empty due to deletion activity'? What you are referring to as "fragmentation" (whichhas never meantsimply "the number of extents") does not equate to "performance degradation" at all. There is a difference in the possible performanceimpact of number/size of extents on DML statements as opposed toDDL statements, as previously explained. Simply put, the "conventional wisdom" left overregarding extents indictionary-managed tablespaces has little or no application in the world since the introduction of locally-managed tablespaces. For example, ahundred million extents for a table or indexwould *not* impact the performance of indexed scans (i.e.DML)in *any* way whatsoever (due to single-block, random-access reads). However, this situation would be catastrophic to any DDL involving extent allocation/deallocationin a dictionary-managed tablespace (requiring database recreation) as *every* other DDL involving extent allocation/deallocation could be crippled (due to abused cluster tables in the UET$/SEG$ cluster in the data dictionary). By the same token, dropping or truncatinga table with a hundred million extents would take a while even in locally-managed tablespaces, but the impact would not be as globally catastrophic for the rest of the segments in the database or for the database's data dictionary itself. - Original Message - From: VIVEK_SHARMA To: [EMAIL PROTECTED] ; [EMAIL PROTECTED] Sent: Monday, September 30, 2002 9:05 AM Subject: RE: datafile sizing question If Next Extent Sizing is NON-Uniform for an LMT , will the Larger Number of Extents cause Fragmentation Performance Degradation ? If so What Number of Extents may be Considered as a Candidate for DE-Fragmentation ? NOTE - We have been Manually Specifyingthe Size of the NEXT_EXTENT of Objects in LMTs by Converting ALLOCATION_TYPE (sys.dba_tablespaces) from "SYSTEM" to "USER" for respective Tablespaces to Check Growth to Larger Numbers of Extents 100-200 Extents we Consider as a Candidate for DE-Fragmentation using exp/imp OR ALTER TABLE/INDEX ... MOVE with Bigger Extent Sizes Oracle 8.1.7 -Original Message-From: Tim Gorman [mailto:[EMAIL PROTECTED]]Sent: Monday, September 30, 2002 7:48 PMTo: Multiple recipients of list ORACLE-LSubject: Re: datafile sizing question Do your own testing. Don't rely on papers. Prove it yourself. It's easy. There are two types of "performance" implied in this discussion about extent allocation and deallocation: performance of SQL statements like SELECT, INSERT, UPDATE, DELETE (i.e. DML) performance of statements like CREATE, ALTER, DROP, and TRUNCATE (i.e. DDL) There is no reason to suggest that the performance of DML might be affected by the number of extents, whether 1 extent or 500,000 extents. Think about it. Random, single-block reads (i.e. indexed scans) are completely unaffected by Oracle extent size and number; they are block-level accesses, after all. They care nothing about the concept of extent. Sequential, multi-block reads (i.e. full table scans, fast full index scans) can only be affected if the extent size is extremely small but is completely unaffected by the number of extents.Extremely small extents can obviously affect a multi-block read if theyconsistently limit thenumber of blocks that can be read. Since testingthis requires some non-trivial resources (i.e. test data and disk space)to prove, I'll leave the proving to those who have both (in addition totime). This leaves DDL, which is mercifully easy to test on any environment using locally-managed tablespaces. Do *not* do this type of testing in dictionary-managed tablespaces, as there is no point. LMTs were created to alleviate the problems you'd be experiencing with DMTs... Try an exercise like the following in SQL*Plus: set timing on create table bumpf (xxx number) tablespace LMT-tsname; begin for i in 1..COUNTER loop execute immediate 'alter table bumpf allocate extent';
RE: datafile sizing question
If I'm feeling particularly AR, or just want to revel in the fact that I can do simple multiplication and addition without error, I'll figure the datafile size to the byte. e.g. 200m + 64k for an LMT is 2097217536 bytes. On NT I tend to use NNNMm on 8.0 and NNNM on 8i LMT's where the extent size is 4m or less, as 4m isn't enough space to worry about wasting. Unless of course, I'm feeling particular AR. Jared Hand, Michael T [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/26/2002 08:33 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: datafile sizing question I also have a just curious question. Do most interchange the values 1000K for 1M or 1000M for 1G? I try to be precise in my usage, but I guess that's just the AR size of my personality. OK, I'll go find my pills now. ;) By the way, we use a maximum size of 4Gb+8k file size but, I've never has a reason to doubt the DEC/Compaq 64bit file systems. Mike -Original Message- Sent: Thursday, September 26, 2002 5:03 AM To: Multiple recipients of list ORACLE-L We tend to use multiples of 1Gb and add 1 Mb to the file so that we get 2001, 10001 Mb etc Solaris 2.8 LMT uniform extents range from 64K to 20Mb John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hand, Michael T 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.com -- 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: datafile sizing question
this is the first time I've NOT figured the file size the AR way :) --- [EMAIL PROTECTED] wrote: If I'm feeling particularly AR, or just want to revel in the fact that I can do simple multiplication and addition without error, I'll figure the datafile size to the byte. e.g. 200m + 64k for an LMT is 2097217536 bytes. On NT I tend to use NNNMm on 8.0 and NNNM on 8i LMT's where the extent size is 4m or less, as 4m isn't enough space to worry about wasting. Unless of course, I'm feeling particular AR. Jared Hand, Michael T [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/26/2002 08:33 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: datafile sizing question I also have a just curious question. Do most interchange the values 1000K for 1M or 1000M for 1G? I try to be precise in my usage, but I guess that's just the AR size of my personality. OK, I'll go find my pills now. ;) By the way, we use a maximum size of 4Gb+8k file size but, I've never has a reason to doubt the DEC/Compaq 64bit file systems. Mike -Original Message- Sent: Thursday, September 26, 2002 5:03 AM To: Multiple recipients of list ORACLE-L We tend to use multiples of 1Gb and add 1 Mb to the file so that we get 2001, 10001 Mb etc Solaris 2.8 LMT uniform extents range from 64K to 20Mb John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hand, Michael T 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.com -- 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). __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: datafile sizing question
We tend to use multiples of 1Gb and add 1 Mb to the file so that we get 2001, 10001 Mb etc Solaris 2.8 LMT uniform extents range from 64K to 20Mb John -Original Message- Sent: 25 September 2002 19:44 To: Multiple recipients of list ORACLE-L We created two datafiles of 16GB+64K all LMT autoallocate ... never gave a problem. A basic testing concluded that fixed size allocation of 128M caused unnecessary delays whereas autoallocate was much faster. I don't know the full details yet, but I'll know soon. Of course this is undergoing lot of testing (the whole application, no problems with datafiles yet), but we will probably settle for about 4GB+64K. This is AIX5L 64 bit running Oracle 9iR2. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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: datafile sizing question
10001Mb? the uniform extent sizes map to what I'll be using as well. good to know I'm not way off track --- [EMAIL PROTECTED] wrote: We tend to use multiples of 1Gb and add 1 Mb to the file so that we get 2001, 10001 Mb etc Solaris 2.8 LMT uniform extents range from 64K to 20Mb John -Original Message- Sent: 25 September 2002 19:44 To: Multiple recipients of list ORACLE-L We created two datafiles of 16GB+64K all LMT autoallocate ... never gave a problem. A basic testing concluded that fixed size allocation of 128M caused unnecessary delays whereas autoallocate was much faster. I don't know the full details yet, but I'll know soon. Of course this is undergoing lot of testing (the whole application, no problems with datafiles yet), but we will probably settle for about 4GB+64K. This is AIX5L 64 bit running Oracle 9iR2. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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). __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: datafile sizing question
Yes, 10Gb datafiles. I think we have a 35Gb datafile somewhere but I have not looked at that database myself John -Original Message- Sent: 26 September 2002 12:03 To: Multiple recipients of list ORACLE-L 10001Mb? the uniform extent sizes map to what I'll be using as well. good to know I'm not way off track --- [EMAIL PROTECTED] wrote: We tend to use multiples of 1Gb and add 1 Mb to the file so that we get 2001, 10001 Mb etc Solaris 2.8 LMT uniform extents range from 64K to 20Mb John -Original Message- Sent: 25 September 2002 19:44 To: Multiple recipients of list ORACLE-L We created two datafiles of 16GB+64K all LMT autoallocate ... never gave a problem. A basic testing concluded that fixed size allocation of 128M caused unnecessary delays whereas autoallocate was much faster. I don't know the full details yet, but I'll know soon. Of course this is undergoing lot of testing (the whole application, no problems with datafiles yet), but we will probably settle for about 4GB+64K. This is AIX5L 64 bit running Oracle 9iR2. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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). __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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: datafile sizing question
Datafile sizing affects the speed of backup and restore, since each datafile can only be backed up or restored by one process at a time. As a result, I try to keep datafiles at uniform sizes of 2-4 Gb max. How do such large and variable-sized datafiles impact your backups and restores? Just curious... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 26, 2002 6:28 AM Yes, 10Gb datafiles. I think we have a 35Gb datafile somewhere but I have not looked at that database myself John -Original Message- Sent: 26 September 2002 12:03 To: Multiple recipients of list ORACLE-L 10001Mb? the uniform extent sizes map to what I'll be using as well. good to know I'm not way off track --- [EMAIL PROTECTED] wrote: We tend to use multiples of 1Gb and add 1 Mb to the file so that we get 2001, 10001 Mb etc Solaris 2.8 LMT uniform extents range from 64K to 20Mb John -Original Message- Sent: 25 September 2002 19:44 To: Multiple recipients of list ORACLE-L We created two datafiles of 16GB+64K all LMT autoallocate ... never gave a problem. A basic testing concluded that fixed size allocation of 128M caused unnecessary delays whereas autoallocate was much faster. I don't know the full details yet, but I'll know soon. Of course this is undergoing lot of testing (the whole application, no problems with datafiles yet), but we will probably settle for about 4GB+64K. This is AIX5L 64 bit running Oracle 9iR2. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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). __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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.com -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: datafile sizing question
Rachel - In the whitepaper How to Stop Defragmenting and Start Living, they make a big deal under LMT about selecting specific extent sizes, and those optimum sizes are different for Oracle 8i and Oracle 8. For Oracle 8i the sizes are 128k, 4m, 128m. I couldn't follow the complete logic of why they selected those sizes, but I felt that I should follow the prescription rather than get creative in my ignorance. Perhaps someone else has an explanation. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Thursday, September 26, 2002 6:03 AM To: Multiple recipients of list ORACLE-L 10001Mb? the uniform extent sizes map to what I'll be using as well. good to know I'm not way off track --- [EMAIL PROTECTED] wrote: We tend to use multiples of 1Gb and add 1 Mb to the file so that we get 2001, 10001 Mb etc Solaris 2.8 LMT uniform extents range from 64K to 20Mb John -Original Message- Sent: 25 September 2002 19:44 To: Multiple recipients of list ORACLE-L We created two datafiles of 16GB+64K all LMT autoallocate ... never gave a problem. A basic testing concluded that fixed size allocation of 128M caused unnecessary delays whereas autoallocate was much faster. I don't know the full details yet, but I'll know soon. Of course this is undergoing lot of testing (the whole application, no problems with datafiles yet), but we will probably settle for about 4GB+64K. This is AIX5L 64 bit running Oracle 9iR2. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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). __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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: datafile sizing question
Tim,
I cannot speak for the 35Gb datafile which is a one-off but for the systems
where we have
datafiles files 4Gb we invariably use EDM to break a mirror and then
RMAN proxy against that.
The mirror is then kept off-line until we are ready to do the next backup.
At that point the mirror is 're-silvered' ('timesliced' is another
expression used) and then the process starts again.
The archived redo logs are kept on a seperate volume group and secured via
RMAN.
If a large datafile has to be recovered then that vg is re-silvered (which
is very fast) and then changes from the redo logs applied.
I agree that recovery of a large datafile will take longer if the required
file is older than the copy of the broken disk. I assume that this was
discussed and agreed with the business beforehand. If we had to recover to
a point in time greater than 1 day ago then I suggest we would be in
significant trouble anyway
John
-Original Message-
Sent: 26 September 2002 14:28
To: Multiple recipients of list ORACLE-L
Datafile sizing affects the speed of backup and restore, since each datafile
can only be backed up or restored by one process at a time. As a result, I
try to keep datafiles at uniform sizes of 2-4 Gb max. How do such large and
variable-sized datafiles impact your backups and restores? Just curious...
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, September 26, 2002 6:28 AM
Yes, 10Gb datafiles.
I think we have a 35Gb datafile somewhere but I have not looked at that
database myself
John
-Original Message-
Sent: 26 September 2002 12:03
To: Multiple recipients of list ORACLE-L
10001Mb?
the uniform extent sizes map to what I'll be using as well. good to
know I'm not way off track
--- [EMAIL PROTECTED] wrote:
We tend to use multiples of 1Gb and add 1 Mb to the file so that we
get
2001, 10001 Mb etc
Solaris 2.8
LMT uniform extents range from 64K to 20Mb
John
-Original Message-
Sent: 25 September 2002 19:44
To: Multiple recipients of list ORACLE-L
We created two datafiles of 16GB+64K all LMT autoallocate ... never
gave a
problem. A basic testing concluded that fixed size allocation of 128M
caused
unnecessary delays whereas autoallocate was much faster. I don't know
the
full details yet, but I'll know soon.
Of course this is undergoing lot of testing (the whole application,
no
problems with datafiles yet), but we will probably settle for about
4GB+64K.
This is AIX5L 64 bit running Oracle 9iR2.
Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of
ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
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).
__
Do you Yahoo!?
New DSL Internet Access from SBC Yahoo!
http://sbc.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
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
RE: datafile sizing question
I also have a just curious question. Do most interchange the values 1000K for 1M or 1000M for 1G? I try to be precise in my usage, but I guess that's just the AR size of my personality. OK, I'll go find my pills now. ;) By the way, we use a maximum size of 4Gb+8k file size but, I've never has a reason to doubt the DEC/Compaq 64bit file systems. Mike -Original Message- Sent: Thursday, September 26, 2002 5:03 AM To: Multiple recipients of list ORACLE-L We tend to use multiples of 1Gb and add 1 Mb to the file so that we get 2001, 10001 Mb etc Solaris 2.8 LMT uniform extents range from 64K to 20Mb John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hand, Michael T 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: Another datafile sizing question
Darren - Let me try to clarify my answer. 1. Yes, 800-meg is not really large with today's disk systems, so that consolidation should be no problem. 2. If you are doing this type of consolidation, I think it would be a good time for you to take as step back for broader perspective. Specifically, it might be a good time for you to take a look at LMTs. Most of us DBAs only perform this type of reorganization every few years, so it is a good idea to incorporate the latest thinking. One of the reasons Oracle is moving us toward LMTs is to reduce the DBA workload, so the Microsoft heads can't keep saying not only is Oracle more expensive, but it takes a lot more management. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Wednesday, September 25, 2002 7:47 PM To: '[EMAIL PROTECTED]' Darren - My advice would be to read up on Locally Managed Tablespaces (LMT) and uniform extents. This is a new feature that will ease your management work. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, September 25, 2002 6:43 PM To: Multiple recipients of list ORACLE-L I am in the process of upgrading our databases from 8.0.5 to 8.1.7, possibly 9i depending on application certifications. I currently have a tablespace that is made up of 4 - 200mb datafiles, my first thought would be to create a 800mb datafile and move all the data into it, The growth of this tablespace is maybe 100 to 150Mb a year, and from what I understand all datafiles should be the same size. So at that point when I need another datafile, I will have to create another 800Mb datafile. Would that be a good practise, or should I stay with multiple 200/300/400/500 etc datafiles ? Thanks Darren -- Darren Browett P.EngThis message was transmitted Data Administrator using 100% recycled electrons Information and Communication Technology City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren 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.com -- 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: Another datafile sizing question
Our SAN has been setup as RAID5, so the use of single large datafiles is probably the method I will go with, using the added option of resizing once I read up on it some more :) Darren -Original Message- Sent: Wednesday, September 25, 2002 8:13 PM To: Multiple recipients of list ORACLE-L It's late at night maybe that's why I do not understand your answer but I do not see the link between LMT and the number/size of datafiles. One reason of multiple datafiles id to spread IO but since nowadays a majority of sites goes on huge disk box using raid 5 (that's what we have, the unix guys are the IT master here) multiple files is less meaningful. What I liked is a file politics where you restrained the number of file size. Here we have from 15M up to 8.5G file size with all the possibility in between. I'm trying to standardize all that. Another factor to consider is backup and recovery. Restoring a 10G file will take more time than a 2G file. In your case, if file placement is not possible than go for a 800M file and use a second one for the future growth. -- DENNIS WILLIAMS [EMAIL PROTECTED] a écrit : Darren - My advice would be to read up on Locally Managed Tablespaces (LMT) and uniform extents. This is a new feature that will ease your management work. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, September 25, 2002 6:43 PM To: Multiple recipients of list ORACLE-L I am in the process of upgrading our databases from 8.0.5 to 8.1.7, possibly 9i depending on application certifications. I currently have a tablespace that is made up of 4 - 200mb datafiles, my first thought would be to create a 800mb datafile and move all the data into it, The growth of this tablespace is maybe 100 to 150Mb a year, and from what I understand all datafiles should be the same size. So at that point when I need another datafile, I will have to create another 800Mb datafile. Would that be a good practise, or should I stay with multiple 200/300/400/500 etc datafiles ? Thanks Darren -- Darren Browett P.Eng This message was transmitted Data Administratorusing 100% recycled electrons Information and Communication Technology City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren 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.com -- 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). = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= 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: Another datafile sizing question
that is something I didn't consider, I will look into it further. Thanks Darren -Original Message- Sent: Wednesday, September 25, 2002 5:48 PM To: Multiple recipients of list ORACLE-L Is there a reason you can't just resize the existing file? -Original Message- Sent: Wednesday, September 25, 2002 7:43 PM To: Multiple recipients of list ORACLE-L I am in the process of upgrading our databases from 8.0.5 to 8.1.7, possibly 9i depending on application certifications. I currently have a tablespace that is made up of 4 - 200mb datafiles, my first thought would be to create a 800mb datafile and move all the data into it, The growth of this tablespace is maybe 100 to 150Mb a year, and from what I understand all datafiles should be the same size. So at that point when I need another datafile, I will have to create another 800Mb datafile. Would that be a good practise, or should I stay with multiple 200/300/400/500 etc datafiles ? Thanks Darren -- Darren Browett P.EngThis message was transmitted Data Administrator using 100% recycled electrons Information and Communication Technology City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren 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.com -- Author: Johnston, Tim 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.com -- Author: Browett, Darren 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: datafile sizing question
Michael - I've done it both ways, and yes, it depends on how AR you wish to be. You can convert everything to K and get pretty precise. Or you can simply create the tablespace and datafile, then query the free space and create your table accordingly. Of course LMT with uniform extents changes the rules a bit, especially for us AR types. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Thursday, September 26, 2002 10:34 AM To: Multiple recipients of list ORACLE-L I also have a just curious question. Do most interchange the values 1000K for 1M or 1000M for 1G? I try to be precise in my usage, but I guess that's just the AR size of my personality. OK, I'll go find my pills now. ;) By the way, we use a maximum size of 4Gb+8k file size but, I've never has a reason to doubt the DEC/Compaq 64bit file systems. Mike -Original Message- Sent: Thursday, September 26, 2002 5:03 AM To: Multiple recipients of list ORACLE-L We tend to use multiples of 1Gb and add 1 Mb to the file so that we get 2001, 10001 Mb etc Solaris 2.8 LMT uniform extents range from 64K to 20Mb John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hand, Michael T 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.com -- 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: Another datafile sizing question
Thanks Dennis I was going to use LMT's, I didn't mention it in my email as I thought it would be irrelevant to the question I was asking. The first database in question to move is oracle financials 11.5.7 which requires you to move to LMT's Darren -Original Message- Sent: Thursday, September 26, 2002 8:49 AM To: Multiple recipients of list ORACLE-L Darren - Let me try to clarify my answer. 1. Yes, 800-meg is not really large with today's disk systems, so that consolidation should be no problem. 2. If you are doing this type of consolidation, I think it would be a good time for you to take as step back for broader perspective. Specifically, it might be a good time for you to take a look at LMTs. Most of us DBAs only perform this type of reorganization every few years, so it is a good idea to incorporate the latest thinking. One of the reasons Oracle is moving us toward LMTs is to reduce the DBA workload, so the Microsoft heads can't keep saying not only is Oracle more expensive, but it takes a lot more management. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Sent: Wednesday, September 25, 2002 7:47 PM To: '[EMAIL PROTECTED]' Darren - My advice would be to read up on Locally Managed Tablespaces (LMT) and uniform extents. This is a new feature that will ease your management work. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, September 25, 2002 6:43 PM To: Multiple recipients of list ORACLE-L I am in the process of upgrading our databases from 8.0.5 to 8.1.7, possibly 9i depending on application certifications. I currently have a tablespace that is made up of 4 - 200mb datafiles, my first thought would be to create a 800mb datafile and move all the data into it, The growth of this tablespace is maybe 100 to 150Mb a year, and from what I understand all datafiles should be the same size. So at that point when I need another datafile, I will have to create another 800Mb datafile. Would that be a good practise, or should I stay with multiple 200/300/400/500 etc datafiles ? Thanks Darren -- Darren Browett P.EngThis message was transmitted Data Administrator using 100% recycled electrons Information and Communication Technology City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren 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.com -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren 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: datafile sizing question
Goddess: The conclusions are correct. The OS must be configured to properly handle the 64-bit architecture, thus, large files. The 32-bit limitation on 2GB files is still imposed unless this occurs. Although Oracle can accommodate 2GB files, you must have the OS handle the outside Oracle work. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Wednesday, September 25, 2002 4:39 PM To: Multiple recipients of list ORACLE-L Subject:RE: datafile sizing question I think it's automatic on Solaris 2.8 but I don't know for sure so I'd rather not rely on it. I'm going with 2001M and creating about a year's worth of tablespaces/partitions. 101 datafiles and tablespaces. Just to START. my aching typing fingers! --- Paul Baumgartel [EMAIL PROTECTED] wrote: I believe that the OS filesystem has to be configured for large file support (on HP-UX, it's a kernel parameter) to allow files 2 GB. I usually just add 1 MB to the file size to allow for the header. Personally, I'd play it safe and go with however many 2001 MB files you need to accommodate your objects. --- Rachel Carmichael [EMAIL PROTECTED] wrote: I'm betting that it's not 64-bit Oracle. See, I am the development DBA. As an employee, I get to create the scripts, but I don't get to run them or even get access as oracle to the servers. In other words, all the grunt work, all the responsibility for problems (production is managed by a hosting company) but none of the power to make sure it's right. I love my job. Why? Rachel __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karniotis, Stephen 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: datafile sizing question
Well as long as we are being AR...Don't you mean DEC/Compaq/HP :-) -Original Message- Sent: Thursday, September 26, 2002 11:34 AM To: Multiple recipients of list ORACLE-L I also have a just curious question. Do most interchange the values 1000K for 1M or 1000M for 1G? I try to be precise in my usage, but I guess that's just the AR size of my personality. OK, I'll go find my pills now. ;) By the way, we use a maximum size of 4Gb+8k file size but, I've never has a reason to doubt the DEC/Compaq 64bit file systems. Mike -Original Message- Sent: Thursday, September 26, 2002 5:03 AM To: Multiple recipients of list ORACLE-L We tend to use multiples of 1Gb and add 1 Mb to the file so that we get 2001, 10001 Mb etc Solaris 2.8 LMT uniform extents range from 64K to 20Mb John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hand, Michael T 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.com -- Author: Gesler, 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: Another datafile sizing question
Is the requirement that all datafiles be the same size a business requirement? I guess that having all datafiles the same size would be useful if you need to move them to other platters. Do you have multiple physical devices to move your datafiles around in? If you only have a single RAID device then there's no point in moving datafiles from one logical device to another (since it's all one physical device). Therefore I'm not sure that there's much point in keeping datafiles the same size. Browett, Darren To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] dbrowettcc: @city.coquitlSubject: Another datafile sizing question am.bc.ca Sent by: root 09/25/2002 07:43 PM Please respond to ORACLE-L I am in the process of upgrading our databases from 8.0.5 to 8.1.7, possibly 9i depending on application certifications. I currently have a tablespace that is made up of 4 - 200mb datafiles, my first thought would be to create a 800mb datafile and move all the data into it, The growth of this tablespace is maybe 100 to 150Mb a year, and from what I understand all datafiles should be the same size. So at that point when I need another datafile, I will have to create another 800Mb datafile. Would that be a good practise, or should I stay with multiple 200/300/400/500 etc datafiles ? Thanks Darren -- Darren Browett P.Eng This message was transmitted Data Administratorusing 100% recycled electrons Information and Communication Technology City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren 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.com -- 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).
datafile sizing question
Okay, I'm about to create some locally managed tablespaces for some large partitioned tables. I plan on making each datafile 2GB, with an extent of 20M. What I've done other times when I use LMTs is add 64K to the file size, for the bitmap header, so that I don't waste most of an extent. But in those cases, the datafile size has been less than 2GB. We will be on Solaris 2.8, I know that the OS can handle files larger than 2GB but it makes me nervous to do this. I hate to waste most of 20M just for the bitmap header. Is my thinking way off? If you've been doing this sort of sizing (it's a data warehouse, yes), what have you done? thanks! Rachel __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: datafile sizing question
Hi Rachel, I know the guy who creates the warehouses here uses 4Gb datafiles (I believe he creates them at 4050Mb). We are on Tru64 Unix He is on his honeymoon at the moment so I cannot ask him his reasoning. Regards Lee -Original Message- Sent: 25 September 2002 17:33 To: Multiple recipients of list ORACLE-L Okay, I'm about to create some locally managed tablespaces for some large partitioned tables. I plan on making each datafile 2GB, with an extent of 20M. What I've done other times when I use LMTs is add 64K to the file size, for the bitmap header, so that I don't waste most of an extent. But in those cases, the datafile size has been less than 2GB. We will be on Solaris 2.8, I know that the OS can handle files larger than 2GB but it makes me nervous to do this. I hate to waste most of 20M just for the bitmap header. Is my thinking way off? If you've been doing this sort of sizing (it's a data warehouse, yes), what have you done? thanks! Rachel __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ** The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe 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: datafile sizing question
I know that wuth 64 bit HP-UX and 64 bit Oracle RDBMS, I have no problems with 1 8GB file. I don't know much about Solaris, but I suppose the following will work: nm $ORACLE_HOME/lib/libclntsh.so|grep lseek64 Results should be nonempty and look something like this: __lseek64 | |undef |code | __lseek64 | 6589540|uext |stub | __lseek64 | 7173576|uext |stub | That meens that lseek64 is used, as an external symbol, from the OS libraries. That, in turn, means that your oracle is using 64 bit routines and is, therefore, 64 bit itself and can handle large files. -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 25, 2002 12:33 PM To: Multiple recipients of list ORACLE-L Subject: datafile sizing question Okay, I'm about to create some locally managed tablespaces for some large partitioned tables. I plan on making each datafile 2GB, with an extent of 20M. What I've done other times when I use LMTs is add 64K to the file size, for the bitmap header, so that I don't waste most of an extent. But in those cases, the datafile size has been less than 2GB. We will be on Solaris 2.8, I know that the OS can handle files larger than 2GB but it makes me nervous to do this. I hate to waste most of 20M just for the bitmap header. Is my thinking way off? If you've been doing this sort of sizing (it's a data warehouse, yes), what have you done? thanks! Rachel __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen 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: datafile sizing question
Are you going with 2048M or the traditional 2000m? A quick dirty way to not waste the space is to use 2001m or 2041m. You 'waste' a little space, but not much. Dan Fink -Original Message- Sent: Wednesday, September 25, 2002 10:33 AM To: Multiple recipients of list ORACLE-L Okay, I'm about to create some locally managed tablespaces for some large partitioned tables. I plan on making each datafile 2GB, with an extent of 20M. What I've done other times when I use LMTs is add 64K to the file size, for the bitmap header, so that I don't waste most of an extent. But in those cases, the datafile size has been less than 2GB. We will be on Solaris 2.8, I know that the OS can handle files larger than 2GB but it makes me nervous to do this. I hate to waste most of 20M just for the bitmap header. Is my thinking way off? If you've been doing this sort of sizing (it's a data warehouse, yes), what have you done? thanks! Rachel __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan 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: datafile sizing question
Title: RE: datafile sizing question We created two datafiles of 16GB+64K all LMT autoallocate ... never gave a problem. A basic testing concluded that fixed size allocation of 128M caused unnecessary delays whereas autoallocate was much faster. I don't know the full details yet, but I'll know soon. Of course this is undergoing lot of testing (the whole application, no problems with datafiles yet), but we will probably settle for about 4GB+64K. This is AIX5L 64 bit running Oracle 9iR2. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *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: datafile sizing question
AHA!I was going to go with 2000M so that's beautiful, 2001M would work perfectly without going over. I don't mind wasting less than a meg. I love the logic everyone here at work has. disk is cheap, don't worry about it. Except every time I ask for more disk, I hear it's too expensive Rachel --- Fink, Dan [EMAIL PROTECTED] wrote: Are you going with 2048M or the traditional 2000m? A quick dirty way to not waste the space is to use 2001m or 2041m. You 'waste' a little space, but not much. Dan Fink -Original Message- Sent: Wednesday, September 25, 2002 10:33 AM To: Multiple recipients of list ORACLE-L Okay, I'm about to create some locally managed tablespaces for some large partitioned tables. I plan on making each datafile 2GB, with an extent of 20M. What I've done other times when I use LMTs is add 64K to the file size, for the bitmap header, so that I don't waste most of an extent. But in those cases, the datafile size has been less than 2GB. We will be on Solaris 2.8, I know that the OS can handle files larger than 2GB but it makes me nervous to do this. I hate to waste most of 20M just for the bitmap header. Is my thinking way off? If you've been doing this sort of sizing (it's a data warehouse, yes), what have you done? thanks! Rachel __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: datafile sizing question
I'm betting that it's not 64-bit Oracle. See, I am the development DBA. As an employee, I get to create the scripts, but I don't get to run them or even get access as oracle to the servers. In other words, all the grunt work, all the responsibility for problems (production is managed by a hosting company) but none of the power to make sure it's right. I love my job. Why? Rachel --- Gogala, Mladen [EMAIL PROTECTED] wrote: I know that wuth 64 bit HP-UX and 64 bit Oracle RDBMS, I have no problems with 1 8GB file. I don't know much about Solaris, but I suppose the following will work: nm $ORACLE_HOME/lib/libclntsh.so|grep lseek64 Results should be nonempty and look something like this: __lseek64 | |undef |code | __lseek64 | 6589540|uext |stub | __lseek64 | 7173576|uext |stub | That meens that lseek64 is used, as an external symbol, from the OS libraries. That, in turn, means that your oracle is using 64 bit routines and is, therefore, 64 bit itself and can handle large files. -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 25, 2002 12:33 PM To: Multiple recipients of list ORACLE-L Subject: datafile sizing question Okay, I'm about to create some locally managed tablespaces for some large partitioned tables. I plan on making each datafile 2GB, with an extent of 20M. What I've done other times when I use LMTs is add 64K to the file size, for the bitmap header, so that I don't waste most of an extent. But in those cases, the datafile size has been less than 2GB. We will be on Solaris 2.8, I know that the OS can handle files larger than 2GB but it makes me nervous to do this. I hate to waste most of 20M just for the bitmap header. Is my thinking way off? If you've been doing this sort of sizing (it's a data warehouse, yes), what have you done? thanks! Rachel __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: datafile sizing question
I believe that the OS filesystem has to be configured for large file support (on HP-UX, it's a kernel parameter) to allow files 2 GB. I usually just add 1 MB to the file size to allow for the header. Personally, I'd play it safe and go with however many 2001 MB files you need to accommodate your objects. --- Rachel Carmichael [EMAIL PROTECTED] wrote: I'm betting that it's not 64-bit Oracle. See, I am the development DBA. As an employee, I get to create the scripts, but I don't get to run them or even get access as oracle to the servers. In other words, all the grunt work, all the responsibility for problems (production is managed by a hosting company) but none of the power to make sure it's right. I love my job. Why? Rachel __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel 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: datafile sizing question
Rachel, that is the same thing as with money itself. Everybody keeps telling me that money isn't everything and that there are many things in life which are more important then money, but when I ask for a check to $10,000 nobody wants to give it to me. Humans are so hard to comprehend for us Vogons. Let me know if you'd like me to recite some poetry. -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 25, 2002 3:18 PM To: Multiple recipients of list ORACLE-L Subject: RE: datafile sizing question AHA!I was going to go with 2000M so that's beautiful, 2001M would work perfectly without going over. I don't mind wasting less than a meg. I love the logic everyone here at work has. disk is cheap, don't worry about it. Except every time I ask for more disk, I hear it's too expensive Rachel --- Fink, Dan [EMAIL PROTECTED] wrote: Are you going with 2048M or the traditional 2000m? A quick dirty way to not waste the space is to use 2001m or 2041m. You 'waste' a little space, but not much. Dan Fink -Original Message- Sent: Wednesday, September 25, 2002 10:33 AM To: Multiple recipients of list ORACLE-L Okay, I'm about to create some locally managed tablespaces for some large partitioned tables. I plan on making each datafile 2GB, with an extent of 20M. What I've done other times when I use LMTs is add 64K to the file size, for the bitmap header, so that I don't waste most of an extent. But in those cases, the datafile size has been less than 2GB. We will be on Solaris 2.8, I know that the OS can handle files larger than 2GB but it makes me nervous to do this. I hate to waste most of 20M just for the bitmap header. Is my thinking way off? If you've been doing this sort of sizing (it's a data warehouse, yes), what have you done? thanks! Rachel __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen 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: datafile sizing question
Rachel, if you can execute sqlplus, you can do the nm thing. You don't need to log in as oracle. -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 25, 2002 3:24 PM To: Multiple recipients of list ORACLE-L Subject: RE: datafile sizing question I'm betting that it's not 64-bit Oracle. See, I am the development DBA. As an employee, I get to create the scripts, but I don't get to run them or even get access as oracle to the servers. In other words, all the grunt work, all the responsibility for problems (production is managed by a hosting company) but none of the power to make sure it's right. I love my job. Why? Rachel --- Gogala, Mladen [EMAIL PROTECTED] wrote: I know that wuth 64 bit HP-UX and 64 bit Oracle RDBMS, I have no problems with 1 8GB file. I don't know much about Solaris, but I suppose the following will work: nm $ORACLE_HOME/lib/libclntsh.so|grep lseek64 Results should be nonempty and look something like this: __lseek64 | |undef |code | __lseek64 | 6589540|uext |stub | __lseek64 | 7173576|uext |stub | That meens that lseek64 is used, as an external symbol, from the OS libraries. That, in turn, means that your oracle is using 64 bit routines and is, therefore, 64 bit itself and can handle large files. -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 25, 2002 12:33 PM To: Multiple recipients of list ORACLE-L Subject: datafile sizing question Okay, I'm about to create some locally managed tablespaces for some large partitioned tables. I plan on making each datafile 2GB, with an extent of 20M. What I've done other times when I use LMTs is add 64K to the file size, for the bitmap header, so that I don't waste most of an extent. But in those cases, the datafile size has been less than 2GB. We will be on Solaris 2.8, I know that the OS can handle files larger than 2GB but it makes me nervous to do this. I hate to waste most of 20M just for the bitmap header. Is my thinking way off? If you've been doing this sort of sizing (it's a data warehouse, yes), what have you done? thanks! Rachel __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: datafile sizing question
I think it's automatic on Solaris 2.8 but I don't know for sure so I'd rather not rely on it. I'm going with 2001M and creating about a year's worth of tablespaces/partitions. 101 datafiles and tablespaces. Just to START. my aching typing fingers! --- Paul Baumgartel [EMAIL PROTECTED] wrote: I believe that the OS filesystem has to be configured for large file support (on HP-UX, it's a kernel parameter) to allow files 2 GB. I usually just add 1 MB to the file size to allow for the header. Personally, I'd play it safe and go with however many 2001 MB files you need to accommodate your objects. --- Rachel Carmichael [EMAIL PROTECTED] wrote: I'm betting that it's not 64-bit Oracle. See, I am the development DBA. As an employee, I get to create the scripts, but I don't get to run them or even get access as oracle to the servers. In other words, all the grunt work, all the responsibility for problems (production is managed by a hosting company) but none of the power to make sure it's right. I love my job. Why? Rachel __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Another datafile sizing question
I am in the process of upgrading our databases from 8.0.5 to 8.1.7, possibly 9i depending on application certifications. I currently have a tablespace that is made up of 4 - 200mb datafiles, my first thought would be to create a 800mb datafile and move all the data into it, The growth of this tablespace is maybe 100 to 150Mb a year, and from what I understand all datafiles should be the same size. So at that point when I need another datafile, I will have to create another 800Mb datafile. Would that be a good practise, or should I stay with multiple 200/300/400/500 etc datafiles ? Thanks Darren -- Darren Browett P.EngThis message was transmitted Data Administrator using 100% recycled electrons Information and Communication Technology City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren 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: Another datafile sizing question
Is there a reason you can't just resize the existing file? -Original Message- Sent: Wednesday, September 25, 2002 7:43 PM To: Multiple recipients of list ORACLE-L I am in the process of upgrading our databases from 8.0.5 to 8.1.7, possibly 9i depending on application certifications. I currently have a tablespace that is made up of 4 - 200mb datafiles, my first thought would be to create a 800mb datafile and move all the data into it, The growth of this tablespace is maybe 100 to 150Mb a year, and from what I understand all datafiles should be the same size. So at that point when I need another datafile, I will have to create another 800Mb datafile. Would that be a good practise, or should I stay with multiple 200/300/400/500 etc datafiles ? Thanks Darren -- Darren Browett P.EngThis message was transmitted Data Administrator using 100% recycled electrons Information and Communication Technology City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren 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.com -- Author: Johnston, Tim 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: Another datafile sizing question
Darren - My advice would be to read up on Locally Managed Tablespaces (LMT) and uniform extents. This is a new feature that will ease your management work. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, September 25, 2002 6:43 PM To: Multiple recipients of list ORACLE-L I am in the process of upgrading our databases from 8.0.5 to 8.1.7, possibly 9i depending on application certifications. I currently have a tablespace that is made up of 4 - 200mb datafiles, my first thought would be to create a 800mb datafile and move all the data into it, The growth of this tablespace is maybe 100 to 150Mb a year, and from what I understand all datafiles should be the same size. So at that point when I need another datafile, I will have to create another 800Mb datafile. Would that be a good practise, or should I stay with multiple 200/300/400/500 etc datafiles ? Thanks Darren -- Darren Browett P.EngThis message was transmitted Data Administrator using 100% recycled electrons Information and Communication Technology City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren 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.com -- 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: Another datafile sizing question
It's late at night maybe that's why I do not understand your answer but I do not see the link between LMT and the number/size of datafiles. One reason of multiple datafiles id to spread IO but since nowadays a majority of sites goes on huge disk box using raid 5 (that's what we have, the unix guys are the IT master here) multiple files is less meaningful. What I liked is a file politics where you restrained the number of file size. Here we have from 15M up to 8.5G file size with all the possibility in between. I'm trying to standardize all that. Another factor to consider is backup and recovery. Restoring a 10G file will take more time than a 2G file. In your case, if file placement is not possible than go for a 800M file and use a second one for the future growth. -- DENNIS WILLIAMS [EMAIL PROTECTED] a écrit : Darren - My advice would be to read up on Locally Managed Tablespaces (LMT) and uniform extents. This is a new feature that will ease your management work. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, September 25, 2002 6:43 PM To: Multiple recipients of list ORACLE-L I am in the process of upgrading our databases from 8.0.5 to 8.1.7, possibly 9i depending on application certifications. I currently have a tablespace that is made up of 4 - 200mb datafiles, my first thought would be to create a 800mb datafile and move all the data into it, The growth of this tablespace is maybe 100 to 150Mb a year, and from what I understand all datafiles should be the same size. So at that point when I need another datafile, I will have to create another 800Mb datafile. Would that be a good practise, or should I stay with multiple 200/300/400/500 etc datafiles ? Thanks Darren -- Darren Browett P.Eng This message was transmitted Data Administratorusing 100% recycled electrons Information and Communication Technology City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren 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.com -- 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). = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= 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: datafile sizing question
Mladen, I'd love to hear your poetry -- but only if you are reciting it at the December NYOUG meeting as the keynote speaker. I still need a keynote speaker for that meeting... Rachel --- Gogala, Mladen [EMAIL PROTECTED] wrote: Rachel, that is the same thing as with money itself. Everybody keeps telling me that money isn't everything and that there are many things in life which are more important then money, but when I ask for a check to $10,000 nobody wants to give it to me. Humans are so hard to comprehend for us Vogons. Let me know if you'd like me to recite some poetry. -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 25, 2002 3:18 PM To: Multiple recipients of list ORACLE-L Subject: RE: datafile sizing question AHA!I was going to go with 2000M so that's beautiful, 2001M would work perfectly without going over. I don't mind wasting less than a meg. I love the logic everyone here at work has. disk is cheap, don't worry about it. Except every time I ask for more disk, I hear it's too expensive Rachel --- Fink, Dan [EMAIL PROTECTED] wrote: Are you going with 2048M or the traditional 2000m? A quick dirty way to not waste the space is to use 2001m or 2041m. You 'waste' a little space, but not much. Dan Fink -Original Message- Sent: Wednesday, September 25, 2002 10:33 AM To: Multiple recipients of list ORACLE-L Okay, I'm about to create some locally managed tablespaces for some large partitioned tables. I plan on making each datafile 2GB, with an extent of 20M. What I've done other times when I use LMTs is add 64K to the file size, for the bitmap header, so that I don't waste most of an extent. But in those cases, the datafile size has been less than 2GB. We will be on Solaris 2.8, I know that the OS can handle files larger than 2GB but it makes me nervous to do this. I hate to waste most of 20M just for the bitmap header. Is my thinking way off? If you've been doing this sort of sizing (it's a data warehouse, yes), what have you done? thanks! Rachel __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen 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
RE: datafile sizing question
I can't with the account they have given me. At least, not yet :( this is so well planned I will be amazed if it goes live on time. and I (and my boss) are living by two mottos failure to plan on your part does not constitute an emergency on mine and if it fails, it will NOT be because of anything the DB group did or failed to do --- Gogala, Mladen [EMAIL PROTECTED] wrote: Rachel, if you can execute sqlplus, you can do the nm thing. You don't need to log in as oracle. -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 25, 2002 3:24 PM To: Multiple recipients of list ORACLE-L Subject: RE: datafile sizing question I'm betting that it's not 64-bit Oracle. See, I am the development DBA. As an employee, I get to create the scripts, but I don't get to run them or even get access as oracle to the servers. In other words, all the grunt work, all the responsibility for problems (production is managed by a hosting company) but none of the power to make sure it's right. I love my job. Why? Rachel --- Gogala, Mladen [EMAIL PROTECTED] wrote: I know that wuth 64 bit HP-UX and 64 bit Oracle RDBMS, I have no problems with 1 8GB file. I don't know much about Solaris, but I suppose the following will work: nm $ORACLE_HOME/lib/libclntsh.so|grep lseek64 Results should be nonempty and look something like this: __lseek64 | |undef |code | __lseek64 | 6589540|uext |stub | __lseek64 | 7173576|uext |stub | That meens that lseek64 is used, as an external symbol, from the OS libraries. That, in turn, means that your oracle is using 64 bit routines and is, therefore, 64 bit itself and can handle large files. -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 25, 2002 12:33 PM To: Multiple recipients of list ORACLE-L Subject: datafile sizing question Okay, I'm about to create some locally managed tablespaces for some large partitioned tables. I plan on making each datafile 2GB, with an extent of 20M. What I've done other times when I use LMTs is add 64K to the file size, for the bitmap header, so that I don't waste most of an extent. But in those cases, the datafile size has been less than 2GB. We will be on Solaris 2.8, I know that the OS can handle files larger than 2GB but it makes me nervous to do this. I hate to waste most of 20M just for the bitmap header. Is my thinking way off? If you've been doing this sort of sizing (it's a data warehouse, yes), what have you done? thanks! Rachel __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? New DSL Internet Access from SBC Yahoo! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL
