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.