I'm not that good in oracle's optimizer, but I'm pretty sure that the
following index will help you:
CREATE INDEX Groups3 ON Groups (LOWER('Type'), LOWER('Domain'), Instance);Can you add it, generate plan again and sent it to the list, so I can check that it really helps in the way I think it should :) On Jan 21, 2008 5:40 PM, Emmanuel Lacour <[EMAIL PROTECTED]> wrote: > > 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 > _______________________________________________ > http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users > > Community help: http://wiki.bestpractical.com > Commercial support: [EMAIL PROTECTED] > > > Discover RT's hidden secrets with RT Essentials from O'Reilly Media. > Buy a copy at http://rtbook.bestpractical.com > -- Best regards, Ruslan. _______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
