On Wed, Feb 27, 2008 at 6:41 PM, Emmanuel Lacour <[EMAIL PROTECTED]> 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. _______________________________________________ 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
