Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Long wait time opening users or groups from admin Workbench. (Running over 300 security groups)

Highlighted
bzdafro
Collector

Long wait time opening users or groups from admin Workbench. (Running over 300 security groups)

Our current PPM 9.12 environment (oracle 11x) takes a very long time to open user accounts or security groups (sometimes over 10 min).   It wasn't like that until we upgraded a number of years back.  Cant remember the version.   Anyway, we setup a new 9.14 test environment and it was running smoothly out of box.   Now it is behaving the same way as our current PROD environment.   My account is a member of 9 security groups.  It takes 12 min for the workbench to open my account settings.   We only have 90 users loaded but have created over 300 groups.   Our security model requries us to create a security group per CI application.   I'm guessing its the number of security groups causing this behavior.   Has anyone run into this issue or use over 300 security groups?

9 REPLIES
Oscar_Pereira
Frequent Visitor

Re: Long wait time opening users or groups from admin Workbench. (Running over 300 security groups

Hi,

 

AWR may help you to identify the problematic query when reproducing the issue

 

What is the result for these queries?

 

a) SELECT * FROM user_ind_columns WHERE table_name IN
('KNTA_ENTITY_RESTRICTIONS', 'KWFL_WORKFLOWS_NLS','KWFL_WORKFLOWS_LOC');

b) SELECT COUNT(*) FROM KNTA_ENTITY_RESTRICTIONS;
c) SELECT COUNT(*) FROM KWFL_WORKFLOWS_NLS;
d) SELECT COUNT(*) FROM KWFL_WORKFLOWS_LOC;

 

 

In a particular scenario, it was found in AWR that each query spent more than 5s and the suspicious was that the query doesn't work on index correctly.

Since the data amount was more than 5000,000 in KNTA_ENTITY_RESTRICTIONS

A combined index on KNTA_ENTITY_RESTRICTIONS was not enough and adding the following separated indexes improved the performance.

 

CREATE INDEX KNTA_ENTITY_RESTRICTIONS_idx1 on KNTA_ENTITY_RESTRICTIONS(RESTRICTION_TYPE)
TABLESPACE ITG_INDEX;
CREATE INDEX KNTA_ENTITY_RESTRICTIONS_idx2 on KNTA_ENTITY_RESTRICTIONS(PARENT_ENTITY_ID) TABLESPACE ITG_INDEX;
CREATE INDEX KNTA_ENTITY_RESTRICTIONS_idx3 on KNTA_ENTITY_RESTRICTIONS(PARENT_VALUE) TABLESPACE
ITG_INDEX;

 

NOTE: 

**Where “ITG_INDEX” is whatever your tablespace index name is…

 

If you see a similar high number of records in KNTA_ENTITY_RESTRICTIONS, check if the above indexes make any difference in you test environment...

 

 

Regards

Oscar Pereira

bzdafro
Collector

Re: Long wait time opening users or groups from admin Workbench. (Running over 300 security groups

I attached export for the first query. 

 

SELECT COUNT(*) FROM KNTA_ENTITY_RESTRICTIONS; = 4551193

 

SELECT COUNT(*) FROM KWFL_WORKFLOWS_NLS; = 190

 

SELECT COUNT(*) FROM KWFL_WORKFLOWS_LOC; = 0

Oscar_Pereira
Frequent Visitor

Re: Long wait time opening users or groups from admin Workbench. (Running over 300 security groups

Thanks for the output, please go ahead with the Indexes creation and let us know the results

 

The output are similar to the scenario that i mentioned above

 

--Kudos are always welcome !

 

 

Regards

Oscar Pereira

Jim Esler
Honored Contributor

Re: Long wait time opening users or groups from admin Workbench. (Running over 300 security groups

We have had a ticket open for this problem for several months and are currently testing a hot fix that appears to address the problem. We are running version 9.14. Our ticket number is 4638721774 .

leewil1
Occasional Contributor

Re: Long wait time opening users or groups from admin Workbench. (Running over 300 security groups

Thanks for the quick responses!

 

Has the problem or slow query been identified?

leewil1
Occasional Contributor

Re: Long wait time opening users or groups from admin Workbench. (Running over 300 security groups

We (Robert and I) applied the above indexes for  KNTA_ENTITY_RESTRICTIONS and the response time has not changed.  We still see occurences of 20+  minutes wait time to open a user from the workbench.

 

Has anyone had success on this issue?  Other ideas?  Hotfix available?  We have an open case and they recommended we try the above.

bzdafro
Collector

Re: Long wait time opening users or groups from admin Workbench. (Running over 300 security groups

Oscar

We created the indexes last night, but so far same results.

 

 

Jim,

We will try to contact our HP rep to see if they can reference your ticket.   Just to clarify, did you experience long wait times trying to open users & groups?    If so, approximately how many groups have been created on your PPM server?    

 

Jim Esler
Honored Contributor

Re: Long wait time opening users or groups from admin Workbench. (Running over 300 security groups

Yes, we see long wait times when opening users or security groups in the workbench. We have about 10,500 security groups in production. About 8,500 are enabled. We see the problem in our development environment, too, and we have a much smaller list of security groups there.

 

Analysis of the issue indicated the problem was with the size of the knta_entity_restrictions table, not specifically the security group table. That table has about 13,500,000 entries. PPM development indicated at one point that they were optimizing the queries for this table. We also initially tried their suggestion to add indexes and it had no effect on performance.

bzdafro
Collector

Re: Long wait time opening users or groups from admin Workbench. (Running over 300 security groups

Thanks Jim.  HP just supplied us a hotfix for the issue so we will give it a try.   I will post the results in a few days.

//Add this to "OnDomLoad" event