DM_GROUP_LIST_LIMIT_TEMP_TBL vs Oracle Database

Content Server recognizes three environment variables which control the manner of ACL checks in DQL queries, these variables are:

DM_GROUP_LIST_LIMIT – sets the upper limit (default is 250) of groups, user belongs to, after which CS performs ACL checks using following manner:

AND ((EXISTS
   (SELECT 1
    FROM dm_acl_s ACL_S0, dm_acl_r ACL_R
   WHERE ACL_S0.r_object_id = ACL_R.r_object_id
     AND dm_folder.acl_domain = ACL_S0.owner_name
     AND dm_folder.acl_name = ACL_S0.object_name
     AND (( ACL_R.r_accessor_name IN
         ('dmadmin', 'dm_world')
       OR (ACL_R.r_is_group = 1
         AND (EXISTS
           (SELECT 1
            FROM dm_group_r gr1, dm_group_r gr2
           WHERE gr1.i_nondyn_supergroups_names =
              ACL_R.r_accessor_name
             AND gr1.r_object_id =
                gr2.r_object_id
             AND gr2.users_names = 'dmadmin'
             AND gr1.i_nondyn_supergroups_names
                IS NOT NULL
            UNION ALL
            SELECT 1
            FROM dm_group_r gr1, dm_group_r gr2
           WHERE gr1.i_nondyn_supergroups_names =
              ACL_R.r_accessor_name
             AND gr1.r_object_id =
                gr2.r_object_id
             AND gr2.groups_names =
                'dm_world'
             AND gr1.i_nondyn_supergroups_names
                IS NOT NULL)))
       OR (ACL_R.r_accessor_name = 'dm_owner'))
        AND (( ACL_R.r_permit_type = 0
          OR ACL_R.r_permit_type IS NULL)
         AND (((ACL_R.r_accessor_permit >= 6))))))))

DM_GROUP_LIST_LIMIT_TEMP_TBL – if set to T, CS stores user’s groups in “temporary” (“temporary” here does not mean true temporary tables, but regular tables which persist in database during some period of time) table and performs security checks using following manner:

AND ((EXISTS
   (SELECT 1
    FROM dm_acl_s ACL_S0, dm_acl_r ACL_R
   WHERE ACL_S0.r_object_id = ACL_R.r_object_id
     AND dm_folder.acl_domain = ACL_S0.owner_name
     AND dm_folder.acl_name = ACL_S0.object_name
     AND (( ACL_R.r_accessor_name IN
         ('dmadmin', 'dm_world')
       OR (ACL_R.r_is_group = 1
         AND (EXISTS
           (SELECT 1
            FROM dmdql80112100000
           WHERE ACL_R.r_accessor_name =
              group_name)))
       OR (ACL_R.r_accessor_name = 'dm_owner'))
        AND (( ACL_R.r_permit_type = 0
          OR ACL_R.r_permit_type IS NULL)
         AND (((ACL_R.r_accessor_permit >= 6))))))))

DM_LEFT_OUTER_JOIN_FOR_ACL – seems does not have effect for Oracle docbases, so I don’t know how CS performs ACL checks in this case.

The problem is when DM_GROUP_LIST_LIMIT_TEMP_TBL is in effect, Content Server permanently creates and drops “temporary” tables, but due to recycle bin feature introduced in Oracle 10g, oracle does not really drop those temporary tables but moves them to recycle bin which leads to the following performance impact: when Oracle runs out of free space it tries to reclaim space by purging objects from recycle bin and database inserts get stuck on “enq: CR – block range reuse ckpt”. So, if you are going to use DM_GROUP_LIST_LIMIT_TEMP_TBL feature disable recycle bin in Oracle to avoid performance troubles.