[rt-users] Oracle 9 and Build.html performance

[Emmanuel Lacour]

Hi everybody,

I'm looking for help or hints on this problem. RT 3.6.5 do the following
request when hitting the search page (Build.html, empty, just the query
builder) and take 20 seconds to get an empty result from Oracle :(

SELECT main.* FROM ( SELECT DISTINCT main.id FROM Users main CROSS JOIN
ACL ACL_4 JOIN Principals Principals_1 ON ( Principals_1.id = main.id )
JOIN CachedGroupMembers CachedGroupMembers_2 ON (
CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN Groups Groups_3
ON ( Groups_3.id = CachedGroupMembers_2.GroupId ) WHERE
(Principals_1.Disabled = '0') AND (ACL_4.PrincipalType = Groups_3.Type)
AND (Principals_1.id != '1') AND (Principals_1.PrincipalType = 'User')
AND (ACL_4.RightName = 'OwnTicket') AND ((ACL_4.ObjectType =
'RT::Queue') OR (ACL_4.ObjectType = 'RT::System')) AND ((Groups_3.Domain
= 'RT::Queue-Role') OR (Groups_3.Domain = 'RT::System-Role')) )
distinctquery, Users main WHERE ( main.id = distinctquery.id) ORDER BY
main.Name ASC;


It's a stock RT oracle schema with the followings custom indexes (needed
for other performances problem):

- FSHACL1 on column OBJECTID, table: ACL,
- FSHCGM1 on columns DISABLED,MEMBERID, table: CACHEDGROUPMEMBERS,
- FSHGROUPMEMBERS1 on column MEMBERID, table: GROUPMEMBERS,
- FSHGROUPS1 on column INSTANCE, table: GROUPS,
- FSHPRINCIPALS1 on column DISABLED, table: PRINCIPALS,
- FSHTICKETS1 on column STATUS, table: TICKETS.

optimizer_mode is "choose"
optimizer_index_caching is 50
optimizer_index_cost_adj is 1


I did an explain with the following result:

Execution Plan
----------------------------------------------------------
   0	  SELECT STATEMENT Optimizer=CHOOSE (Cost=11446 Card=1 Bytes=2
	  54)

   1	0   SORT (ORDER BY) (Cost=11446 Card=1 Bytes=254)
   2	1     NESTED LOOPS (Cost=11444 Card=1 Bytes=254)
   3	2	VIEW (Cost=11443 Card=1 Bytes=10)
   4	3	  SORT (UNIQUE) (Cost=11443 Card=1 Bytes=77)
   5	4	    NESTED LOOPS (Cost=1231 Card=1170677 Bytes=9014212
	  9)

   6	5	      HASH JOIN (Cost=1230 Card=20646030161 Bytes=1424
	  576081109)

   7	6		TABLE ACCESS (FULL) OF 'GROUPS' (Cost=195 Card
	  =227456 Bytes=5686400)

   8	6		NESTED LOOPS (Cost=205 Card=453847 Bytes=19969
	  268)

   9	8		  MERGE JOIN (CARTESIAN) (Cost=204 Card=209510
	  2 Bytes=83804080)

  10	9		    INLIST ITERATOR
  11   10		      INDEX (RANGE SCAN) OF 'ACL1' (NON-UNIQUE
	  ) (Cost=1 Card=23 Bytes=667)

  12	9		    BUFFER (SORT) (Cost=203 Card=90683 Bytes=9
	  97513)

  13   12		      TABLE ACCESS (BY INDEX ROWID) OF 'PRINCI
	  PALS' (Cost=9 Card=90683 Bytes=997513)

  14   13			INDEX (RANGE SCAN) OF 'FSHPRINCIPALS1'
	   (NON-UNIQUE)

  15	8		  INDEX (UNIQUE SCAN) OF 'USERS_KEY' (UNIQUE)
  16	5	      INDEX (RANGE SCAN) OF 'GROUMEM' (NON-UNIQUE)
  17	2	TABLE ACCESS (BY INDEX ROWID) OF 'USERS' (Cost=1 Card=
	  1 Bytes=244)

  18   17	  INDEX (UNIQUE SCAN) OF 'USERS_KEY' (UNIQUE)




Statistics
----------------------------------------------------------
	237  recursive calls
	  0  db block gets
    2699081  consistent gets
	  0  physical reads
	  0  redo size
       1249  bytes sent via SQL*Net to client
	275  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  9  sorts (memory)
	  0  sorts (disk)
	  0  rows processed



Thanks for any help :)


-- 
Emmanuel Lacour