Running queries in IAPI

August 29, 2007 at 11:16 am | Posted in Troubleshooting | 3 Comments

Just noticed this post in the EMC Forums. The user wanted to know how to run a query in IAPI. Many of the answers talk about using execquery and readquery and so on which is all well and good, however there is a really nifty IAPI command that can run the query and display the results. If you read this blog regularly you will notice I use it a lot.

Using the ‘?’ command will run a query e.g.

?,c,select r_object_id,r_version_label from dm_document where object_name = 'template 1'

produces output like this:

r_object_id       r_version_label
----------------  --------------------------------
0900022b80003bfd  CURRENT
(1 row affected)

no need for next, get and close commands, it’s all done for you by IAPI.

DQL Hints – Optimize Top

August 14, 2007 at 11:30 am | Posted in Documentum and Oracle, Performance | 5 Comments

DQL Hints are a very useful feature brought in by Documentum 5 that has been incrementally improved from 5.1 through 5.3. No doubt we will have some more in Documentum 6. DQL hints usually translate into a SQL level hint when Content Server transforms DQL into a SQL statement. For example when the database is Oracle the SQL level hint will look some thing like this:

select /*+ hint */ object_name from dm_sysobject_s

The hint is added after the ‘select’ keyword between the /*+ and */ tokens.

There is a DQL Hint called optimize top which might produce a performance boost when returning the first few records from a potentially large result set. According to the DQL documentation this translates to an ‘optimize top’ hint in Oracle. This got me scratching my head as I didn’t recall such a hint in Oracle. A quick google confirmed that this is not a recognised Oracle hint so the quickest way of finding out is to check the actual SQL produced by documentum. As I’ve pointed out before there are 2 ways to do this, either call ‘execute get_last_sql’ after you have run a DQL or use the Documentum Administrator ‘show SQL’ option (which does the same thing on your behalf). What I found was that ‘optimize top’ actually translates to a ‘first_rows’ hint in Oracle.

The idea of first_rows is that the Cost-based Optimizer (CBO) should choose a query execution plan that retrieves the first rows quickly even if the overall cost to retrieve all rows is greater than other query plans. This is potentially a good idea for many queries that are executed in ‘paged’ query scenarios. A paged query occurs for example when viewing a large folder in Webtop. The query to return all the documents is run but only the first 50 or 100 rows are initially retrieved to build the first few pages. If the user then selects the ‘next’ page the next 50 or 100 rows are retrieved.

first_rows is actually a deprecated hint in Oracle. Since Oracle 9 the preferred hint is first_rows_n where n=the number of rows to optimise for. So for example first_rows_10 will optimise for returning the first 10 rows quickly. If you want to use this hint from DQL then you would have to use ‘pass-through’ hints:

select * from dm_document enable(oracle('first_rows_10'))

The Art of Troubleshooting part deux

August 7, 2007 at 8:50 pm | Posted in Troubleshooting | Leave a comment

Jonathan Lewis blogged about another way of looking at troubleshooting. Whilst much of Jonathan’s blog is deeply technical he also has the ability to translate a rather dry subject into a colourful and memorable metaphor.

If you are in the mood for light-hearted reading then you could try another of Lewis’ more colourful posts. According to my wife if you find that funny you’re a geek. That’s a good thing in my book.

The Art of Troubleshooting

August 6, 2007 at 11:32 am | Posted in Troubleshooting | Leave a comment

Laurence has a neat trick to solve an LDAP Synchronization problem. I’ve used this a few times myself in the past. The really interesting thing about the post is it’s a good illustration of how to go about troubleshooting a problem. I suspect I went through a similar process when I discovered the trick myself.

It’s not just about the technical details but about the attitude you take in solving the problem. First of all you start of with a general understanding of how Documentum implements jobs, you know what is being called and when. As a result of reading round the problem (docs, forums, blogs, etc) you have an idea of what structures are possibly being updated. You understand in broad terms how the code is doing the job it is supposed to do. From all this you are usually left with a number of possibilities as to why you get the results you see. A little testing narrows it down to (hopefully) just one thing.

Finally it’s good to see someone discuss the issue of when and where you can test out theories about how the system works. Usually you need to do this in an isolated, controlled setup; to avoid messing up the production system for real users and also to ensure that tests are controlled and repeatable. But as Laurence points out removing a user wasn’t a big deal if that user couldn’t access the system in the first place, but you still need to be careful.

What they don’t tell you about Oracle SQL Profiles

August 4, 2007 at 4:38 pm | Posted in Performance | 4 Comments

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.

Blog at
Entries and comments feeds.