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

[Ruslan Zakirov]

On Wed, Feb 27, 2008 at 12:43 PM, Emmanuel Lacour
<elacour at easter-eggs.com> wrote:
> On Sun, Feb 24, 2008 at 11:57:46PM +0300, Ruslan Zakirov wrote:
>  > Hello, guys.
>  >
>
>  Privet Ruslan ;)
Privet.

>
>  (and thanks for commiting my patches in svn :))
Welcome, there are still some not applied. I'll look into them as well.

>  > I'm reviewing this again after receiving new info from various sources.
>  > 1) People say that our function based indexes are incorrect, instead
>  > of LOWER('XXX') we must use LOWER(XXX), where XXX is name of a column.
>  > 2) This particular query we build by hand without using our abstract
>  > interface, so it even don't have any calls to LOWER() function.
>  >
>  > So you should try:
>  > 1) replace all indexes that has LOWER('XXX'), list of indexes we
>  > create by default is in etc/schema.Oracle
>
>  Done, no change, see my comment on #8970.
Ok.

>  > 2) create index on Groups(Type, Domain, Instance) instead of one I
>  > suggested before and explain query without any LOWER calls.
>
>  I tried, but it isn't needed, actually, with the following custom
>  indexes (made as I said, for other speed improvements), _and_ after
>  running dbms_utility.analyze_schema('RT', 'compute'), every indexes are
>  used:

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



>
>  My current custom indexes:
>
>  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);
>

[snip plan]

>
>  I can enhance a little bit with the following index:
>
>  CREATE INDEX TEST1 ON CACHEDGROUPMEMBERS(MEMBERID);
>

Instead of TEST1 index create, however read to the end first:
CREATE INDEX TEST2 ON CACHEDGROUPMEMBERS(MEMBERID, GROUPID);

You have the following index:
CREATE INDEX FSHCGM1 ON CACHEDGROUPMEMBERS (DISABLED, MEMBERID);

I do think that in the end you can have one index:
CREATE INDEX CGM_FINAL ON CACHEDGROUPMEMBERS(MEMBERID, GROUPID, DISABLED);
That will cover FSHCGM1, TEST1 and TEST2 without much penalty.

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.

Second goal is too confirm that CGM_FINAL will not make things much
worse when there is no FSHCGM1, TEST1 and TEST2.

[snip plan]

>
>
>  Maybe we should try to review the SQL to minimize NON-UNIQUE indexes uses, but that's outside my current knowledge :(
>

-- 
Best regards, Ruslan.