[Rt-devel] Owner Drop-Down Performance - Solution

[Stephen Turner]

Hello,

A while ago I reported very slow performance in RT 3.4.1 on some screens - 
the "Owner" drop-down list seemed to be the culprit. The ticket create 
screen was taking 3.5 minutes to display. We also encountered slowness on 
the home page - the list of Quicksearch queues was taking about 12 seconds 
because of the "ShowTicket" rights check. Several other people on the 
mailing list have also reported slowness with the Owner drop-down list.

Our system admin/DBA has solved these problems for us by adding a couple of 
indexes:

  create index groups3 on groups(instance);
  create index groups4 on groups(lower(type));

This is an Oracle 9 installation, so I don't know if the solution works for 
other databases, or even if the problem exists for other databases, but I 
thought this knowledge was worth sharing. Details of our DBA's analysis of 
the problem are below.

Steve



Here is the query that was slowing us down:

SELECT main.* FROM ( SELECT DISTINCT main.id FROM Users main , Principals 
Principals_1, ACL ACL_2, Groups Groups_3, CachedGroupMembers 
CachedGroupMembers_4  WHERE
        ((ACL_2.RightName = 'OwnTicket')) AND
        ((CachedGroupMembers_4.MemberId = Principals_1.id)) AND
        ((Groups_3.id = CachedGroupMembers_4.GroupId)) AND
        ((Principals_1.Disabled = '0') or(Principals_1.Disabled = '0')) AND
        ((Principals_1.id != '1')) AND ((main.id = Principals_1.id)) AND
        ((ACL_2.PrincipalId = Groups_3.id AND ACL_2.PrincipalType = 'Group' AND
        (Groups_3.Domain = 'SystemInternal' OR Groups_3.Domain = 
'UserDefined' OR \
         Groups_3.Domain = 'ACLEquivalence'))  OR
        (((Groups_3.Domain = 'RT::Queue-Role' AND Groups_3.Instance = 
31)  )  AND \
          Groups_3.Type = ACL_2.PrincipalType) )  AND
        (ACL_2.ObjectType = 'RT::System' OR
        (ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId = 31) )  )
        distinctquery, Users main WHERE (main.id = distinctquery.id)
        ORDER BY main.Name ASC;

Lets take a quick look at TKprof and query tracing, and we find the following:

call     count       cpu    elapsed       disk      query    current
     rows
------- ------  -------- ---------- ---------- ---------- ----------
----------
Parse        1      0.00       0.00          0          0          0
        0
Execute      1      0.00       0.00          0          0          0
        0
Fetch       37     21.53     135.31      34097    1658525          0
       36
------- ------  -------- ---------- ---------- ---------- ----------
----------
total       39     21.53     135.31      34097    1658525          0
       36

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 115

Rows     Row Source Operation
-------  ---------------------------------------------------
      36  SORT ORDER BY
      36   NESTED LOOPS
      36    VIEW
      36     SORT UNIQUE
      83      CONCATENATION
       0       NESTED LOOPS
       0        NESTED LOOPS
       4         NESTED LOOPS
       4          NESTED LOOPS
       4           TABLE ACCESS FULL OBJ#(40166)
       4           INDEX RANGE SCAN OBJ#(40331) (object id 40331)
       4          TABLE ACCESS BY INDEX ROWID OBJ#(40172)
       4           INDEX UNIQUE SCAN OBJ#(40350) (object id 40350)
       0         INDEX UNIQUE SCAN OBJ#(40358) (object id 40358)
       0        INDEX RANGE SCAN OBJ#(40329) (object id 40329)
      83       HASH JOIN
       4        INDEX RANGE SCAN OBJ#(40329) (object id 40329)
  573518        HASH JOIN
  114581         TABLE ACCESS FULL OBJ#(40166)
1050634         NESTED LOOPS
1643365          HASH JOIN
  707103           TABLE ACCESS FULL OBJ#(40172)
1644032           INDEX FAST FULL SCAN OBJ#(40331) (object id 40331)
1050634          INDEX UNIQUE SCAN OBJ#(40358) (object id 40358)
      36    TABLE ACCESS BY INDEX ROWID OBJ#(40242)
      36     INDEX UNIQUE SCAN OBJ#(40358) (object id 40358)

OUCH!!!! Thats bad news, we see multiple occurrences of the dreaded full 
table scan. Breaking the query apart it appears its doing full table scans 
against the groups and principals table (you can look up the object_id and 
object_name in dba_objects). Some of the sub queries in the above large 
query cannot take advantage of the indexes which are created as part of the 
RT install which are:

CREATE UNIQUE INDEX groups_key on groups(id);
CREATE INDEX Groups1 ON Groups (lower( Domain), Instance, lower(Type), id);
CREATE INDEX Groups2 ON Groups (lower(Type), Instance, lower(Domain));

So lets help oracle and its query planner out and convince it to use index 
based scans by creating the following two additional indexes on the groups 
table.

SQL> create index groups3 on groups(instance);
SQL> create index groups4 on groups(lower(type));

Query run time is now down from 3 minutes to 3 seconds....

Now lets look at the query analysis after we have added the new indexes:

      36  SORT ORDER BY
      36   NESTED LOOPS
      36    VIEW
      36     SORT UNIQUE
      83      NESTED LOOPS
      93       NESTED LOOPS
      93        NESTED LOOPS
       4         NESTED LOOPS
       4          INDEX RANGE SCAN OBJ#(40329) (object id 40329)
       4          TABLE ACCESS BY INDEX ROWID OBJ#(40166)
      36           BITMAP CONVERSION TO ROWIDS
       4            BITMAP OR
       4             BITMAP CONVERSION FROM ROWIDS
       4              INDEX RANGE SCAN OBJ#(40351) (object id 40351)
       4             BITMAP CONVERSION FROM ROWIDS
      32              INDEX RANGE SCAN OBJ#(40409) (object id 40409)
      93         INDEX RANGE SCAN OBJ#(40331) (object id 40331)
      93        TABLE ACCESS BY INDEX ROWID OBJ#(40172)
      93         INDEX UNIQUE SCAN OBJ#(40350) (object id 40350)
      83       INDEX UNIQUE SCAN OBJ#(40358) (object id 40358)
      36    TABLE ACCESS BY INDEX ROWID OBJ#(40242)
      36     INDEX UNIQUE SCAN OBJ#(40358) (object id 40358)

Much better, oracle is using the indexes for all its work and we clearly 
see the benefit.