Well, I have a slightly different way of approaching file sizing. Here we have Hatachi storage array's on a FIDI setup. We stripe several drives, RAID, and get quite good performance.
I do NOT limit datafiles to any particular size (in production). Why? Because I want to eliminate, as much as possible, any risk of a load, or user action failing because of insufficient tablespace space. While we monitor for potential space problems, sometimes things happen in bunches and very fast. We have, rather than standardize on a datafile size (early bugs with datafiles over 2gb and autoextend aside), standardized on file system sizes, 25gb in our case coupled with use of autoextend. We generally create the initial datafiles at between 5 and 10GB max, with autoextend on. We generally leave a minimum of 20% space in the file system for growth (or more). How did we come by this 25gb file system size number? I wrote some C programs that simulated random and sequential reads, blasted the heck out of the system, and 25gb FS sizes came out quite well. Smaller file system sizes can slow down bringing up the system because this usually equates to more file systems that all have to fsck'd during a boot. Larger file system sizes have recovery time impact. All in all, 25gb was a good size for us. Thus, between autoextend, monitoring and (hopefully) proactive DBA's, I eliminate outages. Restricting datafile sizes requires much more manual intervention on the part of the DBA, calls at 2am to restart loads and resize tablesaces and makes outages more likely. We also deal with the issue of moving between machines with the requirement that file systems are all 25GB. Now, if stuff is already in those file systems, then we might well have to either move stuff around or create new file systems.... but hey, thats my job! Just how I do things, YMMV... Robert G. Freeman - Oracle8i OCP Oracle DBA Technical Lead CSX Midtier Database Administration The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -----Original Message----- Sent: Tuesday, March 05, 2002 12:33 PM To: Multiple recipients of list ORACLE-L Having 500mb, and then 4 50mb data files is over killed. Have multiple files per tablespace is generally good pratice and should make them same size. We have limited the database files to 2G for just one reason. That reason is that if you have to recover a database or move to another box, it is easier to manage them in terms of the disk size of file system on that target box--just in case the target box file system size could not fit the large file. -----Original Message----- Sent: Tuesday, March 05, 2002 5:59 AM To: Multiple recipients of list ORACLE-L Ah, but we use partitioning. However, the design you described is slightly flawed me thinks. I had to do something similar at the last job and what we did is have a separate tablespace for each month, which in turn produces a separate data file of course. Not that there was anything wrong in what you said per say its just that it really does not simulate partitioning if they are all in the same tablespace. It would be purely a load balancing thing. That being said, I am not really anticipating a load balance problem on this server. Not saying its not possible, I am just not anticipating it. But with 9i it would be fairly easy to reorganize after the fact if I do experience it. -----Original Message----- Sent: Tuesday, March 05, 2002 4:58 AM To: Multiple recipients of list ORACLE-L Not using the RBS tablespace as the tablespace of discussion because it has special requirements and can create a lot of discussion. I can fore see a reason for using multiple datafiles in a tablespace. Lets say that you have a large table than contains information based on dates. you load the table with data each year and at the years end you resize the datafile to eliminate the unused space. Then you create another datafile for the tablespace to use for the next years data and load the data for the new year. The new data is still part of the same table and tablespace but in a separate datafile. It could be a method of creating partitions when you can't afford the option or it is not available to you (pre 8). Then you would eliminate some of the bottlenecks with the IO to the drives if the datafiles are on different drives. The users would see an improvement in response time if the were querying different date based data. Also the multiple datafile concept could be used during the backup/restore process. The user could have limits to the max tape size available but still want to backup the database. I know that it could take a lot of tapes to backup a 70GIG database when your tape machine has a 2GIG limit on the tape capacity. They do still exist. ROR mª¿ªm >>> [EMAIL PROTECTED] 03/04/02 09:28PM >>> no reason. I can see creating multiple files under those conditions only because you want to keep files to a specific size. Now, I did once find that the rollback datafiles were a bottleneck on a system I had. So we built TWO rollback tablespaces, with datafiles on different mount points etc and the rollback segments divided between the two tablespaces. cleared up that bottleneck like a dream other than that though.. why? --- Kimberly Smith <[EMAIL PROTECTED]> wrote: > OK, I know we had the debate already but lets have another go at it. > > Say you got a tablespace, lets call it RBS and its for rollbacks. > Now, for what reason would you create a 500M file and 4 50M files > for this puppy as opposed to just one file. I just cannot see the > reasoning > for this at all. None. Natta. Zilch. > > So educate me please if someone out there knows a legit reason they > would do this. > > Lets assume for the sake of argument that disk size and mount point > size is not a limitation. Space available to me on any one mount > point > is unlimited. > > ___________________________ > Kimberly Smith > Portland, OR > [EMAIL PROTECTED] > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Kimberly Smith > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing > Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). __________________________________________________ Do You Yahoo!? Yahoo! Sports - sign up for Fantasy Baseball http://sports.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 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the H ELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kimberly Smith INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Wong, Bing INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
