Oracle 11g and Documentum

In my current project my task is to migrate data from Oracle to a new Documentum environment. Beside they also asked me to keep baby sitting on the Oracle database lying under the Content Server of Documentum. The install was done by following the installation guide from Documentum. That is not that much for Oracle, just install and set some parameters. After that run some scripts and Oracle is ready to receive Documentum.

I looked at the database and saw those parameters:

  1. optimizer_index_cost_adj was 5
  2. optimizer_index_caching was 95
  3. optimizer_mode was choose

After some investigation I found out that these setting are common for a Documentum install in Oracle. It has been the best practice for more than 10 years and Documentum works this way. I made the next adjustments to the database, because the above settings come from before oracle 10g, which will be the standards for the next installation for Oracle for Documentum.

  1. optimizer_index_cost_adj back to 100
  2. optimizer_index_caching back to 0
  3. optimizer_mode to ALL_ROWS

Furthermore I set CURSOR_SHARING to FORCE. There is no way in the translation from DQL (The SQL language of Documentum) to SQL in which Documentum uses Bind Variables. So they made the variables hard-coded. So I made the decision to set CURSOR_SHARING. I tested also Similar, but then some queries went mad, mostly inserts and updates. So the safest setting is FORCE. Similar will also be desupported by Oracle in 11.2.

By default installation Documentum makes 2 tablespaces, one for data and one for indexes. But the default gives these a max size of 2GB. So after the first tests in the test environment, the tablespace of data became full. This is mostly due to the heavy auditing. This auditing happens in 1 table, DM_AUDITTRAIL_S. I made some adjustments by making another tablespace and put the table and indexes into it. This way the table can be easily monitored, by monitoring the tablespace.

The last thing what I now need to do, is monitoring the database for peaks. This is because Documentum can make new objects (which will lead to new tables), but will not make the indexes. So it is to the developer or the DBA to find these. They put me on this task. I keep monitoring and use the OEM for this. A look at the performance tab for a peak and also some jobs to find simple FULL TABLE scans. So some work to do.

Advertisements

2 Responses to “Oracle 11g and Documentum”

  1. David Bailey Says:

    I’ve found the opposite to be true in terms of query performance in 10G with respect to the optimizer parameters you referenced – 5/95 can make a significant improvement in the elapsed time for queries over 100/0

  2. htendam Says:

    I have no experience with 10g. But now with 11g the default parameters are running smoothly on a 11g database. The only thing I can comment extra is on some tables it is the best to get statitics without Histograms, on some tables even the performance goes up without any statistics; these tables are the one used for workflows.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: