Slowness to view the documents in Webtop 6.5 for perticular group

Hi,

We are facing slowness to view the documents in Webtop 6.5 for perticular group. We have used a common DQL to display the documents to all the groups. But for a periticular group it is taking more than 10 mins to display the documents, for other groups it took less than 1 min. We checked the volume of the documents in the problematic group and it seems it is not more than 1000.

We further checked the sql for the DQL we used in webtop and found.

For problematic group it is generating below SQL.

select all :"SYS_B_00", upper(mn_object.object_name), mn_object.r_object_id, mn_object.object_name, mn_object.r_object_type "r_object_type", mn_object.r_lock_owner, mn_object.owner_name, mn_object.r_link_cnt, mn_object.r_is_virtual_doc, mn_object.r_content_size, mn_object.a_content_type, mn_object.i_is_reference, mn_object.r_assembled_from_id, mn_object.r_has_frzn_assembly, mn_object.a_compound_architecture, mn_object.i_is_replica, mn_object.r_policy_id, mn_object.r_modify_date, :"SYS_B_01" "isfolder"
from mn_object_sp mn_object
where ( not (mn_object.r_object_id in
(select dm_sysobject_r2.r_object_id
from dm_sysobject_r dm_sysobject_r2, dm_folder_r dm_folder_r1
where dm_sysobject_r2.i_folder_id = dm_folder_r1.r_object_id and dm_folder_r1.i_ancestor_id = :"SYS_B_02") ) and mn_object.r_object_type in (:"SYS_B_03", :"SYS_B_04", :"SYS_B_05", :"SYS_B_06", :"SYS_B_07", :"SYS_B_08", :"SYS_B_09", :"SYS_B_10", :"SYS_B_11", :"SYS_B_12") and exists
(select r_object_id
from dm_sysobject_r
where mn_object.r_object_id = r_object_id and r_version_label in (:"SYS_B_13", :"SYS_B_14")) and (mn_object.a_is_hidden=:"SYS_B_15") and (mn_object.i_latest_flag=:"SYS_B_16")) and mn_object.i_is_deleted = :"SYS_B_17" and ( ( mn_object.owner_name in (:"SYS_B_18",:"SYS_B_19",:"SYS_B_20",:"SYS_B_21",:"SYS_B_22",:"SYS_B_23",:"SYS_B_24",:"SYS_B_25",:"SYS_B_26",:"SYS_B_27",:"SYS_B_28",:"SYS_B_29",:"SYS_B_30",:"SYS_B_31",:"SYS_B_32",:"SYS_B_33",:"SYS_B_34",:"SYS_B_35",:"SYS_B_36",:"SYS_B_37",:"SYS_B_38",:"SYS_B_39",:"SYS_B_40",:"SYS_B_41",:"SYS_B_42",:"SYS_B_43",:"SYS_B_44")) or (exists
(select :"SYS_B_45"
from dm_acl_s ACL_S0, dm_acl_r ACL_R
where ACL_S0.r_object_id = ACL_R.r_object_id and mn_object.acl_domain = ACL_S0.owner_name and mn_object.acl_name = ACL_S0.object_name and ((ACL_R.r_accessor_name in (:"SYS_B_46",:"SYS_B_47") or (ACL_R.r_is_group = :"SYS_B_48" and (ACL_R.r_accessor_name in (:"SYS_B_49",:"SYS_B_50",:"SYS_B_51",:"SYS_B_52",:"SYS_B_53",:"SYS_B_54",:"SYS_B_55",:"SYS_B_56",:"SYS_B_57",:"SYS_B_58",:"SYS_B_59",:"SYS_B_60",:"SYS_B_61",:"SYS_B_62",:"SYS_B_63",:"SYS_B_64",:"SYS_B_65",:"SYS_B_66",:"SYS_B_67",:"SYS_B_68",:"SYS_B_69",:"SYS_B_70",:"SYS_B_71",:"SYS_B_72",:"SYS_B_73",:"SYS_B_74",:"SYS_B_75")))) and ((ACL_R.r_permit_type = :"SYS_B_76" or ACL_R.r_permit_type is null) and (((ACL_R.r_accessor_permit >= :"SYS_B_77")))))))) order by :"SYS_B_78", :"SYS_B_79"

For other group the sql is

I have include the sql bind file as attachment.

Can any one help on this? Let me know if you need more information.

slow_SQL_missing_binds.xls

Find more posts tagged with

Comments

PanfilovAB

Looks like common problem for all queries that do not provide good selectivity.

How many nm objects satisfy your second query?

sanjay4u

Hi,

second query display more than 1000. and it depends on the groups. Actually our groups are created as per the object type.

Example: for our custom object type (bioanalysis) there is group called bioanalysis. Base object type is mn object.

So when user login to the webtop it has a page called "workarea" where user can view the documents in which user is working and the group the user belongs to.

If one user is belongs to bioanlysis group when the user login to webtop in the workarea user can view all the documents for bioanalysis.

I think it will help you to get an idea about my question.

PanfilovAB

The difference between your queries is: the second one runs under superuser account, the first one - not. Your problem is definitely a design gap, because before show any results to user the first query should filter out a lot of data that user does not have required permissions.

sanjay4u

But for both queries we have same dql. so i am not sure why it is adding so many filters for first queries and not for second?

M.S.

In my experience those "filters" are always added for non-superuser accounts simply because access permissions are applied. Documentum translates your DQL into SQL by applying ACLs that are defined.

M.S.

These are things I have tried when faced with similar performance issue:

Try desabling MACL secuirty features (In the dm_docbase_config object set macl_security_disabled to TRUE)

Check if (I assume Oracle) DB params are set as recommended by EMC:

Parameter name	Recommended settings
db_block_size	8192(default)
ungo_management	AUTO
sga_target	Total physical memory * 80% * 80%
pga_aggregate_target	Total physical memory * 80% * 20%
processes	2 * concurrent_session in server.ini
session	processes * 1.1 + 5 (default)
optimizer_mode	FIRST_ROW_10
optimizer_index_cost_adj	5
optimizer_index_caching	95
cursor_sharing	FORCE
session_cached_cursors	50

Eventually run SQL Tuning Advisor (Oracle Enterprise Manager) on the identifed SQL statement. You may need some DBA to help you with this one.

In our case SQL tuning resulted in the biggest performance boost.

You should also check Robin East's blog for performance tips: http://robineast.wordpress.com/?s=performance