What happens when smart guy does not sit back

Previously I posted about performance challenge related to a lot of fetches from docbase, there I have described what makes SysObjFullFetch RPC command extremely slow – a set of useless SQL queries:

-- determine object type by r_object_id
SELECT dm_dbalias_B.I_TYPE
  FROM DMI_OBJECT_TYPE dm_dbalias_B
 WHERE dm_dbalias_B.R_OBJECT_ID = :dmb_handle
 
-- retrieve data from database
  SELECT *
    FROM TYPE_RV dm_dbalias_B, TYPE_SV dm_dbalias_C
   WHERE (    dm_dbalias_C.R_OBJECT_ID = :dmb_handle
          AND dm_dbalias_C.R_OBJECT_ID = dm_dbalias_B.R_OBJECT_ID)
ORDER BY dm_dbalias_B.R_OBJECT_ID, dm_dbalias_B.I_POSITION
 
-- get identifier of object's ACL (totally useless)
SELECT dm_dbalias_B.R_OBJECT_ID
  FROM DM_ACL_S dm_dbalias_B
 WHERE (dm_dbalias_B.OWNER_NAME = :p00 
    AND dm_dbalias_B.OBJECT_NAME = :p01)
 
-- retrieve object's ACL
SELECT *
    FROM DM_ACL_RV dm_dbalias_B, DM_ACL_SV dm_dbalias_C
   WHERE (    dm_dbalias_C.R_OBJECT_ID = :dmb_handle
          AND dm_dbalias_C.R_OBJECT_ID = dm_dbalias_B.R_OBJECT_ID)
ORDER BY dm_dbalias_B.R_OBJECT_ID, dm_dbalias_B.I_POSITION
 
-- check whether ACL is actual or not (totally useless)
SELECT dm_dbalias_B.R_OBJECT_ID
  FROM DM_ACL_S dm_dbalias_B
 WHERE (    dm_dbalias_B.R_OBJECT_ID = :dmb_objectp
        AND dm_dbalias_B.I_VSTAMP = :dmb_versionp)

Let me clarify why I do think so.
This query:

-- get identifier of object's ACL (totally useless)
SELECT dm_dbalias_B.R_OBJECT_ID
  FROM DM_ACL_S dm_dbalias_B
 WHERE (dm_dbalias_B.OWNER_NAME = :p00 
    AND dm_dbalias_B.OBJECT_NAME = :p01)

is completely useless because technically we do not need r_object_id of ACL to retrieve ACL from database in follow-up query:

-- retrieve object's ACL
SELECT *
    FROM DM_ACL_RV dm_dbalias_B, DM_ACL_SV dm_dbalias_C
   WHERE (    dm_dbalias_C.R_OBJECT_ID = :dmb_handle
          AND dm_dbalias_C.R_OBJECT_ID = dm_dbalias_B.R_OBJECT_ID)
ORDER BY dm_dbalias_B.R_OBJECT_ID, dm_dbalias_B.I_POSITION

so, we can replace two queries by the single one:

SELECT *
    FROM DM_ACL_RV dm_dbalias_B, DM_ACL_SV dm_dbalias_C
   WHERE (    dm_dbalias_C.OWNER_NAME = :p00 
          AND dm_dbalias_C.OBJECT_NAME = :p01
          AND dm_dbalias_C.R_OBJECT_ID = dm_dbalias_B.R_OBJECT_ID)
ORDER BY dm_dbalias_B.R_OBJECT_ID, dm_dbalias_B.I_POSITION

Moreover, in some set of cases we do not need to check ACLs at all, these cases are:

  • current user is a superuser or a member of dm_browse_all (dm_read_all, etc) group, or current user is a sysobject’s owner
  • sysobject has r_is_public=T or world_permit>=2 and MACL is turned off:

It had taken about 3 months to force EMC to change something in Content Server to improve performance (frankly speaking EMC employees had no idea about r_is_public and world_permit attributes), and finally we have following notice in patch notes:

Below is a comparison of single thread performance (amount of fetches per 10 seconds) between old and new implementation for case when sysobject has r_is_public=T (I believe that orange graph’s laydown is related to GC settings):

One thought on “What happens when smart guy does not sit back

  1. Pingback: aspects or not aspects… | Documentum in a (nuts)HELL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s