I'm fairly sure I know the answer to this - should I use a single extent or multiple extents on a 10.2B database (Linux). I'd opt for multiple extents.
My issue is I have to refute someone who says a single extent is 'better' because it means the variable extent cannot fill and bring the database down.
So, can someone please point me at definitive expert documentation on this?
From my experience multiple extents perform much better. Even if you had multiple extents your last extend would be variable.
From my experience all variable extents are easier to manage and any performance gains related to fixed extents are imaginary EXCEPT in very specific situations or on very old operating systems.
For example, imagine your DB grows by 1 Gb per month. That's 30ish Mg a day = 1.x Mg per hour. Or worse, assume your business only runs M-F 9-5: that 1 Gb of growth only occurs during (20 days X 8 hours = 160 hours) which equals about 6.5 Mg per hour (6.5 Mg X 160 hours ~= 1 Gb).
On a storage area with 512 blocks per cluster and a 4 Kb blocksize, each extend operation will grow the extent by 2Mg. This means that your system will "suffer" through 3-4 extensions per hour at a cost of a few milliseconds each. I.e. the cost of variable length data extents in this case is less than one second per hour.
Variable length AI extents are a different story but I still typically deploy variable length AI extents. The performance hit here is more measurable BUT what you must measure is the real-life effect on your business. In my experience that real-life performance cost is also negligible to the business even if in terms of absolute values it is measurable.
Thanks for the replies guys.
I'm a bit rusty with database structures - am I right is saying that a multi-extent database can have many fixed but only 1 variable extent - OR - am I picking up that a database can have multiple variable extents?
Also, is the 2GB limit we typically see on the variable extent an OS limit (SUSE 11 64bit, ext3) or can we have that bigger with a database setting?
You can have one variable length extent per storage area.
The 2 Gb limit was imposed by very old operating systems but is enforced if you have a WorkGroup DB license. If you have an Enterprise DB license you must run "proutil db -C enablelargefiles".
I would have to defer to Paul as I am no DB expert. However, I can say for certain we have seen noticeable improvement in performance when using multiple extents. This is on windows not linux.
Multiple extents means you have multiple file handles. I do not know how the OS / FS support reliable simultaneous access for multiple processes to a single file, but I can imagine that there will be some sort of contention, at least some of the time. Distributing the access over more files may have the type of effect that Richard talk about.
On the other hand, most of the new data generated in a business application originates from the transactional functions, which tends to be generated sequentially, updated rarely, if ever. That data is often time sensitive and will loose relevance to most queries within the span of one to 12 months. So the bulk of the access will in any case be near the high water mark...
Simon L Prinsloo
There are the other factors that affect performance much stronger than a type of db extents. For example, record fragmentation and logical scatter factors. The last ones are in turn affected by the block chains. The chains are affected not only by the toss/create limits but also by a type of transaction activity. Etc etc.
I do not think there is a consensus on Single vs Multiple extents per area question. This discussion is as old as version 9.
Both camps of gurus have valid points and will stand on their own. The simple solution -- 1 extent per aria works for me on 1.5 TB database. But other people have valid arguments to have multiple fixed extents instead of one huge variable.
In OE one area could have only 1 variable extent.
In Oracle I have several "variable" (autoextendable) "extents" (.dbf files) per "area" ( tablespace ) . And for a seasoned Progress DBA it is very unusual to see how all of them grow somehow together.
Guys, thanks a million for the feedback.