Re: The cardinality of tablespace and datafile

Just see below, I think we might have come to an agreement, just having different reasons justifying what we do.

One point not brought up below, but I will mention it here. One of the reasons that I separate into multiple tablespaces, and also separate indexes from data, is to allow me with a utlb/estat report to easily see if my indexes are being used. If in the data tablespaces I have a large difference in the number of read requests to the number of blocks read, I know that queries are doing full scans, and are not using the indexes.

It appears that I might agree with your justification, but there are other reasons for separating indexes, data, RBS, redo, and temp that you may not be considering. My reasons are more for the ease of detecting problems after they occur, since I rarely have the luxury of knowing what is going to be done to the database, until after it happens, and doesn't work as the developers expected.

--
Robert Fazio, Oracle DBA
rfazio_at_home.com
remove nospam from reply address
http://24.8.218.197/
"akolk - gelrevision.nl" <akolk_at_gelrevision.nl> wrote in message
news:3940298C.6F1B07E5_at_gelrevision.nl...


> Ok,

>

> I am wearing it ;-)

>

> There is an exception though.  In the case of

> redo logs, the idea is to make those writes occur as quickly and

 efficiently


> as possible

>

> Well, consider this:

> Writing 64K in one operation is faster than writing 64 in 32 operations of

 2K.


> So if the LGWR goes too fast, piggy backing will suffer and that could

 hurt the


> response time of your OLTP transactions.

> Striping the redo logfiles is an option if you write in large chunks (say

> 128K), having 4 or 8 disks will cause writes of 16K or 32K to happen to

> different disks.


I think I agree with your above statement, but what I do is leave a 9 gig
disk with 1 redo log on it raw of possibly as small as 100m.  I really do
get great performance this way.  It can just be a waste of disk, but the
gains in performance are worth the expense.



> Also I think that RAID-5 is not a problem, the problem is that people size


I work with sun disk primarily, and RAID5 is a problem, they do all raid at
the OS level, and with 4 I/O's per write request all processed at the OS
level.



> wrong with RAID 5. They normally need more disks (to handle the extra

 number of


> I/Os) in a RAID5 configuration.

> The idea is that if you have 5 disks in a RAID0, you need probably double

 that


> if you switch to RAID5. So if you use RAID5 size it as RAID10 (RAID0 +

RAID1).

I think that  you are mistaken on your terminology, Raid10 is very different
that Raid5, not a version of it.
Raid 0 is stripping.  No protection, but speed.
Raid 1 is mirroring.  Good protection, but expensive, and minimal speed
improvement on reads.
Raid 3 is optimal for writing, but not reading.
Raid 5 is optimal for reading, but not writing.
Raid 10 is Raid0 with mirroring.  Expensive, but fast.

We use OPS, and all we use is RAID10



>

> Another note: most systems that I see that are used for OLTP, people size

 their


> systems based on storage not workload. That means that people buy one or 2

> large disks and get performance problems if they try to run with several

> concurrent users.


I have a 75G peoplesoft database (this one is my performance problem)  layed
out across 4 arrays with 4 controllers of 14 9G disks.  This is sized for
workload, not storage.  I think we agree again (WOW!)



>

> On point 2:

>

> You indicate yourself that there is a difference between OLTP and DSS. I

 assume


> that OLTP means larg(er) number of users as compared to DSS (single user,

 but


> multiple processes for PQ).

> In OLTP the number of I/Os is important. If a transaction takes 3 I/Os,

 and I


> need to do 24000 transactions (no I am not making this number up, it is a

 real


> production system where they can actually do 150000 transactions  per

 minute or


> 400 sec. I need 400*3 = 1200 I/Os per second. If a disk can do 100 I/Os I

 need


> 12 disks or more. This assumes that my data and index are striped over

 these 12


> disks. If on the other hand I would use  6disks for the index (or less

 because


> if takes less space) and 6 disks for the data, I would have a problem if 2

 I/Os


> go to either the index or the data. I would be limited to 300 or less

> transactions.

>


I must not have been clear earlier.  I did state that as you approach OLTP,
that the difference between the types of requests to the disk become more
similar, and the need to stripe becomes more important.  So still
agreeing...



> On the recovery note I agree with you

>


In conclusion,  I think we both have different situations, and are handling
them in different ways.  Both correct for our needs.  I also always use raw
devices for all of my datafiles, and use O/S tools to montitor hotspots.  I
stripe as needed by the datafile, and usually don't consider what is on the
drive, just the load.



>

>

> Bob Fazio wrote:

>

> > Get out you flame retardant clothing.

> > See below.

> >

> > --

> > Robert Fazio, Oracle DBA

> > rfazio_at_home.com

> > remove nospam from reply address

> > http://24.8.218.197/

> > "akolk - gelrevision.nl" <akolk_at_gelrevision.nl> wrote in message

> > news:393FAAB4.31366B14_at_gelrevision.nl...

> > > It is not wise to dedicate disks for certain functions. The best and

 simplest thing


> > > to do is stripe over as many disks as you can .....

> >

> > What?  The idea is not to stripe over as many disks as you can. If

 anything


> > the goal is to distribute the I/O requests over

> > as many disks as you can.  There is an exception though.  In the case of

> > redo logs, the idea is to make those writes occur as quickly and

 efficiently


> > as possible.  Because of the way that redo logs are written, the best

> > practice for performance is to actually give them their own disk,

 Period!


> > Any other I/O's to the disks dedicated to the redo logs, will cause the

 head


> > to move to accommodate the request.  This will cause the head to have to

> > move back to finish the redo writes.  If they are the only one on the

 disk,


> > the head moves slowly and only for the redo write requests.  I realize

 this


> > is an expensive proposition, one disk or two if mirroring per redo log

 file,


> > but if you want performance you will get it here.  Also with that

 approach


> > there is no need to stripe.  If you choose not to do that, most likely

 for


> > the cost issues, then I agree stripe heavily.  As far as where, I don't

> > really think redo's matter much, but I would most likely choose the same

 as


> > the data, and not the index.  The only thing is that I would suggest

 that it


> > not be on a RAID 5 device,  remember they are basically only writes.

> > Inserts into the data section will most likely be similar types of

 writes to


> > the redo's whereas the writes to the index are quite random.

> >

> > >

> > > Point 2 is old and has never been true and can really hurt

 performance/throughput


> > > of your database

> >

> > Again What?  I will address the second first.  How in the world do you

 think


> > separating the indexes from the tables will hurt performance?  A query

 will


> > make a request first on the index and second on the table, after it

 knows


> > where to look.  The index read's although can be a full scan are often

 very


> > random.  The scans on the data are more than likely not.  The striping

 on


> > data tablespaces can be much less agressive than on the indexes.  In a

> > heavily utilized oltp system, that starts to fade, but in a DSS it is

 more


> > than true.  What will ultimately occur though is that the data area in a

 DSS


> > system will require, due to its size, to eventually be striped.  As for

 it


> > being old and never true, what Oracle school did you go to, because I

 have


> > never before heard that statement made.  Again the idea is to distribute

 the


> > number of I/O requests to the operating system to as many disks and

> > controllers as possible for the entire system, but not overload them

 all.


> > Heavy striping can cause most requests to go through almost all disks

 and


> > controllers for each request.   You are trying to balance the load, not

> > saturate it.

> >

> > >

> > > Also remember Databases is about disks/IOs not number of files ! (1

 disk
 with 10


> > > files or 1 file will perform as bad).

> >

> > Ok, you do know something.  1 disk with 10 files MAY perform as poorly

 as 1


> > file on 1 disk.  Indexes are the exception to this.  Indexes can and

 will


> > get fragmented and require rebuilds since they don't actually delete

 keys of


> > deleted rows.  The tables however will reuse that space.  Due to the

> > previous statement,  Indexes can and will really screw up the

 fragmentation


> > of a tablespace if indexes are in the same tablespace as the data,

 making


> > those tables that require full table scans to go all over the place.

 This


> > will also screw up the O/S that is trying to perform read ahead

 requests.


> > There will be a lot of misses, since it won't be able to anticipate

 where it


> > really needs to go next.

> >

> > Even if you want to use one and only one disk/filesystem.  At the very

 least


> > you need to separate the indexes from the data.

> >

> > The number of files, as was stated,  is to allow recovery to occur in a

> > timely fashion, and with as little disruption as possible to the entire

 user


> > community.  If you have one tablespace with one datafile, a single

> > corruption will take down the entire database.  A single datafile in a

> > single tablespace with many tablespaces and datafiles, may not even be

> > noticed during the entire recovery phase.

> >

> > >

> > > Waiting for replies ;-)

> > >

> > > Anjo.

> > >

> > >

> > > Andreas Stephan wrote:

> > >

> > > > Hi Chuan,

> > > >

> > > > here are some rules I use when I install a database:

> > > >

> > > > 1) never put rollback segment tablespace on a drive containig data

 (table) or


> > > > index tablespaces

> > > > 2) never put data and index tablespaces on the same drive if they

 interact


> > > > 3) use a max of 2Gig Datafiles to keep maintenance simpler and to

 avoid


> > > > unnecessary recovery time

> > > >     (it is easier and quicker to recover one 2 Gig Datafile than one

 of
 20 Gig).


> > > > You can transport a

> > > >     2 Gig Datafile on an old dat tape but with 20 Gigs?? And copying

 from tape


> > > > to disk or over the lan

> > > >     will not cause any problems if you have only 2 Gig Datafiles.

> > > > 4) try to use partitioned tablespaces if possible. Split the data

 onto
 the


> > > > partitions. Each partition should

> > > >     reside on a separate drive to maximize performance

> > > >

> > > > putting datafile on different drives will only help if you have more

 than one


> > > > tablespace and your objects are

> > > > well separated onto these tablespaces (index objects in index

 tablespaces,


> > > > tables in data tablespaces, tables that

> > > > are often joined moved into separate tablespaces on separate disks

 or


> > > > implemented using clusters etc..)

> > > >

> > > > hth

> > > > Andy

> > > >

> > > > chuan zhang schrieb:

> > > >

> > > > > Hi, all,

> > > > >

> > > > >   Could anyone tell me that which the following option is better

 in
 terms of


> > > > > performance:

> > > > >

> > > > > 1) One tablespace with one big datafile, this datafile might

 increase
 more


> > > > > if there are more storage.

> > > > >

> > > > > 2) One tablespace with many small or medium datafiles

> > > > > or

> > > > > 3) Splitting many datafiles into many tablespaces.

> > > > >

> > > > > Note, I have considered to put the datafiles into different drives

 in
 order


> > > > > to increase the performance.

> > > > >

> > > > > Thanks

> > > > >

> > > > > Chuan Zhang

> > > > >

> > > > > Asiaonline Ltd Co.

> > >

> > >

> > >

> > >

> > >

> > >

> > >

>