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

[Ruslan Zakirov]

On Wed, Feb 27, 2008 at 6:41 PM, Emmanuel Lacour
<elacour at easter-eggs.com> wrote:
> On Wed, Feb 27, 2008 at 04:41:55PM +0300, Ruslan Zakirov wrote:
>  >
>  > Privet.
>  >
>
>  I will be near request-tracker.ru on saturday, flying to Tomck :)
Missed this note last time :) but anyway Moscow is to far from Tomsk
and I have feeling that it's farther than France :)

>  > Oracle doesn't want to build plan I want it to build :(. I still
>  > believe it should use different way. I hope you'll help me by
>  > providing more explains and may be we'll make this query really fast
>  > as it should be or learn some lessons to remember in the future.
>  >
>  > The following query use a hint to predefine order of joins, I want you
>  > to explain it, so I can compare plans with those we have now.
>  >
>  > SELECT main.* FROM (
>  >     SELECT /* ORDERED */ DISTINCT main.ID
>  >     FROM acl acl_4, GROUPS groups_3, cachedgroupmembers
>  > cachedgroupmembers_2, principals principals_1, users main
>  >     WHERE
>  >             acl_4.rightname = 'OwnTicket'
>  >         AND (acl_4.objecttype = 'RT::Queue' OR acl_4.objecttype = 'RT::System')
>  >         AND acl_4.principaltype = groups_3.TYPE
>  >         AND (groups_3.domain = 'RT::Queue-Role' OR groups_3.domain =
>  > 'RT::System-Role')
>  >         AND groups_3.ID = cachedgroupmembers_2.groupid
>  >         AND cachedgroupmembers_2.memberid = principals_1.ID
>  >         AND principals_1.ID != '1'
>  >         AND principals_1.disabled = '0'
>  >         AND principals_1.principaltype = 'User'
>  >         AND principals_1.ID = main.ID
>  > ) distinctquery, users main
>  > WHERE (main.ID = distinctquery.ID)
>  > ORDER BY main.NAME ASC

Emanuel, what about the above query with optimizer hint?

>  2) we clearly need index on Principals, si plan with the folowing index:
>
>
>  CREATE INDEX FSHPRINCIPALS1 ON PRINCIPALS (DISABLED);
As this index helps you I'm pretty sure the following will be better:
CREATE INDEX FSHPRINCIPALS2 ON PRINCIPALS (PRINCIPALTYPE, DISABLED);

>
>  5) plan with CGM_FINAL instead of TEST1/TEST2
I'm going to add this index into RT in 3.8 for Oracle and mysql, not
sure about Pg and other DBs.


> > >  CREATE INDEX FSHACL1 ON ACL (OBJECTID);
>  > >  CREATE INDEX FSHCGM1 ON CACHEDGROUPMEMBERS (DISABLED, MEMBERID);
>  > >  CREATE INDEX FSHGROUPMEMBERS1 ON GROUPMEMBERS (MEMBERID);
>  > >  CREATE INDEX FSHGROUPS1 ON GROUPS (INSTANCE);
>  > >  CREATE INDEX FSHPRINCIPALS1 ON PRINCIPALS (DISABLED);
>  > >  CREATE INDEX FSHTICKETS1 ON TICKETS (STATUS);
>  > >
>  >
>
> > The most important thing I want to see explain with TEST2, we need
>  > confirmation that oracle successfully switches from TEST1 to TEST2 and
>  > benefits from it.
>
>  That's ok.
Thank you for valuable feedback.

>
>
>  >
>  > Second goal is too confirm that CGM_FINAL will not make things much
>  > worse when there is no FSHCGM1, TEST1 and TEST2.
>
>  Also ok.
>
>
>  But still no perf improvement :(

Not sure what to do. May be we should try to explain the query on
oracle 10 with similar amount of data.

-- 
Best regards, Ruslan.