Inside Documentum

There is a very useful performance tuning document on the EMC developer site here. The document describes the use of a new Oracle 10g facility called the SQL Tuning Advisor. The SQL Tuning Advisor analyses SQL statements for you and provides tuning recommendations. One of these recommendations could be a SQL Profile which is essentially a set of hints that, hopefully, guide the Oracle cost-based optimiser (CBO) to choose a more appropriate execution plan. SQL Profiles can be a very effective tool to address performance issues with slow database queries however they do have limitations when used with a Documentum system.

Changing Literals

SQL Profiles are applied by the database at query optimisation time. The text of the SQL is compared with any available profiles and if there is a match the hint information contained in the SQL Profile is taken into account by the CBO. Since the SQL needs to be an exact match there are going to be some situations where a SQL Profile is not appropriate. For example the following DQL may well come from some code to display the properties of the object.

select * from dm_relation where parent_id = '09fff99980001117'

Let’s suppose we have been asked to tune the performance of the queries run in this part of the code. When this query is run by Content Server it is transformed to SQL that probably looks like this:

select * from dm_relation_s where parent_id = '09fff99980001117'

Since the literal ‘09fff99980001117′ is likely to be different for every execution of the DQL it won’t be sensible to define a SQL Profile; the SQL will likely keep changing every time the code runs.

Now one possibility to consider is setting the Oracle CURSOR_SHARING parameter to FORCE or SIMILAR (although see Jonathan Lewis’ comment below about the force_match option in the SQL Profiles API). With these setting Oracle will transform all the literals in the SQL query into bind variables so that it looks something like this:

select * from dm_relation_s where parent_id = :"SYS_B_000"

Every execution of the DQL now produces the same SQL and a single SQL Profile would be effective.

dm_sysobject and its subtypes

A similar problem crops up when the query involves a dm_sysobject or sub-type. Consider this query:

select * from dm_document

If user JohnSmith, who is a member of 2 groups (group1 and group2), runs this query the resulting SQL might look like this. In order to enforce ACLs on the object being queried some extra predicates are added to the query. These predicates include literals for the user’s name and the groups they are a member of. So if user DaveJones, a member of 2 different groups (group3 and group4), runs the same DQL then the resulting SQL will look like this. Again the SQL is different and a SQL Profile that tunes the first query will not tune the second one.

The CURSOR_SHARING idea will fix this particular problem but notice that there is a further problem. If users are in different numbers of groups then different SQL is created. This is the typical SQL generated for JohnSmith or DaveJones when CURSOR_SHARING=FORCE. But for a user who is a member of 3 groups the SQL looks like this. Once more the SQL Profile that applies for users in 2 groups will not apply for users in 3 groups; the SQL is different.

You can of course attempt to create SQL Profiles that cover all possible group membership counts (and note this is membership both directly and indirectly of groups). But this strategy could quickly become unmanageable.