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

[Ruslan Zakirov]

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 <elacour at easter-eggs.com> 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: sales at bestpractical.com
>
>
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
> Buy a copy at http://rtbook.bestpractical.com
>



-- 
Best regards, Ruslan.