Dictionary managed objects

Quote:

---

Originally posted by slimdave
You are still choking the i/o for each table and for each index by not spreading them all over every available device.

---

I think this all depends on type of application you are running.
Sure, for a data warehouse implementation, with a massive budget for hardware, a ****-hot raid array is just the business.
But for the majority of us average, behind-the-times DBAs, building and supporting high-volume OLTP databases; trying to bleed as much throughput out of our 5-8 SSA devices due to budget contraints as we can, seperating data and indexes is a basic step and should not be overlooked or forgotten. Also, we do not want the overhead associated with writing parity bits.
Although you are right when you say that a read/write operation on a table will be seperate from that on an index, you are talking at Oracle's level. Those requests are then dealt with by the o/s, if your data and indexes are on seperate spindles and controllers, performance will be greatly enhanced.

Brgds,

Andy

Quote:

---

Originally posted by DaPi
I could envisage a situation where you are hammering one table and one index - device separation reduces (avoids?) head movement. I suspect that is not a real-life scenario.

---

Consider two scenarios -- one session doing the hammering, and multiple sessions doing it.

If it is one session then the index and table are not being accessed simultaneously -- Oracle flip-flops between the two. So there's realy no benefit to the separation. Also there is going to ne head movement anyway because there are no guarantees that contiguous logical blocks on the data file map to a contiguous set of physical blocks on the disk, especially when RAID is involved.

If there are multiple users doing the accessing then head movement is unavoidable. The aim is just to provide the maximum possible i/o rate for the index, and the maximum possible i/o rate for the table. Spreading each one over all the devices is the way to do that.

Quote:

---

Originally posted by oddster
I think this all depends on type of application you are running.
Sure, for a data warehouse implementation, with a massive budget for hardware, a ****-hot raid array is just the business.
But for the majority of us average, behind-the-times DBAs, building and supporting high-volume OLTP databases; trying to bleed as much throughput out of our 5-8 SSA devices due to budget contraints as we can, seperating data and indexes is a basic step and should not be overlooked or forgotten. Also, we do not want the overhead associated with writing parity bits.
Although you are right when you say that a read/write operation on a table will be seperate from that on an index, you are talking at Oracle's level. Those requests are then dealt with by the o/s, if your data and indexes are on seperate spindles and controllers, performance will be greatly enhanced.

Brgds,

Andy

---

The trouble is that the myth of table and index separation is so ingrained that many people now view it as "a basic step" -- it's like it's as natural as breathing.

I don't think that the benefits of spreading of table and index segments are confined to RAID arrays. What you want to do on anything but the smallest, simplest system is spread the load out as much as possible. When you have thirty users accessing the db at the same time you want to spread out your hot tables and indexes so that the disk that is accessed is virtually random. When a batch job scans a couple of big tables you want to spread out the impact as much as possible, not focus it on one or two disks.

Even if you have only 4 simple unraided disks, spread the table over all four, and spread the index over all four. When the system is being loaded up by your users, you want to see all the disk lights flashing at the same time. If you saw disk2 2 & 4 light up, then 1 & 3, then 2 & 4 again, it would be a pretty clear indication that your loadis not spreadout, and someone, somewhere, is getting excessive waiting on i/o operation. Monitor the length of the disk queue on your disks. If the values are not pretty evenly distributed, then you have the same problem.

The same logic would suggest spreading RBS and Temp as well . . ?

Is that a trick question? ;)

The types of files that i would isolate would just depend on what RAID level you wanted.

If you want to run your application on RAID5 for safety/economy/whatever, but want to put temp on a better performing RAID level, then that would be a good enoiugh reason IMHO to isolate temp from your regular data files. Maybe, though, I'd just keep an eye on that device. If you have an OLTP system that doesn't need temp very much then you could just be wasting potential i/o bandwidth.

The main thing, though is to stop worrying about indexes and tables being colocated on the same device. I think that some of the resistance to the "mix 'em and spread 'em" methodology, curiously enough, is that it is too easy. DBA's seem to make things complex, and have a hard time letting go of complexity. It seems to be extraordinarily difficult to convince Oracle 7 dba's, who are used to ...

* manually specifying initial, next, pctfree, initrans, etc.
* specifying the location of every single table and index
* worrying about all that other crap we used to have to do

... that they can just relax -- use LMT's with fixed sizes, automatic space management, mix tables/indexes and spread them over all the devices.

Convincing them that it is not only easier, but also performs better? Next thing to impossible.

Quote:

---

Originally posted by slimdave
Is that a trick question? ;)

---

Am I being dim? coz I don't see what the trick might be :confused:

Oh OK, i was double-guessing you.

I thought that maybe if i said "yes" you would triumphantly cry "Ah ha! But what about ... ?" and then proceed to demolish and ridicule my answer.

On the other hand, I also thought that might happen if i said "no" as well.

I alway put tables with few delete in one tablespace and tables with a lot of delete in another tablespace. It will help you for reorg your tablespaces. There are some tables with daily delete which you may need to reorg quite often.

Quote:

---

Originally posted by xyz2000
I alway put tables with few delete in one tablespace and tables with a lot of delete in another tablespace. It will help you for reorg your tablespaces. There are some tables with daily delete which you may need to reorg quite often.

---

Would you like to explain ...

i) what you mean by reorg?
ii) what the benefit of it is?
iii) how it helps to have the tables in different tablespaces?

Quote:

---

Originally posted by slimdave
The trouble is that the myth of table and index separation is so ingrained that many people now view it as "a basic step" -- it's like it's as natural as breathing.

I don't think that the benefits of spreading of table and index segments are confined to RAID arrays. What you want to do on anything but the smallest, simplest system is spread the load out as much as possible. When you have thirty users accessing the db at the same time you want to spread out your hot tables and indexes so that the disk that is accessed is virtually random. When a batch job scans a couple of big tables you want to spread out the impact as much as possible, not focus it on one or two disks.

Even if you have only 4 simple unraided disks, spread the table over all four, and spread the index over all four. When the system is being loaded up by your users, you want to see all the disk lights flashing at the same time. If you saw disk2 2 & 4 light up, then 1 & 3, then 2 & 4 again, it would be a pretty clear indication that your loadis not spreadout, and someone, somewhere, is getting excessive waiting on i/o operation. Monitor the length of the disk queue on your disks. If the values are not pretty evenly distributed, then you have the same problem.

---

Dave :

By this method you will be even worsing I/O. I guess you know how CPU handles I/O, queing requests that need high I/O (FTS).

When CPU sees that a requesting process which needs only 30 Blocks(index) & other one requesting 1000 blocks from say DISK1,
then CPU will first complete smaller I/O request. And now Indexed data will always have much less amount of data then Table,
so when User1 requesting FULL data from tableX and other user requesting Index/Table(by rowid) data which ammounts much less
I/O then formeer, user1 process will have to wait for this request. And I beleive there will be much such smaller I/O requests
than higer I/O, so process requesting higher I/O(particularly Table data requests more than 20% for big tables) will suffer
waits if small indexes are being mixed up on disks where Big tables are present.

So, I suggested before in my post also, lemme eloborate.

Consider you have 6 Devices/Disks, I will have 3 TS_I for indexes and 3 TS_T for tables, however I will locate the datafiles
of these 6 TS on 2 devices each ( accroding to there capablities to fetch max I/O per fetch ). Huge tables/indexes will
be put on TS_I1/TS_T1 which will be on say Disk/Device 1 and 2 which has high i/o per fetch. Medium sized will be put on TS_I2/TS_T2
and so.

Now as you said "disk that is accessed is virtually random", I really dont see any point here because I/O requests will
be handled by CPU which decides which process has to queue/wait, so out of 30 users requesting data from these devices have,
some have to wait for some other requesting less I/O.

Abhay.