Task Manager+

February 20, 2007 at 10:29 pm | Posted in Performance, Troubleshooting | 1 Comment

I work with Documentum on Windows a lot and one of the indispensible tools is Task Manager. From monitoring CPU and memory usage, to checking whether dm_agent_exec is running I wouldn’t be without it. However recently I have been using something even better: Process Explorer from sysinternals.

Process Explorer is like Task Manager on rocket fuel. It has all the standard functionality of Task Manager, like the performance stats dashboard view and the list of processes that can be ordered by CPU usage. However it has loads of extra features. One I particularly like is the tree-view of processes that shows parent-child relationship; the children are the processes that have been started by the parent.

In the screenshot below it is easy to trace which docbasic method servers (mthdsvr.exe) belongs to which content server (dmserver_v4.exe).

Process Explorer

Something else that is incredibly useful is the ability to view the command line a particular process was invoked with. Recently I had to track down a performance problem where 1 content server out of several was hogging all the CPU. Since the docbase name is embedded in the command line it was easy to double-click on the dmserver_v4.exe process that was at the top of the CPU usage list and select the image tab. There are other ways but this was the quickest and easiest.

Since sysinternals was bought by Microsoft at the end of last year you can now get this utility (and all the other sysinternals favourites like regmon and filemon) from here

Content Server and DMCL

February 19, 2007 at 2:36 pm | Posted in Architecture, Troubleshooting | 4 Comments

The poor old Documentum Client Library (DMCL) gets blamed for a lot of things. The suggestion to delete the dmcl cache to avoid a multitude of problems has now entered the realm of myth. I’m sure that for many problems this is the correct solution. However, as you will need to shutdown the application using DMCL before deleting the cache, in many cases the simple act of restarting the application may have resolved the problem anyway. Still, since you can never be sure, a quick delete of the cache is no big deal if you are going to restart an application anyway.

However the finger of blame sometimes gets pointed at the DMCL even when it is unlikely to have any connection with the problem in hand. An example is internal processing failures of the Content Server process. I have seen people (including Documentum support) suggest dmcl cache clearing even for problems that arise inside Content Server processes. One of the keys to troubleshooting is using an understanding of how applications are architected to rule out certain explanations for the error.

The figure below shows a typical Documentum technology stack. This particular example is for a web browser accessing WebPublisher but you will see a similar picture for other client applications. The key here is that DMCL is a client library. Content Server exposes all its functionality via a set of (undocumented) server functions that are accessed via a Remote Procedure Call (RPC).

Documentum Technology Stack

The DMCL is a linked library inside the client application – a DLL in windows, shared library in Unix. The DMCL takes documented API calls made by the client application and maps them to Server RPC functions that can be executed by the Content Server. Once the call has arrived inside the Content Server process the processing is no longer affected by the DMCL environment. In fact the DMCL thread that made the call will simply be sleeping awaiting the results of the processing from the server.

The really confusing part is that DMCL is included with the Content Server installation. The reason is that much of the functionality that is associated with the Content Server is actually implemented in external methods. In these cases the external method is a client of the Content Server and will utilise DMCL (via the API) to do its work. So really you need to be clear where the error is originating from. Does it arise from the external method code or does it arise from processing inside the content server?

Registering a table in another database

February 15, 2007 at 6:06 pm | Posted in Performance | Leave a comment

Well I could have answered this question here but Fabian Lee has produced the definitive answer here. You’ll need a login to the Documentum Support Site to access the post so if you have any problems let me know and I’ll ask Fabian to make the information available for general viewing.

Magic SANs

February 7, 2007 at 10:31 am | Posted in Architecture, Documentum and Oracle, Performance | Leave a comment

There is an excellent piece here about some of the choices to be made in configuring SANs for your application. The key point is that if your application, or part of it, has performance that is constrained by disk I/O then you should care about how that magic box of tricks is configured. This consideration implies that you should know something about the level of disk I/O your application requires!

In a similar vein I recall reading a presentation (possibly by Anjo Kolk) about the typical Docbase Admin/DBA conversation with the SAN administrator, something like:

DBAdmin: We need to setup a new docbase and database
SAN admin: Ok how much space do you need?
DBAdmin: 50Gb for the database, 1TB for the docbase
SAN admin: OK you can access the space here …

Not a mention of how many I/Os per second the application will require. Don’t be afraid to ask these questions.

By the way SAME stands for Stripe and Mirror Everywhere, an Oracle approach to fully utilising disk resources. See this document, many of the disk performance details are not specific to Oracle.

What SQL is being used?

February 2, 2007 at 10:04 am | Posted in Documentum and Oracle, Performance, Troubleshooting | Leave a comment

What SQL is generated by the following DQL query:

select object_name from dm_document

Well the answer depends on a number of things:

  • What user I am
  • What version of Content Server I’m using
  • Certain server.ini parameters
  • etc

The most important one is the first one, and particularly whether the user is a superuser or not. If I am a superuser then the query will look something like this:

select     all dm_document.object_name 
from       dm_document_sp dm_document 
where    (    dm_document.i_has_folder = 1 
             and dm_document.i_is_deleted = 0)

But if I don’t have superuser privileges it will look something like this:

select     all dm_document.object_name 
from       dm_document_sp dm_document 
where      (    dm_document.i_has_folder = 1 
            and dm_document.i_is_deleted = 0) 
and        (  (   dm_document.owner_name = 'Robin East' 
               or dm_document.owner_name in ('administrator')
            or exists (select    * 
                       from      dm_acl_sp, 
                       where     dm_acl_sp.r_object_id = dm_acl_rp.r_object_id 
                       and       dm_document.acl_domain = dm_acl_sp.owner_name 
                       and       dm_document.acl_name = dm_acl_sp.object_name 
                       and       dm_acl_rp.r_accessor_permit >= 2 
                       and       (    dm_acl_rp.r_accessor_name = 'dm_world' 
                                   or (    dm_document.owner_name = 'Robin East' 
                                       and dm_acl_rp.r_accessor_name = 'dm_owner'
                                   or dm_acl_rp.r_accessor_name = 'Robin East' 
                                   or (    dm_acl_rp.r_is_group = 1 
                                       and dm_acl_rp.r_accessor_name in ('administrator')

All that extra stuff results in joins or subqueries on the dm_acl tables to enforce permissions; superusers always have read access to everything so there is no need for the additional predicates.

The upshot is: be very careful when tuning DQL queries by turning them into SQL. If you have a DQL that is slow for a non-superuser and you log in to Administrator as dmadmin to identify the SQL being generated you will not be using the same SQL!!!

Here are some ways to make sure you are getting the right SQL:

  1. login to DA as the user – you’ll need her password! Now you can run the query and use ‘Show SQL’ to get the right SQL
  2. If you know how, run the API trace,c,10,,SQL_TRACE from her session. In WDK apps you can do this by accessing the API tester component. All SQL statements generated for her session will appear in the user’s session log on the Content Server
  3. The following API commands can be run by a superuser in IAPI:

    getlogin,c,user (this returns a ticket for the user)
    run query
    ?,c,execute get_last_sql

  4. execute get_last_sql will give you the SQL for the DQL command just run, this is the command that is run when you click ‘Show SQL’ in the DA query interface.

dm_UpdateStats vs DBMS_STATS

February 2, 2007 at 8:33 am | Posted in Documentum and Oracle, Performance | Leave a comment

A comment on one of my previous posts asked whether dm_UpdateStats should be used if automatic statistic collection is enabled in Oracle 10g. I discuss the answer I gave in more detail here.

All the current databases that run Content Server use a cost-based optimizer to decide how a query should be executed. A key input into that decision is statistics the database stores about the data in tables and indexes. Clearly there is some benefit in making sure that the statistical information is up-to-date.

This can be overstated sometimes (I’ve known people who routinely run update stats before they attempt to examine any sort of problem) but whenever you have a performance problem that can be traced to the database the first thing to check is whether statistics have been created on the tables involved.

In Oracle versions 9 and 10 there are 2 different methods of collecting statistics:

  • DBMS_STATS package

The DBMS_STATS package is Oracle’s recommended way of collecting stats; ANALYZE TABLE … COMPUTE STATISTICS is retained for backward compatibility. Eventually DBMS_STATS will be the only supported way of collecting statistics. But bear in mind even though ANALYZE TABLE is still officially tolerated by Oracle the statistics it compiles are likely to be different from DBMS_STATS. It is possible that certain new features in the CBO (e.g. new or improved query execution methods) may not be used when ANALYZE TABLE statistics have been created.

So what does dm_UpdateStats use? ANALYZE TABLE of course. The code for dm_UpdateStats was written sometime ago and has not been seriously updated since. When I spoke to Ed Bueche at Momentum last year he indicated that the switch to DBMS_STATS would have to be done sometime, just not yet.

This is what dm_UpdateStats actually does:

  1. A list of ANALYZE TABLE commands is maintained in the table dm_update_stats_commands
  2. When dm_UpdateStats runs it refreshes the tables using the query:
    CREATE TABLE dm_udpate_stats_commands AS
    SELECT 'analyze table ' || table_name || ' compute statistics' AS commands FROM user_tables
  3. Since the table user_tables contains a list of all tables in the docbase owner’s schema the dm_update_stats_commands table contains a row-by-row list of the ANALYZE TABLE commands that must be run for all tables in the schema. The dm_UpdateStats code simply runs through this row, executing each command using the execsql API command.
  4. For good measure there is a set of additional ANALYZE statements in the script custom_oracle_stat.sql located in the directory %DOCUMENTUM%\dba\config\. These additional ANALYZE statements contain ANALYZE TABLE ... COMPUTE STATISTICS FOR COLUMNS ... statements that add histograms to certain columns. If you want to add additional histograms or alter the existing ones this is the place to add your customisations. I’ll be saying a lot more about histograms in a future posting.

So to wrap up:

  • It is a good idea to ensure statistics have been created on your tables. For most installations running dm_UpdateStats once a week works well
  • If your DBA is already running a regular job using DBMS_STATS then don’t run dm_UpdateStats (inactivate the job)

Blog at WordPress.com.
Entries and comments feeds.