| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: # of datafiles per tablespace
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
>>> wisernet100_at_yahoo.com 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?
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: wisernet100_at_yahoo.com 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: ListGuru_at_fatcity.com (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: RROGERS_at_galottery.org 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: ListGuru_at_fatcity.com (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: ksmith2_at_myfirstlink.net 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: ListGuru_at_fatcity.com (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: bing.wong_at_IngramMicro.com 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: ListGuru_at_fatcity.com (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: Robert_Freeman_at_csx.com 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: ListGuru_at_fatcity.com (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).Received on Tue Mar 05 2002 - 12:43:26 CST
 |
 |