UI task timeout

[jaisuryan]

Hi Experts,

I'm in a weird situation..!! we have workflow configured to assign a business role to job role thru approvals and direct assignment. Whole configuration works well except when I try to remove a business role from the job role directly. When I remove the role and click "Save" in the UI task, it keeps loading for few mins and gets timed out and the corresponding workflow is not getting triggered.

Below is the link where I'm removing a role from the entry 425961 (a job role). Task 102003 is for processing the changes done in the job role.

https://****/webdynpro/dispatcher/sap.com/tc~idm~wd~workflow/EditTask?TaskId=102003&EntryId=425961

Screenshot of the task 102003 as below,

and as per config, it should trigger the task "Commit Approved Roles"

But the page gets times out and task doesn't get triggered. This happens only in Production. Please suggest where the issue would lie.

Not sure if it is IDM UI issue or workflow config or Portal. It works completely fine if i add business role to job role and so any modification to job role.

Kind regards,

Jaisuryan

[Former Member · Accepted solution]

Hi ,

Are you able to do the removal from MMC ? How much time MMC job is taking in this case . Also which Support Pack or Patch you are working ?

Thanks ,

Jerry

[Former Member · Accepted solution]

As Jerry asked, which version of IdM are you running (SP level)?

If this is an older version of IdM running without the global constant MX_RECONCILE set to FALSE then this is most likely because there's a huge transaction running on the server going through all the members of both roles and recalculating their memberships. If the constant is set the entries will be marked dirty and can be recalculated individually in smaller transactions using the "reconcile users" job template.  But if I remember you're (guessing the customer from the name of the scripts in the screenshot) running on SP8.

On newer versions (>sp6) its more likely to be an issue with the structure root table (mxi_structure_root) and you should do a count on it to see if it has an excessive amount of entries. If I recall correctly there was an issue where this table became bloated and killed performance.

Other than that I would start with the contents of troubleshooting using the threshold log, and Finding problems using database queries from . Finding out which procedure or query is running "forever" will help a lot.

Br,

Chris

[jaisuryan · Accepted solution]

Hi Chris,

Yes you are right, we are on SP8 and MXI_STRUCTURE_ROOT table caused performance and role removal issue for us. I will try to execute database queries and check the database performance while removing the role.

However am still confused why it is getting timed out only for role removal and not for addition of the role. Doesnt IDM does the same calculations and database updates of roles and its members for both addition and removal?

Thanks for your inputs Chris and Jerry.

Kind regards,

Jaisuryan

[Former Member · Accepted solution]

Inserting new rows from a new link is quick since there should be no need to do checks on the existing data in mxi_structure_root (its a new link after all). I can think of two possible issues on Oracle:

First one is that occationally there are inserts of duplicate data which cause bloating during role/role/priv linking. This causes problems when deleting from the structure root as deletes requires conditions to avoid deleting too much. If this is the problem you'll see the procedure mxi_update_structure running for a long time. A count(*) from mxi_structure_root would also be very helpful.

The second one is that you perform an operation that cause the system to just rebuild the entire table instead of updating the existing structure, and there is some performance enhancements done there lately. If this is the case you'll see the procedure mxi_build_Roots running for long periods of time.

In either case it sounds like you have a CSS ticket issue, but it would help speed up the resolution if report atleast the rowcount mentioned above with the ticket.

Br,

Chris

[jaisuryan · Accepted solution]

Hi Chris,

The row count for MXI_STRUCTURE_ROOT table is 687851

I have done the 1st set of checking in database side. Yes, when I delete the role and save, it executes below sql from MXI_BUILD_ROOTS procedure,

INSERT INTO MXI_STRUCTURE_ROOT (MCCHILDMSKEY, MCROOTMSKEY, MCPARENTMSKEY, MCENTRYTYPE, MCLEVEL) SELECT A.MCCHILDMSKEY, B.MCROOTMSKEY, A.MCPARENTMSKEY, A.MCENTRYTYPE, 0 FROM MXI_STRUCTURE_ROOT A INNER JOIN MXI_STRUCTURE_ROOT B ON A.MCPARENTMSKEY = B.MCCHILDMSKEY WHERE A.MCLEVEL = :B1 AND NOT EXISTS (SELECT 1 FROM MXI_STRUCTURE_ROOT C WHERE C.MCCHILDMSKEY = A.MCCHILDMSKEY AND B.MCROOTMSKEY = C.MCROOTMSKEY AND A.MCPARENTMSKEY = C.MCPARENTMSKEY)

INSERT INTO MXI_STRUCTURE_ROOT (MCCHILDMSKEY, MCROOTMSKEY, MCPARENTMSKEY, MCENTRYTYPE, MCLEVEL) SELECT A.MCTHISMSKEY, A.MCOTHERMSKEY, A.MCOTHERMSKEY, A.MCTHISENTRYTYPE, :B1 FROM MXI_LINK A WHERE A.MCLINKTYPE = 1 AND A.MCLINKSTATE = 0 AND A.MCOTHERMSKEY IN (SELECT B.MCCHILDMSKEY FROM MXI_STRUCTURE_ROOT B WHERE B.MCLEVEL = :B1 -1 AND NOT EXISTS (SELECT 1 FROM MXI_STRUCTURE_ROOT C WHERE C.MCCHILDMSKEY = A.MCTHISMSKEY AND A.MCOTHERMSKEY = C.MCROOTMSKEY AND A.MCOTHERMSKEY = C.MCPARENTMSKEY))

Below is the sql in MXI_BUILD_ROOTS procedure,

=================

(

  InMSKEY number,

  ROOTMSKEY number,

  Plevel number,

  Pmaxlevel number,

  Perr OUT number)

IS

        MyLevel  number(10);

        Lnum     number(10);

        Sublevel number(10);

BEGIN

   Perr := 0;

   if InMSKEY = 0 then

      -- This function reset the mxi_structure_root table and build it up again

      delete from mxi_structure_root;

      -- Insert first level links

      insert into mxi_structure_root (mcChildMskey, mcRootMskey, mcParentMskey, mcEntryType, mcLevel)

            select A.mcthismskey, A.mcothermskey, A.mcothermskey, A.mcthisentrytype, 1 from mxi_link A

            where not exists (select 1 from mxi_link B where  A.mcothermskey = B.mcthismskey and B.mcLinkType = 1 AND B.mcLinkState = 0)

            and   A.mcLinkType = 1 AND A.mcLinkState = 0

            and   not exists (select 1 from mxi_objectclasses C where C.ocName = 'MX_PERSON' and C.ocid = A.mcotherentrytype);

      -- Other level links

      Mylevel := 2;

      while Mylevel < Pmaxlevel

      loop

         insert into mxi_structure_root (mcChildMskey, mcRootMskey, mcParentMskey, mcEntryType, mcLevel)

               select A.mcThisMSKEY, A.mcothermskey, A.mcothermskey, A.mcthisentrytype, Mylevel from mxi_link A

               where A.mcLinkType = 1 and A.mcLinkState = 0

               and   A.mcOtherMSKEY in (select B.mcchildmskey from mxi_structure_root B where B.mclevel = Mylevel-1

               and   not exists (select 1 from mxi_structure_root C where C.mcChildMskey = A.mcThisMSKEY and A.mcothermskey = C.mcRootmskey and A.mcothermskey = C.mcParentmskey));

         Lnum := SQL%ROWCOUNT;

         if Lnum = 0 then

            exit;

         end if;

         -- Build inherit link structure

         Sublevel := Mylevel;

         while Sublevel > 1

         loop

            insert into mxi_structure_root (mcChildMskey, mcRootMskey, mcParentMskey, mcEntryType, mcLevel)

                  select A.mcChildMskey, B.mcRootMskey, A.mcParentMskey, A.mcentrytype, 0 from mxi_structure_root A

                  inner join mxi_structure_root B on A.mcParentMskey = B.mcChildMskey

                  where A.mcLevel = Sublevel

                  and   not exists

                  (select 1 from mxi_structure_root C where C.mcChildMskey = A.mcChildMskey and B.mcRootMskey = C.mcRootmskey and A.mcParentmskey = C.mcParentmskey);

            Sublevel := Sublevel - 1;

         end loop;

         Mylevel := Mylevel + 1;

      end loop;

      -- Update sub tree nodes to level 0.

      update mxi_structure_root set mcLevel = 0 where mcLevel > 1;

   end if;

END;

==============================

Please let me know what enhancements were done in this procedure. I update the post again with Execution Threshold outputs.

Thanks for your help.

Kind regards,

Jaisuryan

[jaisuryan · Accepted solution]

Sorry for not mentioning, yes, database performance is adverse when MXI_BUILD_ROOTS procedure is executed. This procedure is not called when I add the role to the job role.

[Former Member · Accepted solution]

Hi Jai ,

There was a known issue around "MXI_STRUCTURE_ROOT" , associated with JOB -> Business Role Assignment . Please refer to SAP  Note - 1935957 - Bad performance when rebuilding role hierarchy

Hope this helps .

Jerry George

[jaisuryan · Accepted solution]

Hi Jerry,

Thanks for the note. We will consider applying the patch and let you know.

Hi Chris,

Can you please confirm if applying database patch would resolve this issue? Please suggest if anything to be done in IDM UI config as well. As you would know, we have to give sure commitment to the customer that the patch would fix the issue to get approval. Thanks.

Kind regards,

Jaisuryan

[Former Member · Accepted solution]

I compared the contents of the procedure in SP8 Patch3 and its identical to what you already listed in your previous reply, the same with SP9. The note might refer to a related but different fix. I'm told that we're waiting for the codelines to be released after the latest patches before we can add the new version of this procedure. Please create a CSS ticket and mention that this is a known problem that is verified (by me) and lets see where it ends up.

Br,

Chris

[jaisuryan · Accepted solution]

Thanks Chris, We have raised the CSS ticket

UI session time out on Deletion of Role ( 179603 / 2014 )

Hopefully we end up with solution sooner. Thanks for the help.