Please share any images or screenshots, if relevant.
[Please insert images here, otherwise delete this section]
Please share any other relevant files that may be required (for example, logs).
[Please insert files here, otherwise delete this section]
Share all details about your problem, including any error messages you may have received.
Hello,
We are seeing a lot of UI Performance issues. We recently upgraded from 8.2p5 to 8.4p2. We have over 500k Entitlements and few active certifications with about 1million total items. We are seeing issues with loading access review pages, approving access reviews and other functionalities.
I am reaching out to see if anyone can provide guidance on database table indexing for performance tuning. It would be great if you can share creating index queries which has helped with database performance on your system?
take a look on my topic. My topic talk about general performance improvment but you can start from it and go more deep.
On my experience I saw a lot of enviroment without a maintainence plan or with a lot of index or with extended columns with 450 when it use for a boolean value.
about this is you use the stardard page of IIQ you must use the queries of IIQ. You can try to optimize the tables which are interested in these cases, but you risk causing damage on DB.
After a deep analisys on the data that you are reading you can growup the size of the pages of db, but you can reduce the performance for other quieres.
Additionally, you can try to reduce the entitlements in your environment, deleting unused ones.
We had this issue a few years ago. Ours was caused by a provisioning policy on an IT role that was detected on thousands of our identities. In the BeanShell rule of that provisioning policy, we were searching for ManagedAttributes, but we had Filters that didn’t make use of the indexes on that table in the database. We also found out that the certification page can create a provisioning plan under certain circumstances (like whenever you Approve an item) which has to run that provisioning policy to calculate the value. The thousands of unoptimized queries caused by certification page loading all hitting our database in the same small window slowed everything to a crawl.
We fixed it by changing the Filters in those rules to match the indexes on the table we were searching and then checking the logs on the database to make sure that the sql query execution plan after hibernate modified it was still using those indexes.
So we went from something like this:
QueryOptions bad = new QueryOptions(Filter.like("value", roleName));
To something like this:
QueryOptions good = new QueryOptions(Filter.and(Filter.eq("application", application.getId()),
Filter.eq("type", "group"),
Filter.eq("attribute", "memberOf"),
Filter.like("value", roleName, Filter.MatchMode.START)
));