Forum OpenACS Q&A: Response to Oracle tablespaces - why pctincrease 1?

Posted by Sebastiano Pilla on
Sebastiano - do you have anything written up on recommended tablespace management for an OpenACS installation? I'm fairly certain that nearly everyone here follows the install docs which derive from aD's early experiences. If you have something available that could be integrated into the install docs or have time to put something together, I'm sure it would be deeply appreciated by many.
I've already considered to write something addressing the whole Oracle installation (time constraints permitting) but I've held it off because I was and I'm still undecided about the recommended Oracle version. The install docs talk about 8i, and OpenACS is known to work with it; however nowadays perhaps the most widely downloaded version is 9i, which changes a lot of things (for example, connect internal is no longer allowed).

And also with respect to the tablespace creation, it's very difficult to write something that would address equally well the need of somebody just playing with OpenACS on their own box and of somebody needing a production-type installation on a Sun E10000 <grin>.

If you could give me some hints about what would be needed, I'll gladly write up something; for the moment however it would suffice if the comment I entered on the install doc page could be put into the official documentation. I repeat it here, feel free to correct any English mistakes I could've made.

Since the documentation talks about Oracle 8i, I have a couple of comments on the tablespace creation statement. I would rewrite it as follows:
create tablespace BIRDNOTES
  datafile '/ora8/m02/oradata/ora8/birdnotes01.dbf'
  size 50M
  autoextend on
  next 10M
  maxsize 300M
  extent management local
  uniform size 32K;
In other words, I would place a limit on the autoextend capability, to avoid that datafile growing to fill the entire partition, and I would make it so the size increments are all of a fixed, predictable amount. I would drop entirely the default storage clause, and rather use a locally managed tablespace, that guarantees that all its extents are of the same, fixed size. There's no particular reason to use 32K as extent size, I just picked that number: there are probably some objects in the OpenACS model that could be efficiently stored with that extent size, but there could be many others that instead wouldn't.