cancel
Showing results for 
Search instead for 
Did you mean: 

Unable to calculate dynamic groups

sd6
Explorer
0 Kudos

Hi Experts,

we have a problem with our dynamic groups calculation. For a while now our job, which calculates all the dynamic groups (about 760), doesn’t work anymore. It’s scheduled every night. We recalculate the dynamic groups with the internal function uIS_ResolveDynamicGroup(). Normally it takes only seconds for each group. Except for 3 groups, there are only a few members per group (>30).

Now it takes several groups about 10 minutes to finish. There is no pattern. There are always different groups and a different number of groups. When the Job takes too long it won't finish, because of timeout. It will start the job a few times before it will stop with an error status and we have to start it manually again.

We are on SP10. From my personal the feeling, the problem exists since we are on SP10. But it only occurred about every 2 weeks. Now it is getting more frequent. When we upgraded to SP10 our database was slow. In consultation with the SAP Oracle DB team we set the oracle parameter '_OPTIMIZER_USE_FEEDBACK' to 'FALSE' and it worked again.

Here is the job log of the job. Normally it should takes between 500 - 600 seconds. As seen on the 22nd.

This is part of the job log from 24th at 00:03. The Job started at 10 pm.

The statements of the dynamic groups look like the following:


select a.mskey from idmv_value_basic a

  inner join

  idmv_value_basic b on

  a.attrname = 'MX_IDENTITYUUID' and b.attrname='MX_IDENTITYUUID' and a.searchvalue = b.searchvalue

  inner join

  idmv_link_ext c on b.mskey = c.mcthismskey and c.mcattrname = 'MXREF_MX_ROLE' and c.mcothermskeyvalue = 'ROLE:COE_LL000006'

  inner join

  idmv_value_basic d on c.mcthismskey = d.mskey and d.attrname='KPMG_USER_TYPE' and d.searchvalue='PERSNR'

  inner join

  idmv_value_basic e on a.mskey = e.mskey and e.attrname='KPMG_USER_TYPE' and e.searchvalue in ('NAMED')

  left outer join

  idmv_value_basic f on

  a.mskey = f.mskey and f.attrname='MX_VALIDTO'

  where f.searchvalue is null or to_char(sysdate, 'YYYY-MM-DD') < SUBSTR(f.searchvalue,1,10)

Do you have any idea why it's taking so long to recalculate a dynamic group with only 14 members?

Thanks and kind regards,

Sven

Accepted Solutions (0)

Answers (1)

Answers (1)

devaprakash_b
Active Contributor
0 Kudos

Hi Sven,

Can you kindly provide the source and destination of the job which you are using to recalculate and also the script which you are calling the uIS_ResolveDynamicGroup() function

Regards,

DP

sd6
Explorer
0 Kudos

Hi DP,

the source tab of the job is empty. The destination tab has only some debug input. The script just selects all dynamic groups and resolves them in a loop:


function kpmg_recalcDynGroupsAll(Par) {

  var debug   = Par.get ("DEBUG");

  var debugTime   = Par.get ("DEBUGTIME");

  var errorMailTime = Par.get ("ERRORMAILTIME");

  var sql = "select mcmskey, mcmskeyvalue from idmv_entry_simple where mcentrytype='MX_DYNAMIC_GROUP'\

  order by mcmskeyvalue";

   

  var result = uSelect (sql);

  var entries = result.split ("!!");

  for (var jj=0; jj<entries.length; jj++) {

  var values = entries[jj].split("|");

  var dynGroupMskeyvalue = values[1];

  var time = new Date().getTime();

  var re = uIS_ResolveDynamicGroup (values[0]);

  var timeNeeded = new Date().getTime()-time;

  if(timeNeeded > debugTime || debug == "1")

  uWarning (timeNeeded + " ms needed to calc Dynamic Group " +dynGroupMskeyvalue+ " returns " + re);

  if(timeNeeded > errorMailTime)

  mailErrorLog2();

  }

  uWarning("______ " +entries.length+ " dynamic groups calculated.");

}

Regards,

Sven

devaprakash_b
Active Contributor
0 Kudos

Hi Sven,

The script seems to be fine. Can you please check the queries defined for the Dynamic group in the target filter. In our project we saw the joins which we used took lot of time.

Regards,

DP

sd6
Explorer
0 Kudos

Hi DP,

the target filters are all lookling like this:


select a.mskey from idmv_value_basic a 

  inner join 

  idmv_value_basic b on 

  a.attrname = 'MX_IDENTITYUUID' and b.attrname='MX_IDENTITYUUID' and a.searchvalue = b.searchvalue 

  inner join 

  idmv_link_ext c on b.mskey = c.mcthismskey and c.mcattrname = 'MXREF_MX_ROLE' and c.mcothermskeyvalue = 'ROLE:COE_LL000006' 

  inner join 

  idmv_value_basic d on c.mcthismskey = d.mskey and d.attrname='Z_USER_TYPE' and d.searchvalue='PERSNR' 

  inner join 

  idmv_value_basic e on a.mskey = e.mskey and e.attrname='Z_USER_TYPE' and e.searchvalue in ('NAMED') 

  left outer join 

  idmv_value_basic f on 

  a.mskey = f.mskey and f.attrname='MX_VALIDTO' 

  where f.searchvalue is null or to_char(sysdate, 'YYYY-MM-DD') < SUBSTR(f.searchvalue,1,10) 

Thanks,

Sven