CS: Performance

When you have a large data set in the repository, the object_name clause (‘and object_name=’) in the folder query generated by the IDfSession.getFolderByPath API produces very slow SQL on Content Server.

The large number and depth of folders leads to poor execution performance of folder query SQL.

Configure DFC by copying the following two properties from dfcfull.properties to dfc.properties and set appropriate values:

  • dfc.query.object_name_for_docbase: Name of the repository in which object_name clause needs to be included or not in the folder query present in IDfSession.getFolderByPath API.

    Specify the repository you are working with.

  • dfc.query.should_include_object_name: This value is used when running a folder query through the IDfSession.getFolderByPath API. It determines whether to include or not object_name clause in the query for the docbase specified in dfc.query.object_name_docbase. If it is explicitly mentioned as false, the object_name clause will not be included in the query for the docbase name that is mentioned in dfc.query.object_name_docbase.
    Default value is true.

    Set it to false to alleviate performance.

If there are multiple docbases to which you want to disable this object_name qualifier, you have to enter it in the following way in dfc.properties:


CS: enable SQL Trace

Commands to Enable / Disable sqltrace for a particular user:
apply,c,NULL,SET_OPTIONS,OPTION,S,sqltrace,VALUE,B,T,USERNAME,S,<user name>
apply,c,NULL,SET_OPTIONS,OPTION,S,sqltrace,VALUE,B,F,USERNAME,S,<user name>

Commands to Enable / Disable sqltrace for a particular session:
apply,c,NULL,SET_OPTIONS,OPTION,S,sqltrace,VALUE,B,T,SESSION_ID,S,<session id>
apply,c,NULL,SET_OPTIONS,OPTION,S,sqltrace,VALUE,B,F,SESSION_ID,S,<session id>

Example: steps performed to enable sqltrace for a user <testuser1>
Connected to IAPI via installation owner and fired below statement:

Later connected to IDQL as testuser1 and ran the following DQL:
select r_object_id, object_name from dm_document where r_creation_date >= date(today);

Later connected to IAPI as installation owner and turned off sqltrace

DCTM – Get user permissions in ACL

below query gives the result


select * from dm_user where
user_name in
select distinct(i_all_users_names) from dm_group where group_name in
select r_accessor_name from dm_acl where object_name = ‘<ACL_NAME>’ and r_accessor_permit >= 4
user_name in
select r_accessor_name from dm_acl where object_name = ‘<ACL_NAME>’ and r_accessor_permit >= 4
and user_name = ‘<USER_NAME>’


source: https://community.emc.com/thread/196461

Also can use

execute check_security with username=’username’, level=x, object_list=’listofobjectids’ with groupname=’groupnamehere’ for groups

Oracle Tuning

  2. Use ALL_ROWS (default value for OPTIMIZER_MODE
    1. As a rule of thumb, PROCESSES should be set to the double of the sum of maximum concurrent sessions of all content server instances.
    2. For example, if there are two instances each with concurrent_sessions set to 250, PROCESSES should be set to 2000 (2x2x250).
  4. Keep statistics up-to-date
    1. Oracle statistics are used by the query optimizer to choose the best execution plan for each SQL statement. A worse execution plan can be chosen with out-of-date statistics. To make sure the statistics are up-to-date, the following command can be executed in SQLPLUS as SYSDBA:
    2. exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'<repository>’, options=>’GATHER’, estimate_percent=><percentage, 30 is recommended>, method_opt=>’FOR ALL COLUMNS SIZE AUTO’, degree=><cpu cores>);