I don't think you meant that, did you? (pctincrease=0?)Quote:
Originally posted by slimdave i) PCTFREE=0. Always.
Printable View
I don't think you meant that, did you? (pctincrease=0?)Quote:
Originally posted by slimdave i) PCTFREE=0. Always.
* Glurk *Quote:
Originally posted by DaPi
I don't think you meant that, did you? (pctincrease=0?)
You're right. Must have typed it during a brain fart.
Suggest you go edit the original post before some unsuspecting person picks it up.
Done. I don't want to contribute to the birth of a new oracle myth.
Im late again...
Yep agreed with the distribution of datafiles across the disks so that extents are equally distributed. This would be true for uniform LMT only (call this the poor man's stripping technique).
But what in case of DMT where the first datafile is filled then it moves to the second datafile and so on, or in the case of SYSTEM managed (or the autoallocate) LMT's. How do you spread the data in that case ?
I know something is going to hit me ;)
Well, that's a tricky business. All I can suggest is that your keep your data files small, forcing many of them to be used and alowing them to be spread about.
It's inconvenient to do it that way, but the alternative would be to create a single array over all your disks.
Dave :Quote:
Originally posted by slimdave
But all you are doing is increasing the chances of contention on the table, and increasing the chances of contention on the index. There is no way to completely eliminate all contention, but the aim should be to reduce the probability of contention. You do that by increasing the possible number of devices that an operation might use.
Suppose you have eight devices. You could choose to segregate tables and indexes by dedicating (for example) four of them to indexes and four of them to tables, or you could mix them up by spreading both indexes and tables across all devices.
Here's a table that compares the number of devices available to support different types of data access
So you see, with the segment types spread across all devices you always have the entire i/o subsystem available.Code:
Query Type Segregated Mix
========== ========== ===
100% Table 4 8
100% Index 4 8
50/50 8 8
By segregating the segment types you drastically reduce the flexibility. By focussing on the 50/50 situation you are only looking at a situation where the segregation would work There are many situations where you could be looking at an instance where you have sessions suffering excessive waits on i/o operations because 4 of your devices are choked -- the other four devices might be only at 25% of their capacity.
I think you got me wrong..Well I was insisting for IndexA on TableA to be stripped, but never said ALL indexes on set of devices and ALL tables in diff set of Devices, ofcourse by that chance of contention will be high.
Herez my rule
* Strip IndexA's data pointing to TableA's in diff sector/disk -- always.
* Have index/table in 50-50 ratio per device. -- Here dont have any Index thats pointing to Table in same sector or rather in simple words have Index of diff tables from the existing tables in this sctor/disk/device.
* use LMT(uniform) as much as possible.
* ok if DMT the PCTincrease 0 alwyas for all segments (as you suggested but I would have Pctincrease 1 or any dummy val at tablespace level simply for autocoalasce -- though I am not convinced yet but my colluege(DBA's) prefer this)
Abhay.
Well thats the biggest load of crap I have ever heard it.Quote:
Originally posted by slimdave
Why are you splitting tables and indexes into different tablespaces? There's no performance advantage, you know -- that's an urban legend.
One of the most basic principles of OFA is to seperate your data and indexes onto different spindles, if possible.
If your storage is a single array and you have little knowledge of the application(s), it may best to follow Oracle's general advice on object storage.
The aim of this strategy is ensure no object in an Oracle8+ database ever has more than 1024 extents:
Object size Tablespace extent size
<128M 128K
<4096M 4M
>4096M 128M
More information can be found on Metalink
Brgds,
Andrew
What you are doing is just as bad as the scenario I laid out in my little map, except you are doing it at the table/index level instead of the schema level. You are still choking the i/o for each table and for each index by not spreading them all over every available device.
The only way your methodology makes sense is if an index by rowid access reads the table and index simultaneously, an Oracle myth that noone should believe in anymore. Since they don't, your methodology doesn't work.
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.Quote:
Originally posted by slimdave
The only way your methodology makes sense is if an index by rowid access reads the table and index simultaneously,