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

The install docs still say to use pctincrease 1 when creating an Oracle tablespace:

SVRMGR> create tablespace birdnotes datafile '/ora8/m02/oradata/ora8/birdnotes01.dbf' size 50m autoextend on default storage (pctincrease 1);

Why?? This setting was in he aD install docs since way back when too, but I never did know why it was there. As far as I can remember, all the books and docs I've ever seen mention pctincrease say it should always be 0. So where did this 1 value come from? And does it make any sense at all to use it?

Collapse
Posted by Sebastiano Pilla on
Scroll to the bottom of the page you linked and you'll see my comment about the tablespace creation. However, pctincrease 1 was used to have the benefit of SMON automatically coalescing free extents in a tablespace without the trouble of wildly varying extent sizes (that you could get with the default of pctincrease 50).

These days, with Oracle 8i and higher, I suggest using locally managed tablespaces. BTW, the pctincrease 1 myth was thoroughly debunked on a recent thread in the comp.databases.oracle.server newsgroup.
Collapse
Posted by Don Baccus 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.
Collapse
Posted by Yonatan Feldman on

i have some documents that i was working on for deployment within arsdigita that i believe have some useful information. they were written in collaboration with Richard Li, Rob Mayoff, and Mark Silis. they are not finished and have some stuff that doesn't pertain to us, arsdigita specific stuff. if anyone feels like bringing these up to date so we can post them on openacs.org let me know and i will email them to you.

here is the table of contents:

note: sections 6 through 10 are basically empty.

Contents

1 Introduction
2 Hardware
2.1 Development
2.2 Staging
2.3 Production
3 Operating System
3.1 Kernel parameters
4 Database
4.1 Database configuration
4.2 Schema configuration
4.3 Maintenance
4.4 Backups
5 Servers
5.1 Running the servers
5.1.1 Installing daemontools
5.1.2 Creating a daemontools service
6 ACS
7 Development
8 Testing
9 Scaling
10 Monitoring
Collapse
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.
Collapse
Posted by Malte Sussdorff on
Oracle suggests to use extends 160K. Furthermore you might want to set the limit to

create tablespace service_name datafile
'/ora8/m02/oradata/ora8/service_name01.dbf' size 50m autoextend on
next 640k maxsize 2147450880 extent management local uniform size 160K;

Furthermore make sure that the extend size (next XXX) is a multiple of the uniform size. And the maxsize should bring you on the safe side with the Linux 2GB oracle limitation.

Collapse
Posted by Malte Sussdorff on
Correction. Oracle recommends 160K, 5120K or 160M for the extent size. You can event think about splitting your objects (tables) among different tablespaces depending their size and the makeup of the tablespace.