Oracle 11g and Documentum (Part 3)

So how did we go further? By using Oracle Enterprise Manager Grid Control (OEMGC), the queries which cause performance penalties, could be found quickly and so some extra indexes were made. But that wasn’t the only thing we saw during test. We also saw some queries running twice instead of running once. It happens for example by changing from one tab to another. Sometimes a query from the first tab was also run, before the second tab. For this behaviour we had to get support from EMC.

For other queries we need to check further, and came to the problem what Oracle has when FORCE is used for CURSOR_SHARING. The Oracle Optimizer doesn’t always like histograms with the setting of the parameter. After looking at the queries the histograms on the table DM_GROUP_R were the problem. So the statistics on the table were collected without the histogram and then these statistics were locked.

Oracle 11g and Documentum (part2)

By not setting the given parameters for Documentum according to their documentation, we need to be sure the statistics in Oracle should be up-to-date. Beside the normal stats on the tables and indexes, the standard night batch is ok, we need also to be sure that the system statistics are ok. This comes in two ways, with workload and no-workload. For the start no-workload is ok, later as in production, workload can be used on a busy system. So I started with the next statement, performed by SYS:

begin
  dbms_stats.gather_system_stats();
end;

After that some queries which went wrong, went better. So later I will perform the workload statement if we go to acceptance and production. Now back for searching the needed indexes, but I will be sure, our testers will also complain about performance, so it will not hard to find the missing ones.

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.