Tuning an Oracle database

This section briefly describes the tools that are available to tune the Content Manager OnDemand database when using Oracle. For more detailed information, see your Oracle technical product information.

In general, most customers use Content Manager OnDemand in one of two ways:
  • Long-term archive for larger reports. These customers create table spaces for each application group that they add to the system, load many rows into the database at one time, maintain data on the system for many months or years, and delete a report at a time from the system. These parameters usually result in very static data and a low maintenance operation.
    • A table space contains data from one application group.
    • Inserts are done by a high-volume batch process.
    • After a table reaches its Maximum Rows value, OnDemand closes the table and no additional inserts are made to the table. Closed tables remain available for queries until the data is removed from the system.
    • Data is removed from the system by dropping a table or deleting a large number of consecutive rows at a time.
    Because of the low rate of change within the tables, these customers should seldom or never need to tune their database.
  • Short-term archive for smaller reports. These customers store all application group data in the SYSTEM table space (or in one or more DATA table spaces), load very few rows into the database at one time, maintain data on the system for a short period of time, or use the Delete Document method to remove data from the system. These parameters usually result in very dynamic data and a high maintenance operation.
    • A table space contains data from more than one application group.
    • Inserts are done by a low-volume batch process.
    • Inserts and deletes happen frequently.
    • Data is removed from the system by deleting one row at a time.
    Because of the high rate of change within the tables, these customers should plan to tune their database on a time-based schedule, such as a weekly or nightly process. Note: Some customers tune their database every day. However, most customers do not tune their database until the optimizer ignores the current set of statistics or generates an inefficient plan. Those customers should tune the database just before they plan to take an offline backup of the database. This schedule keeps the optimization information up to date and minimizes the impact to system availability, because a system outage is already planned.

Tuning the database is done by collecting statistics on the tables, which can provide faster access to the data, thereby improving performance. Statistics on tables are gathered by using the ANALYZE command. When you analyze a table, its associated indexes are automatically analyzed as well. The frequency with which you analyze the tables depends on the rate of change within the tables. Note: If you collect statistics and do not notice a visible performance improvement, then dropping and re-creating the indexes to your tables may help. Customers in a high maintenance operation may need to periodically rebuild the indexes on their most active tables. See your Oracle information for details about rebuilding indexes.

Content Manager OnDemand provides two programs to collect statistics on database tables: the ARSDB program and the ARSMAINT program.

Using the ARSDB program

You can use the ARSDB program to collect statistics on the Content Manager OnDemand system tables. The Content Manager OnDemand system tables include the user table, the group table, and the application group table. For a complete list of the Content Manager OnDemand system tables, see System Control Tables. For most customers, the Content Manager OnDemand system tables require very little maintenance. You can probably schedule the ARSDB program to collect statistics once a month (or less often).

The syntax is: 
   /opt/IBM/ondemand/V10.5/bin/arsdb <options>
The options are:
–e
Drop configuration indexes
–r
Create configuration indexes
–s
Collect statistics

Using the ARSMAINT program

You can use the ARSMAINT program to maintain the tables that contain user-defined application group data. User-defined application groups are the application groups that you define to the system. Customers in a high maintenance operation should run the ARSMAINT program on a regular schedule.

The syntax is: 
    /opt/IBM/ondemand/V10.5/bin/arsmaint <options>
The options are:
–d, –i
Expire index data from the database. The –i parameter expires index data that has been imported from archive storage. If you do not migrate index data to archive storage, then you do not need to specify the –i parameter.
–e
Migrate index data from the database to archive storage. If you do not migrate index data to archive storage, then you do not need to specify the –e parameter.
–r
Collect statistics. Note: The ARSMAINT program collects statistics only on the tables that have changed since the last time that statistics were collected. OnDemand keeps information about all of its tables, including the last time that it modified a table and the last time that it collected statistics on a table.
–g applGroup
Process the tables for the specified application group. If you do not specify this parameter and name an application group, then the ARSMAINT program processes all of the user-defined application groups.

Recommended practice for managing table space for Oracle

The following list describes the advantage and disadvantage to using one table per table space if you use Oracle for the Content Manager OnDemand database:
  • Advantage: Having one table per table space enables you to move tables among volumes easily. To move tables among volumes, you should take the table space offline, move the table space's datafile to a new location, rename the datafile in the database, and then bring the table space back online. You might also notice a small performance improvement when each table has its own table space.
  • Disadvantage: Having one table per table space requires more frequent updates to the ars.dbfs file. Also, if each OnDemand application group has its own data table, after a new application group is created, a new application group data table and an associated table space are created.
Therefore, in most cases, it is recommended that you bundle the table space into one single logical unit and spread that logical unit over as many devices as possible by using striping or a logical volume manager. This method provides equal or faster performance than manually separating table spaces. However, with one table per tablespace, other DBA tasks, especially monitoring the growth of table spaces, can become more difficult, because there are more table spaces.
Note: Oracle allows a maximum of 1023 data files per tablespace. If your operating system allows a maximum size of 2 GB per file, the maximum size for a tablespace is 2 TB (terabytes) minus 2 GB. For example, if you want to add 2 GB to tablespace "ROOT_DBA12", use this command: ALTER TABLESPACE "ROOT_DBA12" ADD DATAFILE '/a099' SIZE 2048M;
Also, you can resize a datafile if you use Oracle, for example: 
ALTER DATABASE foo DATAFILE '/mydatafile' resize 2048M;