12 Oracle Content DB Maintenance and Tuning

This chapter provides information about ongoing system maintenance, performance tuning, and recovery. As with any production system, your implementation of Oracle Content DB needs to include a basic disaster recovery plan.

This chapter provides information about the following topics:

Backup and Recovery
Service Configurations and Java Memory Sizing
Performance Tuning
Analyzing Performance Problems

Backup and Recovery

Always back up the system before upgrading, migrating new data, or making other major changes:

Oracle Database tier: See Oracle Database Backup and Recovery User's Guide for complete information about backing up Oracle Database. In addition, note the following:
- In addition to the Oracle Content DB schema, there is a special schema that ensures secure connectivity to other systems, called CONTENT$CM. When you back up your system, make sure to include this schema.
- Make sure to back up the Oracle Workflow schema, OWF_MGR.
- If you use BFILEs, make sure to back them up also.
Oracle Content DB middle tier: There is no backup and recovery tool for the middle tier. To back up the middle tier, make a complete copy of the Oracle home. Also, make a copy of the oraInventory directory on the middle-tier computer.

Service Configurations and Java Memory Sizing

In Oracle Content DB, the default service configurations specify the maximum number of Library sessions that can connect to the service. Restricting the number of Library sessions reduces the likelihood of getting out of memory errors in the OC4J_Content.default_island.1 or in the application.log files.

When the maximum number of Library sessions is restricted, you may see the following errors:

Oracle Content DB Web client: "The maximum number of concurrent sessions has been reached. Please try your request again later."
OC4J_Content.default_island.1 or application.log: "IFS-20127: Service too busy (maximum concurrent sessions)"

If you see either error, modify the node configuration to change the service configuration from small to medium or from medium to large. You can also create a custom service configuration. If you use the large service configuration, or if you create a custom service configuration, you must adjust your -Xmx setting.

If you see java.lang.OutOfMemory errors in your OC4J_Content.default_island.1 or application.log files, then you also need to adjust your -Xmx setting.

Table 12-1 describes factors that might require you to change the -Xmx setting.

Table 12-1 Xmx Settings

Service Configuration	Setting for IFS.SERVICE. Maximum ConcurrentSessions	Expected PCCU	Recommended Size for Xmx (Java Maximum Memory)	Need to change the default Xmx setting of 256MB?
Small	40	25	64 MB	No
Medium	70	45	162 MB	No
Large	200	125	430 MB	Yes

Note:

The term PCCU refers to Peak Concurrent Connected Users. PCCU is the number of users who are signed on to Oracle Content DB and have performed an operation during the peak hour of the day. If you do not know the number of peak hour users, assume 10 percent of your Oracle Content DB user population.

See "Creating Service Configurations" for additional information about creating custom service configurations. See "Modifying Node Configurations" for information about changing service configurations for a node.

Calculating Xmx Settings

A general guideline for calculating the Xmx setting is:

Xmx = PCCU * 2.8MB

Alternatively, you can use the following equation to determine a more precise value:

Xmx = (PCCU * 1.6 sessions per PCCU * 1MB per session) + (DATACACHE.Size * 3KB per data cache object) + (20% JVM overhead for garbage collection)

The maximum value for the Xmx setting depends on your operating system. On Linux operating systems, the setting cannot exceed 2GB. On Solaris operating systems, the setting cannot exceed 4GB. Oracle recommends that the Xmx setting does not exceed 2GB for Oracle Content DB.

See "Adjusting Java Parameters for Nodes" for information about how to change the Xmx setting.

Adjusting Service Configuration Settings

If you expect that your peak concurrent connected users (PCCU) will exceed 125, create a custom service configuration using the following recommendations:

MaximumConcurrentSessions = 1.6 * PCCU DATACACHE.Size = 400 * PCCU DATACACHE.EmergencyTrigger = 0.80 * DATACACHE.Size DATACACHE.UrgentTrigger = 0.75 * DATACACHE.Size DATACACHE.NormalTrigger = 0.65 * DATACACHE.Size DATACACHE.PurgeTarget = 0.55 * DATACACHE.Size CONNECTIONPOOL.WRITEABLE.MaximumSize = 0.05 * PCCU CONNECTIONPOOL.WRITEABLE.TargetSize = 0.04 * PCCU CONNECTIONPOOL.WRITEABLE.MinimumSize = 5 CONNECTIONPOOL.READONLY.MaximumSize = 0.05 * PCCU CONNECTIONPOOL.READONLY.TargetSize = 0.04 * PCCU CONNECTIONPOOL.READONLY.MinimumSize = 5

The other settings in the service configuration generally do not need to be adjusted.

Performance Tuning

Performance is typically affected by network I/O, hard-disk drive I/O, memory (random access memory) I/O, or some combination of these three or other factors. Adjusting one of the factors sometimes moves the performance problem to a new location, so you must approach the tuning task in a logical manner.

In addition to the information provided in the following section, see "Storing Files in an Oracle Database" and "Oracle Content DB Metadata and Infrastructure" for information about how to calculate the appropriate space for document storage.

See Oracle Database Performance Tuning Guide for complete information about performance tuning.

Running the Oracle Content DB analyze.sql Script

Oracle Content DB uses Oracle Database Cost-Based Optimizer (CBO) to determine the most efficient way to run SQL statements. For the CBO to work properly, the Oracle Content DB analyze.sql script needs to be run as part of regular Oracle Content DB operations, especially after large volume changes to the data, such as after users have loaded a large number of files into the database instance. This script generates statistics about the distribution of data in Oracle Content DB so that the CBO can choose the most efficient way to execute SQL statements. For more information about the Cost-Based Optimizer, see Oracle Database Performance Tuning Guide.

Run the script during periods that are not busy to avoid impeding system performance.

The analyze.sql script, which makes calls to the DBMS_STATS package, exports schema statistics to a backup table, so you can restore statistics later, if necessary, as discussed in "Restoring Prior Statistics" in the following section. To run the script, first ensure that a SQL*Plus client has been installed. Then, enter the following at the command line:

cd ORACLE_HOME/content/admin/sql
sqlplus content_db_schema/password@connect_string @analyze.sql content_db_schema

This script may take a while to run, especially if Oracle Content DB contains a large number of documents.

Restoring Prior Statistics

Before gathering new statistics, the analyze.sql script exports backup statistics to the IFS_BACKUP_STATS table, marking the set of statistics with a time stamp. You can query the table for existing saved sets by running this SQL statement:

SQL> select distinct statid from IFS_BACKUP_STATS;

This query returns a list of all statistics by statistic ID (the date and time stamp). For example:

STATID
------------------------------
01-MAY-02 02:15.36
04-MAY-02 20:00.15
08-MAY-02 02:15.48
11-MAY-02 06:21.40
11-MAY-02 20:15.37

You can then restore the statistics from a day and time when you know performance was better. For example, if you find that after using the statistics from the 8:00 p.m. run of the analyze script that performance is worse, then you can restore the statistics from earlier that day using:

SQL> call dbms_stats.import_schema_stats (content_db_schema,
'IFS_BACKUP_STATS', '08-MAY-02 06:21.40',content_db_schema);

By restoring the statistics, you are directing the CBO to revert to the way it previously ran SQL statements.

Analyzing Performance Problems

After ensuring that you have run statistics properly and have enough free hard-disk space to support the tablespaces, you may still have performance problems. If you have performance problems, you must determine whether the performance bottleneck is caused by Oracle Database, Oracle Content DB, or other factors.

To isolate the problem, start looking at which processes are running and how many resources they are using:

Run top (on UNIX) or start the Task Manager (on Windows platforms) as you reproduce the problem.
Determine whether a Java process, the Oracle shadow process, I/O, or a combination is the bottleneck during that time.

If the Database Is Causing the Problem

If the problem is the Oracle shadow process, use the Statspack utility to determine the SQL statement that is causing the largest number of buffer gets, and run Explain Plan on it.

If you see full table scans, then that may be the cause of the problem; the optimizer may not be choosing an appropriate plan. Report that problem to Oracle Support Services. Additional work must be done to isolate the problem.

For more information about the Statspack utility and Explain Plan, see Oracle Database Performance Tuning Guide.

If the Java Processes Are Causing the Problem

You may not have enough memory. For example, if you see any java.lang.OutOfMemoryError errors in your logs, increase your maximum memory (Xmx) settings for that JVM. See "Modifying Node Configurations" for more information about changing the Xmx setting.

If users are experiencing poor response times, and top (on UNIX) or its equivalent (for example, Task Manager on Windows platforms), shows a Java process running at 100 percent of a CPU for a minute or longer, then the Xmx setting for Java may be too small.

Turn on verbose garbage collection (verbosegc). To do this, edit the Java Parameters of the node configuration. See "Adjusting Java Parameters for Nodes" for more information.

In the node log file, output related to garbage collection appears as follows:
```
[Full GC 1476K->1476K(2112K), 0.0549430 secs]
```
A Full GC occurs when the Garbage Collector has exhausted all available memory in the nursery, and has to go into the rest of the heap to reclaim memory.
If Full GCs occur more than once every 10 minutes (not just after startup), increase your Xmx settings for that JVM.

Viewing Cache Statistics and Changing Cache Settings

If the problem is an Oracle Content DB Java process, start by checking the percentage of cache hits for the Oracle Content DB service using the Application Server Control, as follows:

Connect to the Application Server Control and go to the Content DB Home page. See "Accessing the Oracle Content DB Home Page" for information about how to do this.
Click the name of the service (for example, IfsDefaultService). The Service page appears.
Click the Performance tab.
In the Committed Data Cache Statistics section, you can view real-time data for Cache Size, Cache Puts, Cache Removes, Cache Purges, Cache Purge Cycles, Cache Lookups, and Cache Hits.

The goal is to have a high percentage of Cache Hits; as much as 100 percent is possible. If the percentage of Cache Hits for the service is less than 98 percent, the size of the Committed Data Cache may be too small.

Because the Statistics Agent captures the real-time data, you can also see prior statistics by viewing the node log or application log. You can also configure this agent to write statistics to a document stored in the Oracle Content DB repository. See "Statistics Agent" for information about the Statistics Agent.
To change the run-time Cache settings, click the Administration tab.
In the Data Cache table row, click the Go to Task icon.
Proportionately increase all Cache settings (Cache Capacity, Normal Purge Trigger, Urgent Purge Trigger, Emergency Purge Trigger, Purge Target) and click Apply.

This will increase your memory usage on the middle tier computer by approximately 3 KB for each object. For example, if you increase cache capacity by 5000, your memory usage will grow by 15 MB.

To make the changes permanent, update the service configuration. See "Modifying Service Configurations" for more information.

Viewing Connection Pool Statistics and Changing Connection Pool Settings

Check the target and maximum number of connections for the Read-Only and Writeable Connection Pools using the Application Server Control, as follows:

Connect to the Application Server Control and go to the Content DB Home page. See "Accessing the Oracle Content DB Home Page" for information about how to do this.
Click the name of the service (for example, IfsDefaultService). The Service page appears.
Click the Performance tab.
Look at the statistics in the Read-Only and Writable Connection Pool Statistics sections.

You will need to increase the Target Maximum Number of Connections and Absolute Maximum Number of Connections if any of the following is true:
- Failed Allocations is greater than zero.
- Total Connections is more than two higher than Target Maximum Number of Connections.
- Deferred Allocations is greater than 5 percent, and Average Allocation Time (ms) is more than 10 milliseconds.
Because the Statistics Agent captures the real-time data, you can also see prior statistics by viewing the node log or application log. You can also configure this agent to write statistics to a document stored in the Oracle Content DB repository. See "Statistics Agent" for information about the Statistics Agent.
To change the run-time Connection Pool settings, click the Administration tab.
In the Read-Only Connection Pool or Writable Connection Pool table row, click the Go to Task icon.
Increase the Target Maximum Number of Connections and Absolute Maximum Number of Connections, and click Apply.

Each additional Target or Absolute connection will use approximately 8 MB for each connection on the middle tier and 1 MB for each connection on the database.

To make the changes permanent, update the service configuration. See "Modifying Service Configurations" for more information.