RE: Multiple Datafiles and performance?
I don't know of any advantage to uniform datafile sizes. Back in the day
some DBAs preferred uniform datafile sizes so that they could easily move
datafiles from disk to disk to balance physical IO. With modern disk
configurations this strikes me as more work than it's worth. However, if
you really, really need that last little bit of "umph" then it is a
consideration.
4x1 or 2x2 - 4x1 has more "things" to keep track of. Otherwise, to the
best of my knowledge it's not an issue.
If a datafile autoextends to the 2G mark, under certain service pack
releases of Win2k the datafile becomes corrupted. As I remember the
discussion, MS had fixed one of their disk IO dlls to work over the 2G mark
but it the datafile size was under 2G the disk IO was handled by a program
that could not handle growing to 2G. So if the datafile was over 2G you
wouldn't run into the problem. MS does have upgrades to fix the problem.
Has your SA applied them?
I've been reading various white papers about RAID 10 vs. RAID 01
(mirror-then-stripe vs stripe-then-mirror). I've heard good arguments on
both sides and I hope that the experts can come to a consensus. Only RAID
manufacturers seem to be pushing RAID 5.
Right now Oracle's recommendation is SAME (Stripe All, Mirror Everything).
It is not the absolutely optimal situation but it will provide adequate
performance in most situations.
HTH
"Dave Phillips"
@gasper-corp.com cc:
> Subject: RE: Multiple Datafiles and
performance?
Sent by:
ml-errors
08/07/2003 11:24
AM
Please respond
to ORACLE-L
Gee, that question sounded a whole lot better when I wrote it yesterday
than it did this morning when I saw it. :) Maybe I should be a little more
vague.:)
The problem is there are a couple of things I am trying to accomplish. We
have clients that use our application that have specific performance issues
which I am working to improve. The other issue is to provide recommendation
to development/tech staff on initial setup of
database/tablespaces/datafiles etc.., along with hardware recommendations
for our application.
So, that being said, I'll try and ask better questions.
The environment is W2K, Oracle 8.1.7.2 or higher
All tablespaces are LMT
Most disk config's are 1 (or 2) Raid 1 along with a Raid 5 for basic
systems.
Most operate application 24/7
Questions:
1) Is there any advantage to uniform datafile sizes?
2) Is there any advantage/disadvantage for say 4 1G datafiles vs 2 2G.
(Other than time to recover from datafile loss)
It is probably safe to assume that the datafiles exist on a RAID 5. (for
now)
3) Why the recommendation to take a Win2k datafile to just over 2G?
For future apps I am pushing for optimal recommendations that go for more
raid 1 sets or raid 10 over the Raid 5. This should allow for more
flexibility for spreading out the i/o.
Thanks for your patience and all the help.
David Phillips
Support DBA
BAARF Member #30
-Original Message-
Sent: Thursday, August 07, 2003 9:59 AM
To: Multiple recipients of list ORACLE-L
Win2K. If you decide to increase the filesize, do it to more than 2G
(does
RE: Multiple Datafiles and performance?
> >a kilt, standing over a grate not quite Marilyn Monroe -Original Message- Daniel Fink Sent: Thursday, August 07, 2003 11:39 AM To: Multiple recipients of list ORACLE-L Tim Gorman wrote: > > I don't mean to be argumentative, but every time I see assertions like > these, I suspect someone has been reading some rather discredited books... Okay, now I have an image of Tim, in a kilt, standing over a grate, with his face painted with Blue vertical stripes and a big sword strapped to his back! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Henry Poras 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: Multiple Datafiles and performance?
Hi! > The benefits of spreading the data over as many physical access paths ( ~ > disks ) using multiple datafiles notwithstanding, there is always the case > of too much. Keep in mind that at checkpoint time the DBWR need to visit > the header of every ( non read-only ) datafile. That's unlikely to be an The number of files had some impact in older Oracle versions (7.x). Starting from 8.0 I believe, this issue is somewhat relieved, as you probably know. Not all file headers are updated together and the update doesn't have to go to disk immediately (this goes for checkpoints caused by log switches). Also, in older versions db_files parameter affected DBWR batch size and some buffer cache structures as well, IIRC. The biggest number of files I've had in a production database is about 1150, 960MB each. On WindowsNT4... =8´o Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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: Multiple Datafiles and performance?
Raj, The point is that separating these data structures physically does not enhance performance per se. Notice that I'm not arguing against striping and the distribution of I/O... Rather, more consideration should be given the I/O statistics (only the counts, not the timings!) in V$FILESTAT/V$TEMPSTAT rather than DBA_SEGMENTS.OBJECT_TYPE when considering how to distribute the load... Thanks! -Tim on 8/7/03 11:34 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote: > > Tim, > > For arguments sake, the I/O steps that you mention is for a single user. > Assume thousands of users, in which case, everyone would be hitting the > same disk volume. Whereas, if they were spread, the I/O would be spread > across 2 different volumes. > > Having said that, I dont recommend spreading them on different disk > volumes. The goal should be spreading I/O evenly across all the available > disk volumes. The S.A.M.E principle. Just for the heck of spreading the > datafiles across disk volumes, I would not want the index datafile to be > moved from a disk with 20% utilization to one with 90%. > > Raj > > > > > Tim Gorman > <[EMAIL PROTECTED] To: Multiple recipients of list > ORACLE-L <[EMAIL PROTECTED]> > .com> cc: > Sent by: Subject: Re: Multiple Datafiles and > performance? > [EMAIL PROTECTED] > ity.com > > > 08/07/2003 > 11:19 AM > Please respond > to ORACLE-L > > > > > > > I don't mean to be argumentative, but every time I see assertions like > these, I suspect someone has been reading some rather discredited books... > > So, my apologies in advance, but comments are inline below... > >> >> In my experience, spreading datafiles across volumes (specially if you > are >> careful not to locate the a table's datafiles and its indexes datafiles > in the >> same drive) greatly increases performance. > > The assertion that performance is enhanced by distributing datafiles > containing tables and datafiles containing indexes to different volumes is > a > myth. > > Think about it. > > Indexed access is a purely sequential activity from an I/O standpoint, > putting aside the reality that a buffer cache exists. First, we access the > root block of the index and read its contents in order to know where to > perform the next I/O (i.e. a branch block). Then we read that branch block > and read its contents in order to know where to perform the next I/O (i.e. > a > leaf block). Then we read the leaf block and read its contents in order to > know where to perform the next I/O (i.e. a block in a table). And so on... > > Since we are performing sequential single-block I/O (hence the name of the > wait event "db file sequential read"), how can separating datafiles > containing tables from datafiles containing indexes matter to performance? > >> >> As for the file size, I can not say because I have not tested it, but I > think >> it should have no real impact compared to splitting it. Reorganizing the >> database regularly is a better way to optimize performance. > > And in what ways does "reorganizing the database regularly" improve > performance? > > To break the question down into more manageable pieces: > > * In what way does rebuilding a table improve performance? > * In what way does rebuilding an index improve performance? > > There are specific answers to these questions. For example, there are > situations in which both tables and indexes can become "sparsely > populated". > Tables become sparsely populated due to large-scale deletions. Indexes > become sparsely populated due to monotonically-ascending data values > inserted transactionally. > > In these cases, how can you detect this condition? The package DBMS_SPACE > has procedures that help for tables and the ANALYZE INDEX ... VALIDATE > STRUCTURE command helps for indexes. > > Does rebuilding a table or index that is not "sparsely populated" aid > performance in any way? Quite frankly, no... > > ..well, there is one condition involving the "clustering factor" of an > index where a rebuild of the table can help, but you'll end up hosing the > "clustering factor" of other indexes. It is a case of favoring one index > over another, and that is a decision that requires intimate knowledge of > the >
RE: Multiple Datafiles and performance?
Thanks for the knowledge dump everyone! Based on your responses, I'll make the recommendation that our client go with 3 2.5G datafiles. They currently are 1 2.5G and 2 1G datafiles for the tablespace in question. Most of our clients have less than 30 datafiles, and I doubt will find any over 50. (slightly less than 1150 ...whew!) Index files are separated from data. The datafiles in question were data, and not index files, but they are on RAID5. We got them add 2 more RAID1's, so they are up to 4 RAID1's and 1 RAID5. We have the Indexes on a RAID1, the OS/Oracle on a RAID1, the remaining were on the RAID5, including RBS and REDO. We are moving the Redo Groups to the other RAID1's (one group on each). We could move the RBS to the OS/Oracle drive should contention is still an issue. I've been walking around with "ORACLE 101 - Performance Tuning" duct taped to the back of my head for about 4 weeks now trying to absorb as much as I can. It's got me looking at every change we make and it performance consequences. The number/size of datafiles was one thing I couldn't track down much info about in relation to performance. We've changed the app to increase use of bind variables, so we're making progress. (It helps that we have an Oracle DBA on each development team). We're making a lot of progress, an the info from this list and the recommended reading has played a big part in helping me get, not just changes made, but the right changes made. Now, if I could just learn to make more focused and cohesive sentences and yeh,uh what I just said. Once again, thanks for the help David Phillips Support DBA -Original Message- Sent: Thursday, August 07, 2003 10:45 AM To: Multiple recipients of list ORACLE-L Dave, There is little about the size of datafiles to affect the performance of SQL statements, but there is much to affect the performance of backup and restore and administration. Uniform-sized datafiles simplify the administration of space. The speed of a backup or restore is a function of the largest datafile, so although files can now be sized in petabytes, it is not a good idea to do so. Personally, I stick to a max size of 2-8 Gbytes, depending on overall database size. Far faster to backup/restore lots of smaller files than to have one 500Gb monster holding things up. Also, if your storage subsystem isn't already implementing RAID-0 striping, then hand-striping multiple datafiles across volumes could help performance. Again, in that situation, many smaller uniform-sized files make the job easier than a few larger odd-sized files. Hope this helps... -Tim on 8/6/03 1:14 PM, Dave Phillips at [EMAIL PROTECTED] wrote: > Oracle 8.1.7.4 > Win2k > > What is the consensus on datafile sizing and the impact/overhead > multiple datafiles have on performance? > > For example, if I have one 2.5g datafile, and three 1g datafiles, and I > need more space, would it be better to increase the size of the 1g to > 2g or add another 1g datafile?. > Is it better to keep them all uniform in size? > > I would think having multiple datafiles that could be spread across > drive volumes would be beneficial, am I wrong? (Wouldn't be the first > time :) > > TIA > > David Phillips > Support DBA > Gasper Corp. > BAARF member #30 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dave Phillips 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: Multiple Datafiles and performance?
Hey Tim, I just wanted to point out an excellent thread (the responses, not my question) on this list from exactly one year ago titled "Checking the rebuildability of an index", which can be searched at fatcity.com using "rebuildability" and the year 2002 (be kind to search engines). Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA > -Original Message- > From: Tim Gorman [mailto:[EMAIL PROTECTED] > Sent: Thursday, August 07, 2003 10:19 AM > To: Multiple recipients of list ORACLE-L > Subject: Re: Multiple Datafiles and performance? > > > I don't mean to be argumentative, but every time I see assertions like > these, I suspect someone has been reading some rather > discredited books... > > So, my apologies in advance, but comments are inline below... > > > > > In my experience, spreading datafiles across volumes > (specially if you are > > careful not to locate the a table's datafiles and its > indexes datafiles in the > > same drive) greatly increases performance. > > The assertion that performance is enhanced by distributing datafiles > containing tables and datafiles containing indexes to > different volumes is a > myth. > > Think about it. > > Indexed access is a purely sequential activity from an I/O standpoint, > putting aside the reality that a buffer cache exists. First, > we access the > root block of the index and read its contents in order to > know where to > perform the next I/O (i.e. a branch block). Then we read > that branch block > and read its contents in order to know where to perform the > next I/O (i.e. a > leaf block). Then we read the leaf block and read its > contents in order to > know where to perform the next I/O (i.e. a block in a table). > And so on... > > Since we are performing sequential single-block I/O (hence > the name of the > wait event "db file sequential read"), how can separating datafiles > containing tables from datafiles containing indexes matter to > performance? > > > > > As for the file size, I can not say because I have not > tested it, but I think > > it should have no real impact compared to splitting it. > Reorganizing the > > database regularly is a better way to optimize performance. > > And in what ways does "reorganizing the database regularly" improve > performance? > > To break the question down into more manageable pieces: > > * In what way does rebuilding a table improve performance? > * In what way does rebuilding an index improve performance? > > There are specific answers to these questions. For example, there are > situations in which both tables and indexes can become > "sparsely populated". > Tables become sparsely populated due to large-scale > deletions. Indexes > become sparsely populated due to monotonically-ascending data values > inserted transactionally. > > In these cases, how can you detect this condition? The > package DBMS_SPACE > has procedures that help for tables and the ANALYZE INDEX ... VALIDATE > STRUCTURE command helps for indexes. > > Does rebuilding a table or index that is not "sparsely populated" aid > performance in any way? Quite frankly, no... > > ...well, there is one condition involving the "clustering > factor" of an > index where a rebuild of the table can help, but you'll end > up hosing the > "clustering factor" of other indexes. It is a case of > favoring one index > over another, and that is a decision that requires intimate > knowledge of the > application's usage of the table and its indexes... > > So, "reorganizing the database" on a regular basis is purely > a waste of > time. Regularly monitoring the database for "sparsely > populated" tables and > indexes, and then determining if the condition is affecting > performance of > any important processes before rebuilding, will indeed help > performance. > > Sorry for the combative tone, but I hope this helps... > > -Tim > > > > > > > > -Mensaje original- > > De: Dave Phillips [mailto:[EMAIL PROTECTED] > > Enviado el: miércoles, 06 de agosto de 2003 22:14 > > Para: Multiple recipients of list ORACLE-L > > Asunto: Multiple Datafiles and performance? > > > > > > Oracle 8.1.7.4 > > Win2k > > > > What is the consensus on datafile sizing and the impact/overhead > > multiple datafiles have on performance? > > > > For example, if I have one 2.5g datafile,
Re: Multiple Datafiles and performance?
Tim, For arguments sake, the I/O steps that you mention is for a single user. Assume thousands of users, in which case, everyone would be hitting the same disk volume. Whereas, if they were spread, the I/O would be spread across 2 different volumes. Having said that, I dont recommend spreading them on different disk volumes. The goal should be spreading I/O evenly across all the available disk volumes. The S.A.M.E principle. Just for the heck of spreading the datafiles across disk volumes, I would not want the index datafile to be moved from a disk with 20% utilization to one with 90%. Raj Tim Gorman <[EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> .com>cc: Sent by: Subject: Re: Multiple Datafiles and performance? [EMAIL PROTECTED] ity.com 08/07/2003 11:19 AM Please respond to ORACLE-L I don't mean to be argumentative, but every time I see assertions like these, I suspect someone has been reading some rather discredited books... So, my apologies in advance, but comments are inline below... > > In my experience, spreading datafiles across volumes (specially if you are > careful not to locate the a table's datafiles and its indexes datafiles in the > same drive) greatly increases performance. The assertion that performance is enhanced by distributing datafiles containing tables and datafiles containing indexes to different volumes is a myth. Think about it. Indexed access is a purely sequential activity from an I/O standpoint, putting aside the reality that a buffer cache exists. First, we access the root block of the index and read its contents in order to know where to perform the next I/O (i.e. a branch block). Then we read that branch block and read its contents in order to know where to perform the next I/O (i.e. a leaf block). Then we read the leaf block and read its contents in order to know where to perform the next I/O (i.e. a block in a table). And so on... Since we are performing sequential single-block I/O (hence the name of the wait event "db file sequential read"), how can separating datafiles containing tables from datafiles containing indexes matter to performance? > > As for the file size, I can not say because I have not tested it, but I think > it should have no real impact compared to splitting it. Reorganizing the > database regularly is a better way to optimize performance. And in what ways does "reorganizing the database regularly" improve performance? To break the question down into more manageable pieces: * In what way does rebuilding a table improve performance? * In what way does rebuilding an index improve performance? There are specific answers to these questions. For example, there are situations in which both tables and indexes can become "sparsely populated". Tables become sparsely populated due to large-scale deletions. Indexes become sparsely populated due to monotonically-ascending data values inserted transactionally. In these cases, how can you detect this condition? The package DBMS_SPACE has procedures that help for tables and the ANALYZE INDEX ... VALIDATE STRUCTURE command helps for indexes. Does rebuilding a table or index that is not "sparsely populated" aid performance in any way? Quite frankly, no... ...well, there is one condition involving the "clustering factor" of an index where a rebuild of the
Re: Multiple Datafiles and performance?
Tim Gorman wrote: > > I don't mean to be argumentative, but every time I see assertions like > these, I suspect someone has been reading some rather discredited books... Okay, now I have an image of Tim, in a kilt, standing over a grate, with his face painted with Blue vertical stripes and a big sword strapped to his back!begin:vcard n:Fink;Daniel x-mozilla-html:FALSE org:Sun Microsystems, Inc. adr:;; version:2.1 title:Lead, Database Services x-mozilla-cpt:;9168 fn:Daniel W. Fink end:vcard
RE: Multiple Datafiles and performance?
Sometimes our comments are not too deep and of course I meant reorganizing
improves performance under the circumstances you mention, i.e. massive deletions on
tables or the insertion on indexes you mention ("monotonically-ascending data values
inserted transactionally" is a very good expression for my poor English!).
As for the datafiles and indexfiles being distributed over different physical
disks, well I can not confirm you are wrong, in our site id DID boost the performance,
and specially if you locate in separate physical drives your redologs and datafiles
(both indexes or data). Of course it depends on the server's work load and how many
people are simultaneously accesing data and indexes. I can understand what you say but
if many different users are accesing the same table via indexes and both the table's
data and the indexes are on the same physical drive I think performance should be
worst than if they were separated, right or wrong? ...
Regards,
Fermin.
-Mensaje original-
De: Tim Gorman [mailto:[EMAIL PROTECTED]
Enviado el: jueves, 07 de agosto de 2003 17:19
Para: Multiple recipients of list ORACLE-L
Asunto: Re: Multiple Datafiles and performance?
I don't mean to be argumentative, but every time I see assertions like
these, I suspect someone has been reading some rather discredited books...
So, my apologies in advance, but comments are inline below...
>
> In my experience, spreading datafiles across volumes (specially if you are
> careful not to locate the a table's datafiles and its indexes datafiles in the
> same drive) greatly increases performance.
The assertion that performance is enhanced by distributing datafiles
containing tables and datafiles containing indexes to different volumes is a
myth.
Think about it.
Indexed access is a purely sequential activity from an I/O standpoint,
putting aside the reality that a buffer cache exists. First, we access the
root block of the index and read its contents in order to know where to
perform the next I/O (i.e. a branch block). Then we read that branch block
and read its contents in order to know where to perform the next I/O (i.e. a
leaf block). Then we read the leaf block and read its contents in order to
know where to perform the next I/O (i.e. a block in a table). And so on...
Since we are performing sequential single-block I/O (hence the name of the
wait event "db file sequential read"), how can separating datafiles
containing tables from datafiles containing indexes matter to performance?
>
> As for the file size, I can not say because I have not tested it, but I think
> it should have no real impact compared to splitting it. Reorganizing the
> database regularly is a better way to optimize performance.
And in what ways does "reorganizing the database regularly" improve
performance?
To break the question down into more manageable pieces:
* In what way does rebuilding a table improve performance?
* In what way does rebuilding an index improve performance?
There are specific answers to these questions. For example, there are
situations in which both tables and indexes can become "sparsely populated".
Tables become sparsely populated due to large-scale deletions. Indexes
become sparsely populated due to monotonically-ascending data values
inserted transactionally.
In these cases, how can you detect this condition? The package DBMS_SPACE
has procedures that help for tables and the ANALYZE INDEX ... VALIDATE
STRUCTURE command helps for indexes.
Does rebuilding a table or index that is not "sparsely populated" aid
performance in any way? Quite frankly, no...
...well, there is one condition involving the "clustering factor" of an
index where a rebuild of the table can help, but you'll end up hosing the
"clustering factor" of other indexes. It is a case of favoring one index
over another, and that is a decision that requires intimate knowledge of the
application's usage of the table and its indexes...
So, "reorganizing the database" on a regular basis is purely a waste of
time. Regularly monitoring the database for "sparsely populated" tables and
indexes, and then determining if the condition is affecting performance of
any important processes before rebuilding, will indeed help performance.
Sorry for the combative tone, but I hope this helps...
-Tim
>
>
> -Mensaje original-
> De: Dave Phillips [mailto:[EMAIL PROTECTED]
> Enviado el: miércoles, 06 de agosto de 2003 22:14
> Para: Multiple recipients of list ORACLE-L
> Asunto: Multiple Datafiles and performance?
>
>
> Oracle 8.1.7.4
> Win2k
>
> What is the consensus on datafile sizing and the impact/overhead
> multiple datafiles have on performance?
>
> For example, if I have one 2.5g datafile, and three 1g datafiles
RE: Multiple Datafiles and performance?
In my experience, spreading datafiles across volumes (specially if you are careful not to locate the a table's datafiles and its indexes datafiles in the same drive) greatly increases performance. As for the file size, I can not say because I have not tested it, but I think it should have no real impact compared to splitting it. Reorganizing the database regularly is a better way to optimize performance. .. Fermín Bernaus Berraondo Dpto. de Informática SAMMIC, S.A. [EMAIL PROTECTED] http://www.sammic.com Telf. +34 - 943 157 331 Fax +34 - 943 151 276 .. -Mensaje original- De: Dave Phillips [mailto:[EMAIL PROTECTED] Enviado el: miércoles, 06 de agosto de 2003 22:14 Para: Multiple recipients of list ORACLE-L Asunto: Multiple Datafiles and performance? Oracle 8.1.7.4 Win2k What is the consensus on datafile sizing and the impact/overhead multiple datafiles have on performance? For example, if I have one 2.5g datafile, and three 1g datafiles, and I need more space, would it be better to increase the size of the 1g to 2g or add another 1g datafile?. Is it better to keep them all uniform in size? I would think having multiple datafiles that could be spread across drive volumes would be beneficial, am I wrong? (Wouldn't be the first time :) TIA David Phillips Support DBA Gasper Corp. BAARF member #30 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dave Phillips INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus 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: Multiple Datafiles and performance?
If by "multiple datafiles that ..." you mean for a single tablespace then: It's possible to stripe a tablespace across drives "by hand", but it is no substitute for real, genuine hardware or software striping. I'm a little foggy on the "by hand" process since (I think) it is considered an archaic practice; but I'll give it try here. Through a process of creating a datafile, then creating a segment, then creating another datafile, then creating another segment or adding extents, etc. try to force the data to be as equally distributed as you can get it. It's a labor intensive process and requires constant fiddling to keep things in balance if the amount data in the database is constantly changing; and it works only if the data is accessed in a completely random manner. You must manually distribute the data equally among the data files and keep it equally distributed as things change. So *NOBODY* does it. If by "multiple datafiles that ..." you mean multiple tablespaces with tables distributed among the tablespaces in such a way as to achieve balanced I/O (or reasonable facsimile thereof) then: Hell yes! Busting up tablespaces into multiple data files just to be busting them up results in a database that is a big turd to maintain; and you end up with your free space scattered all over hell and half of Texas. And right now I have http://tinyurl.com/jaq4 in the CD drive. And it's on track 5: Gay Gordons / Camerons/ Brown Haired Maiden / SCOTLAND THE BRAVE. I'm ready for a fight now. Anybody want to fight?!! (No I'm not Scottish; but if it isn't Scottish it's still CRAP) > > Oracle 8.1.7.4 > Win2k > > What is the consensus on datafile sizing and the impact/overhead > multiple datafiles have on performance? > > For example, if I have one 2.5g datafile, and three 1g > datafiles, and I > need more space, would it be better to increase the size of > the 1g to > 2g or add another 1g datafile?. > Is it better to keep them all uniform in size? > > I would think having multiple datafiles that could be spread across > drive volumes would be beneficial, am I wrong? (Wouldn't be the first > time :) > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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: Multiple Datafiles and performance?
Dave, There is little about the size of datafiles to affect the performance of SQL statements, but there is much to affect the performance of backup and restore and administration. Uniform-sized datafiles simplify the administration of space. The speed of a backup or restore is a function of the largest datafile, so although files can now be sized in petabytes, it is not a good idea to do so. Personally, I stick to a max size of 2-8 Gbytes, depending on overall database size. Far faster to backup/restore lots of smaller files than to have one 500Gb monster holding things up. Also, if your storage subsystem isn't already implementing RAID-0 striping, then hand-striping multiple datafiles across volumes could help performance. Again, in that situation, many smaller uniform-sized files make the job easier than a few larger odd-sized files. Hope this helps... -Tim on 8/6/03 1:14 PM, Dave Phillips at [EMAIL PROTECTED] wrote: > Oracle 8.1.7.4 > Win2k > > What is the consensus on datafile sizing and the impact/overhead > multiple datafiles have on performance? > > For example, if I have one 2.5g datafile, and three 1g datafiles, and I > need more space, would it be better to increase the size of the 1g to > 2g or add another 1g datafile?. > Is it better to keep them all uniform in size? > > I would think having multiple datafiles that could be spread across > drive volumes would be beneficial, am I wrong? (Wouldn't be the first > time :) > > TIA > > David Phillips > Support DBA > Gasper Corp. > BAARF member #30 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Multiple Datafiles and performance?
Note that in previous mail, my reply starts from 2nd paragraph, the first one wasn't indented correctly for some reason.. Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, August 08, 2003 2:59 PM > > As for the datafiles and indexfiles being distributed over different > physical disks, well I can not confirm you are wrong, in our site id DID > boost the performance, and specially if you locate in separate physical > drives your redologs and datafiles (both indexes or data). Of course it > depends on the server's work load and how many people are simultaneously > accesing data and indexes. I can understand what you say but if many > different users are accesing the same table via indexes and both the table's > data and the indexes are on the same physical drive I think performance > should be worst than if they were separated, right or wrong? ... > > Wrong, because in multi-user environment, disk reading heads will never be > physically be in same place where you "left" them. (Well, almost never, > depending on your IO queue length). > > Are you comparing oranges with oranges here - if you got one disk in your > server and get performance boost by buying another disk in and putting some > files (doesn't matter which files) on new disk, then you are comparing 1 > disk vs. 2 disks, and that doesn't have anything to do with the type of > segments in the files. > > Tanel. > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Tanel Poder > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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).
Multiple Datafiles and performance?
Oracle 8.1.7.4 Win2k What is the consensus on datafile sizing and the impact/overhead multiple datafiles have on performance? For example, if I have one 2.5g datafile, and three 1g datafiles, and I need more space, would it be better to increase the size of the 1g to 2g or add another 1g datafile?. Is it better to keep them all uniform in size? I would think having multiple datafiles that could be spread across drive volumes would be beneficial, am I wrong? (Wouldn't be the first time :) TIA David Phillips Support DBA Gasper Corp. BAARF member #30 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dave Phillips 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: Multiple Datafiles and performance?
guess that depends on the gender of the person looking :) --- Henry Poras <[EMAIL PROTECTED]> wrote: > > > >a kilt, standing over a grate > > not quite Marilyn Monroe > > > -Original Message- > Daniel Fink > Sent: Thursday, August 07, 2003 11:39 AM > To: Multiple recipients of list ORACLE-L > > > Tim Gorman wrote: > > > > I don't mean to be argumentative, but every time I see assertions > like > > these, I suspect someone has been reading some rather discredited > books... > > Okay, now I have an image of Tim, in a kilt, standing over a grate, > with his > face painted with Blue vertical stripes and a big sword strapped to > his > back! > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Henry Poras > 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!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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: Multiple Datafiles and performance?
Tim, >Indexed access is a purely sequential activity from an I/O standpoint, >putting aside the reality that a buffer cache exists. First, we access the >root block of the index and read its contents in order to know where to >perform the next I/O (i.e. a branch block). Then we read that branch block >and read its contents in order to know where to perform the next I/O (i.e. a >leaf block). Then we read the leaf block and read its contents in order to >know where to perform the next I/O (i.e. a block in a table). And so on... >Since we are performing sequential single-block I/O (hence the name of the >wait event "db file sequential read"), how can separating datafiles >containing tables from datafiles containing indexes matter to performance? For arguments sake, the I/O steps that you mention is for a single user. Assume thousands of users, in which case, everyone would be hitting the same disk volume. Whereas, if they were spread, the I/O would be spread across 2 different volumes. Having said that, I am not for spreading them on different disk volumes. The goal should be spreading I/O evenly across all the available disk volumes. The S.A.M.E principle. Just for the heck of spreading the datafiles across disk volumes, I would not want the index datafile to be moved from a disk with 20% utilization to one with 90%. Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Multiple Datafiles and performance?
Win2K. If you decide to increase the filesize, do it to more than 2G (doesn't have to be by much). Of course, you didn't mention autoextend so this may not be an issue. Also, just how many physical disks do you have? Logical disks are not the issue. If you're going to get any increased performance you should be putting the second datafile on a second physical disk. Any how come you're not using some sort of RAID device (or don't you have your logical drives striped across your physical drives)? There is no easy answer to your question without an understanding of the reality of your disk layout. "Fermin Bernaus" @sammic.com> cc: Sent by: Subject: RE: Multiple Datafiles and performance? ml-errors 08/07/2003 08:05 AM Please respond to ORACLE-L In my experience, spreading datafiles across volumes (specially if you are careful not to locate the a table's datafiles and its indexes datafiles in the same drive) greatly increases performance. As for the file size, I can not say because I have not tested it, but I think it should have no real impact compared to splitting it. Reorganizing the database regularly is a better way to optimize performance. .. Fermín Bernaus Berraondo Dpto. de Informática SAMMIC, S.A. [EMAIL PROTECTED] http://www.sammic.com Telf. +34 - 943 157 331 Fax +34 - 943 151 276 .. -Mensaje original- De: Dave Phillips [mailto:[EMAIL PROTECTED] Enviado el: miércoles, 06 de agosto de 2003 22:14 Para: Multiple recipients of list ORACLE-L Asunto: Multiple Datafiles and performance? Oracle 8.1.7.4 Win2k What is the consensus on datafile sizing and the impact/overhead multiple datafiles have on performance? For example, if I have one 2.5g datafile, and three 1g datafiles, and I need more space, would it be better to increase the size of the 1g to 2g or add another 1g datafile?. Is it better to keep them all uniform in size? I would think having multiple datafiles that could be spread across drive volumes would be beneficial, am I wrong? (Wouldn't be the first time :) TIA David Phillips Support DBA Gasper Corp. BAARF member #30 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dave Phillips INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus 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:
RE: Multiple Datafiles and performance?
Goes by the name of: William Wallace Marilyn Monroe Cloud* *(of Final Fantasy 7) > -Original Message- > > Okay, now I have an image of Tim, in a kilt, standing over a > grate, with his face painted with Blue vertical stripes and a > big sword strapped to his back! > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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: Multiple Datafiles and performance?
There was a similar problem under HP-UX systems (I believe it was on version
10.0) in which the file size was limited to 2Gb, then they released a patch so that
files could get bigger than 2 Gb after setting a parameter and recompiling the kernel.
Under version 11.0 this was fixes (yet the parameter still exists). Sorry I have no
exact documentation on this issue at hand and what I am writing could not be exactly
right.
Fermin.
-Mensaje original-
De: Thomas Day [mailto:[EMAIL PROTECTED]
Enviado el: jueves, 07 de agosto de 2003 18:09
Para: Multiple recipients of list ORACLE-L
Asunto: RE: Multiple Datafiles and performance?
I don't know of any advantage to uniform datafile sizes. Back in the day
some DBAs preferred uniform datafile sizes so that they could easily move
datafiles from disk to disk to balance physical IO. With modern disk
configurations this strikes me as more work than it's worth. However, if
you really, really need that last little bit of "umph" then it is a
consideration.
4x1 or 2x2 - 4x1 has more "things" to keep track of. Otherwise, to the
best of my knowledge it's not an issue.
If a datafile autoextends to the 2G mark, under certain service pack
releases of Win2k the datafile becomes corrupted. As I remember the
discussion, MS had fixed one of their disk IO dlls to work over the 2G mark
but it the datafile size was under 2G the disk IO was handled by a program
that could not handle growing to 2G. So if the datafile was over 2G you
wouldn't run into the problem. MS does have upgrades to fix the problem.
Has your SA applied them?
I've been reading various white papers about RAID 10 vs. RAID 01
(mirror-then-stripe vs stripe-then-mirror). I've heard good arguments on
both sides and I hope that the experts can come to a consensus. Only RAID
manufacturers seem to be pushing RAID 5.
Right now Oracle's recommendation is SAME (Stripe All, Mirror Everything).
It is not the absolutely optimal situation but it will provide adequate
performance in most situations.
HTH
"Dave Phillips"
@gasper-corp.com cc:
> Subject: RE: Multiple Datafiles and
performance?
Sent by:
ml-errors
08/07/2003 11:24
AM
Please respond
to ORACLE-L
Gee, that question sounded a whole lot better when I wrote it yesterday
than it did this morning when I saw it. :) Maybe I should be a little more
vague.:)
The problem is there are a couple of things I am trying to accomplish. We
have clients that use our application that have specific performance issues
which I am working to improve. The other issue is to provide recommendation
to development/tech staff on initial setup of
database/tablespaces/datafiles etc.., along with hardware recommendations
for our application.
So, that being said, I'll try and ask better questions.
The environment is W2K, Oracle 8.1.7.2 or higher
All tablespaces are LMT
Most disk config's are 1 (or 2) Raid 1 along with a Raid 5 for basic
systems.
Most operate application 24/7
Questions:
1) Is there any advantage to uniform datafile sizes?
2) Is there any advantage/disadvantage for say 4 1G datafiles vs 2 2G.
RE: Multiple Datafiles and performance?
1). I don't know. But I always try to create datafile with same size (2G) and it is easier to estimate how many datafiles I can fit into one disk. 2). If all datafiles are on 1 disk, then 4 1G datafiles vs 2 2G. should not make much difference. If you could spread 4 1G datafiles into 4 different disks (mount points), then it is better. 3). I seem to read it from somewhere that Win2K has problem to handle datafile larger than 2G. Guang -Original Message- Dave Phillips Sent: Thursday, August 07, 2003 11:25 AM To: Multiple recipients of list ORACLE-L ... 1) Is there any advantage to uniform datafile sizes? 2) Is there any advantage/disadvantage for say 4 1G datafiles vs 2 2G. (Other than time to recover from datafile loss) It is probably safe to assume that the datafiles exist on a RAID 5. (for now) 3) Why the recommendation to take a Win2k datafile to just over 2G? For future apps I am pushing for optimal recommendations that go for more raid 1 sets or raid 10 over the Raid 5. This should allow for more flexibility for spreading out the i/o. Thanks for your patience and all the help. David Phillips Support DBA BAARF Member #30 -Original Message- Sent: Thursday, August 07, 2003 9:59 AM To: Multiple recipients of list ORACLE-L Win2K. If you decide to increase the filesize, do it to more than 2G (doesn't have to be by much). Of course, you didn't mention autoextend so this may not be an issue. Also, just how many physical disks do you have? Logical disks are not the issue. If you're going to get any increased performance you should be putting the second datafile on a second physical disk. Any how come you're not using some sort of RAID device (or don't you have your logical drives striped across your physical drives)? There is no easy answer to your question without an understanding of the reality of your disk layout. "Fermin Bernaus" @sammic.com> cc: Sent by: Subject: RE: Multiple Datafiles and performance? ml-errors 08/07/2003 08:05 AM Please respond to ORACLE-L In my experience, spreading datafiles across volumes (specially if you are careful not to locate the a table's datafiles and its indexes datafiles in the same drive) greatly increases performance. As for the file size, I can not say because I have not tested it, but I think it should have no real impact compared to splitting it. Reorganizing the database regularly is a better way to optimize performance. .. Fermín Bernaus Berraondo Dpto. de Informática SAMMIC, S.A. [EMAIL PROTECTED] http://www.sammic.com Telf. +34 - 943 157 331 Fax +34 - 943 151 276 .. -Mensaje original- De: Dave Phillips [mailto:[EMAIL PROTECTED] Enviado el: miércoles, 06 de agosto de 2003 22:14 Para: Multiple recipients of list ORACLE-L Asunto: Multiple Datafiles and performance? Oracle 8.1.7.4 Win2k What is the consensus on datafile sizing and the impact/overhead multiple datafiles have on performance? For example, if I have one 2.5g datafile, and three 1g datafiles, and I need more space, would it be better to increase the size of the 1g to 2g or add another 1g datafile?. Is it better to keep them all uniform in size? I would think having multiple datafiles that could be spread across drive volumes would be beneficial, am I wrong? (Wouldn't be the first time :) TIA David Phillips Support DBA Gasper Corp. BAARF member #30 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dave Phillips INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus 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 yo
RE: Multiple Datafiles and performance?
Gee, that question sounded a whole lot better when I wrote it yesterday than it did this morning when I saw it. :) Maybe I should be a little more vague.:) The problem is there are a couple of things I am trying to accomplish. We have clients that use our application that have specific performance issues which I am working to improve. The other issue is to provide recommendation to development/tech staff on initial setup of database/tablespaces/datafiles etc.., along with hardware recommendations for our application. So, that being said, I'll try and ask better questions. The environment is W2K, Oracle 8.1.7.2 or higher All tablespaces are LMT Most disk config's are 1 (or 2) Raid 1 along with a Raid 5 for basic systems. Most operate application 24/7 Questions: 1) Is there any advantage to uniform datafile sizes? 2) Is there any advantage/disadvantage for say 4 1G datafiles vs 2 2G. (Other than time to recover from datafile loss) It is probably safe to assume that the datafiles exist on a RAID 5. (for now) 3) Why the recommendation to take a Win2k datafile to just over 2G? For future apps I am pushing for optimal recommendations that go for more raid 1 sets or raid 10 over the Raid 5. This should allow for more flexibility for spreading out the i/o. Thanks for your patience and all the help. David Phillips Support DBA BAARF Member #30 -Original Message- Sent: Thursday, August 07, 2003 9:59 AM To: Multiple recipients of list ORACLE-L Win2K. If you decide to increase the filesize, do it to more than 2G (doesn't have to be by much). Of course, you didn't mention autoextend so this may not be an issue. Also, just how many physical disks do you have? Logical disks are not the issue. If you're going to get any increased performance you should be putting the second datafile on a second physical disk. Any how come you're not using some sort of RAID device (or don't you have your logical drives striped across your physical drives)? There is no easy answer to your question without an understanding of the reality of your disk layout. "Fermin Bernaus" @sammic.com> cc: Sent by: Subject: RE: Multiple Datafiles and performance? ml-errors 08/07/2003 08:05 AM Please respond to ORACLE-L In my experience, spreading datafiles across volumes (specially if you are careful not to locate the a table's datafiles and its indexes datafiles in the same drive) greatly increases performance. As for the file size, I can not say because I have not tested it, but I think it should have no real impact compared to splitting it. Reorganizing the database regularly is a better way to optimize performance. .. Fermín Bernaus Berraondo Dpto. de Informática SAMMIC, S.A. [EMAIL PROTECTED] http://www.sammic.com Telf. +34 - 943 157 331 Fax +34 - 943 151 276 .. -Mensaje original- De: Dave Phillips [mailto:[EMAIL PROTECTED] Enviado el: miércoles, 06 de agosto de 2003 22:14 Para: Multiple recipients of list ORACLE-L Asunto: Multiple Datafiles and performance? Oracle 8.1.7.4 Win2k What is the consensus on datafile sizing and the impact/overhead multiple datafiles have on perform
Re: Multiple Datafiles and performance?
At 09:59 AM 8/7/2003 -0800, you wrote: Hi! > The benefits of spreading the data over as many physical access paths ( ~ > disks ) using multiple datafiles notwithstanding, there is always the case > of too much. Keep in mind that at checkpoint time the DBWR need to visit > the header of every ( non read-only ) datafile. That's unlikely to be an The number of files had some impact in older Oracle versions (7.x). Starting from 8.0 I believe, this issue is somewhat relieved, as you probably know. Not all file headers are updated together and the update doesn't have to go to disk immediately (this goes for checkpoints caused by log switches). Actually I didn't or else I wouldn't have made the point. I guess that is how myths start and get perpetuated: by no keeping up with changes. Also, in older versions db_files parameter affected DBWR batch size and some buffer cache structures as well, IIRC. The biggest number of files I've had in a production database is about 1150, 960MB each. On WindowsNT4... =8´o Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Multiple Datafiles and performance?
The benefits of spreading the data over as many physical access paths ( ~ disks ) using multiple datafiles notwithstanding, there is always the case of too much. Keep in mind that at checkpoint time the DBWR need to visit the header of every ( non read-only ) datafile. That's unlikely to be an issue for a few dozen datafiles, but if you are getting into hundreds of them, keep that in mind. If you can get the striping done without multiple datafiles you get the best of both worlds. I am just suffering that exact issue on a test system for an upgrade with an extremely poor IO subsystem where bottlenecks like this get magnified. At 07:24 AM 8/7/2003 -0800, you wrote: Gee, that question sounded a whole lot better when I wrote it yesterday than it did this morning when I saw it. :) Maybe I should be a little more vague.:) The problem is there are a couple of things I am trying to accomplish. We have clients that use our application that have specific performance issues which I am working to improve. The other issue is to provide recommendation to development/tech staff on initial setup of database/tablespaces/datafiles etc.., along with hardware recommendations for our application. So, that being said, I'll try and ask better questions. The environment is W2K, Oracle 8.1.7.2 or higher All tablespaces are LMT Most disk config's are 1 (or 2) Raid 1 along with a Raid 5 for basic systems. Most operate application 24/7 Questions: 1) Is there any advantage to uniform datafile sizes? 2) Is there any advantage/disadvantage for say 4 1G datafiles vs 2 2G. (Other than time to recover from datafile loss) It is probably safe to assume that the datafiles exist on a RAID 5. (for now) 3) Why the recommendation to take a Win2k datafile to just over 2G? For future apps I am pushing for optimal recommendations that go for more raid 1 sets or raid 10 over the Raid 5. This should allow for more flexibility for spreading out the i/o. Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Multiple Datafiles and performance?
> As for the datafiles and indexfiles being distributed over different physical disks, well I can not confirm you are wrong, in our site id DID boost the performance, and specially if you locate in separate physical drives your redologs and datafiles (both indexes or data). Of course it depends on the server's work load and how many people are simultaneously accesing data and indexes. I can understand what you say but if many different users are accesing the same table via indexes and both the table's data and the indexes are on the same physical drive I think performance should be worst than if they were separated, right or wrong? ... Wrong, because in multi-user environment, disk reading heads will never be physically be in same place where you "left" them. (Well, almost never, depending on your IO queue length). Are you comparing oranges with oranges here - if you got one disk in your server and get performance boost by buying another disk in and putting some files (doesn't matter which files) on new disk, then you are comparing 1 disk vs. 2 disks, and that doesn't have anything to do with the type of segments in the files. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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: Multiple Datafiles and performance?
I don't mean to be argumentative, but every time I see assertions like these, I suspect someone has been reading some rather discredited books... So, my apologies in advance, but comments are inline below... > > In my experience, spreading datafiles across volumes (specially if you are > careful not to locate the a table's datafiles and its indexes datafiles in the > same drive) greatly increases performance. The assertion that performance is enhanced by distributing datafiles containing tables and datafiles containing indexes to different volumes is a myth. Think about it. Indexed access is a purely sequential activity from an I/O standpoint, putting aside the reality that a buffer cache exists. First, we access the root block of the index and read its contents in order to know where to perform the next I/O (i.e. a branch block). Then we read that branch block and read its contents in order to know where to perform the next I/O (i.e. a leaf block). Then we read the leaf block and read its contents in order to know where to perform the next I/O (i.e. a block in a table). And so on... Since we are performing sequential single-block I/O (hence the name of the wait event "db file sequential read"), how can separating datafiles containing tables from datafiles containing indexes matter to performance? > > As for the file size, I can not say because I have not tested it, but I think > it should have no real impact compared to splitting it. Reorganizing the > database regularly is a better way to optimize performance. And in what ways does "reorganizing the database regularly" improve performance? To break the question down into more manageable pieces: * In what way does rebuilding a table improve performance? * In what way does rebuilding an index improve performance? There are specific answers to these questions. For example, there are situations in which both tables and indexes can become "sparsely populated". Tables become sparsely populated due to large-scale deletions. Indexes become sparsely populated due to monotonically-ascending data values inserted transactionally. In these cases, how can you detect this condition? The package DBMS_SPACE has procedures that help for tables and the ANALYZE INDEX ... VALIDATE STRUCTURE command helps for indexes. Does rebuilding a table or index that is not "sparsely populated" aid performance in any way? Quite frankly, no... ...well, there is one condition involving the "clustering factor" of an index where a rebuild of the table can help, but you'll end up hosing the "clustering factor" of other indexes. It is a case of favoring one index over another, and that is a decision that requires intimate knowledge of the application's usage of the table and its indexes... So, "reorganizing the database" on a regular basis is purely a waste of time. Regularly monitoring the database for "sparsely populated" tables and indexes, and then determining if the condition is affecting performance of any important processes before rebuilding, will indeed help performance. Sorry for the combative tone, but I hope this helps... -Tim > > > -Mensaje original- > De: Dave Phillips [mailto:[EMAIL PROTECTED] > Enviado el: miércoles, 06 de agosto de 2003 22:14 > Para: Multiple recipients of list ORACLE-L > Asunto: Multiple Datafiles and performance? > > > Oracle 8.1.7.4 > Win2k > > What is the consensus on datafile sizing and the impact/overhead > multiple datafiles have on performance? > > For example, if I have one 2.5g datafile, and three 1g datafiles, and I > need more space, would it be better to increase the size of the 1g to > 2g or add another 1g datafile?. > Is it better to keep them all uniform in size? > > I would think having multiple datafiles that could be spread across > drive volumes would be beneficial, am I wrong? (Wouldn't be the first > time :) > > TIA > > David Phillips > Support DBA > Gasper Corp. > BAARF member #30 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
