Grouper Users - Open Discussion List

["Redman, Chad" <>]

I wanted to send out a note on a performance issue that we had in Oracle, which we fixed. We had been getting complaints that some UI actions were slow recently. As a wheel group user in Grouper, I rarely see any of these issues, so it took a non-wheel user going through a live demo of normal UI actions, that made the issue clear. The left navbar folder navigation has always been somewhat slow for users, even with security.show.folders.where.user.can.see.subobjects=false to skip empty folder checking. But the subject/group search bar was really bad, around 1.5 to 2 minutes to do a basic search for a group by name -- enough to time out regularly.

I identified one query was likely causing the problem. It was querying the groups by the search term, but also linking to the user's privileges, to make sure it only returned groups the user could read. The way Oracle reported its query plan, it thought that querying the memberships first would narrow the cardinality to 16 rows, small enough that it could do nested loops and table scans for all the other query steps. Since we have 1 million rows in grouper_memberships but 100,000 unique members, this wasn't an unreasonable assumption. But in actuality, the "EveryEntity/GrouperAll" subject had view or read privileges on 35,000 groups. So the query was looping and table scanning 2,000 times more than it expected.

Oracle not only collects table statistics, but it also has a feature to maintain histogram data on columns. Values are put into a fixed number of bins. If the column data is heavily skewed toward one value, that value will occupy one or more bins by itself, and the query analysis can use that information to get a rough estimate on the cardinality of a filter on that column.

In our case, we simply added a histogram statistic on GROUPER_MEMBERSHIPS.MEMBER_ID:

BEGIN

  DBMS_STATS.GATHER_TABLE_STATS (

    ownname    => 'GROUPER'

,   tabname    => 'GROUPER_MEMBERSHIPS'

,   method_opt => 'FOR COLUMNS MEMBER_ID'

);

END;

After that, the query went from 1.5 minutes to 4-8 seconds. The user reported not only that groups searches by name were now taking 8 seconds instead of timing out after 2 minutes, but the folder tree navigation was now instantaneous!

-Chad