Oracle 9 and Build.html performance

Emmanuel_Lacour · January 21, 2008, 2:40pm

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 SQLNet to client
275 bytes received via SQLNet from client
2 SQL*Net roundtrips to/from client
9 sorts (memory)
0 sorts (disk)
0 rows processed

Thanks for any help

Emmanuel Lacour

Ruslan_Zakirov1 · January 21, 2008, 4:51pm

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@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

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@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.

Torsten_Brumm · January 21, 2008, 7:06pm

Hi RT Users,

Another small problem. I’m trying to write down the CurrentUser
Information acting on a ticket to a CF.

I tried this code:

my $queue = $ticket->QueueObj;
my $actualuser = $session{CurrentUser}->Name;
$self->TicketObj->AddCustomFieldValue(Field => ‘Assigned_by_SA’, Value
=> $acutaluser, RecordTransaction => 0);
return 1;

Whichs ends up with a strange error message: %sessions not found…

I can’t find any good solution at the wiki yet.

Thanks

Torsten
Kuhne + Nagel (AG & Co.) KG, Geschaftsleitung: Hans-Georg Brinkmann (Vors.), Uwe Bielang (Stellv.), Bruno Mang, Alfred Manke, Thorsten Meincke, Mark Reinhardt (Stellv.), Jens Wollesen, Rainer Wunn (Stellv.), Sitz: Bremen, Registergericht: Bremen, HRA 21928, USt-IdNr.: DE 812773878, Personlich haftende Gesellschaft: Kuhne & Nagel A.G., Sitz: Contern/Luxemburg Geschaftsfuhrender Verwaltungsrat: Klaus-Michael Kuhne

Ruslan_Zakirov1 · January 21, 2008, 7:16pm

$self->Transaction->Creator - id
$self->Transaction->CreatorObj - RT::User objectOn Jan 21, 2008 10:06 PM, Ham MI-ID, Torsten Brumm torsten.brumm@kuehne-nagel.com wrote:

Hi RT Users,

Another small problem. I’m trying to write down the CurrentUser
Information acting on a ticket to a CF.

I tried this code:

my $queue = $ticket->QueueObj;
my $actualuser = $session{CurrentUser}->Name;
$self->TicketObj->AddCustomFieldValue(Field => ‘Assigned_by_SA’, Value
=> $acutaluser, RecordTransaction => 0);
return 1;

Whichs ends up with a strange error message: %sessions not found…

I can’t find any good solution at the wiki yet.

Thanks

Torsten
Kuhne + Nagel (AG & Co.) KG, Geschaftsleitung: Hans-Georg Brinkmann (Vors.), Uwe Bielang (Stellv.), Bruno Mang, Alfred Manke, Thorsten Meincke, Mark Reinhardt (Stellv.), Jens Wollesen, Rainer Wunn (Stellv.), Sitz: Bremen, Registergericht: Bremen, HRA 21928, USt-IdNr.: DE 812773878, Personlich haftende Gesellschaft: Kuhne & Nagel A.G., Sitz: Contern/Luxemburg Geschaftsfuhrender Verwaltungsrat: Klaus-Michael Kuhne

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@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.

Torsten_Brumm · January 22, 2008, 6:46am

Hi Ruslan,

Many thanks for your help.

$self->Transaction->CreatorObj wont work for me, played around a little and got success with: $self->TransactionObj->CreatorObj->Name

But it was a good hint into the correct direction.

Thanks

Torsten

Emmanuel_Lacour · January 22, 2008, 11:25am

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

Thanks for your help Ruslan! Unfortunatly, it didn’t solve anything

An interesting think is that I tried the same request on an Oracle 10g, with
same content … and here no problem of performances with the stock RT indexes.
So maybe we would better try to upgrade to Oracle 10g.

I did:

SQL> set autotrace on
SQL> CREATE INDEX Groups3 ON Groups (LOWER(‘Type’), LOWER(‘Domain’), Instance);
Index created.
SQL> execute dbms_utility.analyze_schema( ‘RT’, ‘estimate’);

then here is the plan for the same request:

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
0 recursive calls
0 db block gets
2699012 consistent gets
0 physical reads
0 redo size
1235 bytes sent via SQLNet to client
275 bytes received via SQLNet from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
0 rows processed

Also here is some statistics for this database:

SQL> SELECT count() FROM Attachments;
627770
SQL> SELECT count() FROM Queues;
334
SQL> SELECT count() FROM Links;
1737
SQL> SELECT count() FROM Principals;
358744
SQL> SELECT count() FROM Groups;
338358
SQL> SELECT count() FROM ScripConditions;
10
SQL> SELECT count() FROM Transactions;
1361371
SQL> SELECT count() FROM Scrips;
164
SQL> SELECT count() FROM ACL;
4124
SQL> SELECT count() FROM GroupMembers;
330161
SQL> SELECT count() FROM CachedGroupMembers;
981829
SQL> SELECT count() FROM Users;
19645
SQL> SELECT count() FROM Tickets;
76012
SQL> SELECT count() FROM ScripActions;
17
SQL> SELECT count() FROM Templates;
58
SQL> SELECT count() FROM ObjectCustomFields;
1198
SQL> SELECT count() FROM ObjectCustomFieldValues;
101937
SQL> SELECT count() FROM CustomFields;
176
SQL> SELECT count() FROM CustomFieldValues;
1244
SQL> SELECT count() FROM Attributes;
1316
SQL> SELECT count(*) FROM sessions;
0

Joop · January 22, 2008, 12:16pm

Emmanuel Lacour wrote:

SQL> set autotrace on
SQL> CREATE INDEX Groups3 ON Groups (LOWER(‘Type’), LOWER(‘Domain’), Instance);
Index created.
SQL> execute dbms_utility.analyze_schema( ‘RT’, ‘estimate’);

Any particular reason to run ‘estimate’ instead of ‘compute’ ?

Further your output of Oracle10g looks almost identical/is identical to
the output of Oracle9. That sounds to me like a copy/paste problem
because that is not what I expect and contradicts your own statement
that Oracle10 is faster than Oracle9.

Joop

Emmanuel_Lacour · January 22, 2008, 1:49pm

Emmanuel Lacour wrote:

SQL> set autotrace on
SQL> CREATE INDEX Groups3 ON Groups (LOWER(‘Type’), LOWER(‘Domain’),
Instance);
Index created.
SQL> execute dbms_utility.analyze_schema( ‘RT’, ‘estimate’);

Any particular reason to run ‘estimate’ instead of ‘compute’ ?

Yes, I follow the rt README.Oracle and I wasn’t aware of “compute”

Further your output of Oracle10g looks almost identical/is identical to
the output of Oracle9. That sounds to me like a copy/paste problem
because that is not what I expect and contradicts your own statement
that Oracle10 is faster than Oracle9.

No It’s just because the explain plan I gave is not about oracle 10g, but about
the request on oracle 9 with the indexes proposed by Ruslan.

Here is the result from oracle 10g, first request took about 25 seconds, if I
run it a second time, less than 1 second:

First

Execution Plan
Plan hash value: 1378103328

| 0 | SELECT STATEMENT | | 1699 | 386K| | 718
9 (3)| 00:01:27 |

| 1 | SORT ORDER BY | | 1699 | 386K| 1064K| 718
9 (3)| 00:01:27 |

|* 2 | HASH JOIN | | 1699 | 386K| | 710
0 (3)| 00:01:26 |

| 3 | VIEW | | 1699 | 6796 | | 694
4 (3)| 00:01:24 |

| 4 | HASH UNIQUE | | 1699 | 127K| 65M| 694
4 (3)| 00:01:24 |

|* 5 | HASH JOIN | | 767K| 56M| | 199
2 (4)| 00:00:24 |

| 6 | INLIST ITERATOR | | | | |
| |

|* 7 | INDEX RANGE SCAN | ACL1 | 21 | 609 | |
3 (0)| 00:00:01 |

|* 8 | HASH JOIN | | 185K| 8715K| 4944K| 198
0 (4)| 00:00:24 |

|* 9 | TABLE ACCESS FULL | GROUPS | 136K| 3340K| | 45
5 (3)| 00:00:06 |

|* 10 | HASH JOIN | | 185K| 4176K| | 96
9 (5)| 00:00:12 |

|* 11 | HASH JOIN | | 16950 | 248K| | 27
9 (5)| 00:00:04 |

|* 12 | INDEX FAST FULL SCAN| USERS_KEY | 16950 | 67800 | | 1
1 (0)| 00:00:01 |

|* 13 | TABLE ACCESS FULL | PRINCIPALS | 72207 | 775K| | 26
6 (5)| 00:00:04 |

|* 14 | INDEX FAST FULL SCAN | GROUMEM | 789K| 6164K| | 67
9 (3)| 00:00:09 |

| 15 | TABLE ACCESS FULL | USERS | 16951 | 3790K| | 15
5 (2)| 00:00:02 |

Predicate Information (identified by operation id):

2 - access(“MAIN”.“ID”=“DISTINCTQUERY”.“ID”)
5 - access(“ACL_4”.“PRINCIPALTYPE”=“GROUPS_3”.“TYPE”)
7 - access(“ACL_4”.“RIGHTNAME”=‘OwnTicket’ AND (“ACL_4”.“OBJECTTYPE”=‘RT::Que
ue’ OR

      "ACL_4"."OBJECTTYPE"='RT::System'))

8 - access(“GROUPS_3”.“ID”=“CACHEDGROUPMEMBERS_2”.“GROUPID”)
9 - filter(“GROUPS_3”.“DOMAIN”=‘RT::Queue-Role’ OR
“GROUPS_3”.“DOMAIN”=‘RT::System-Role’)
10 - access(“CACHEDGROUPMEMBERS_2”.“MEMBERID”=“PRINCIPALS_1”.“ID”)
11 - access(“PRINCIPALS_1”.“ID”=“MAIN”.“ID”)
12 - filter(“MAIN”.“ID”<>1)
13 - filter(“PRINCIPALS_1”.“DISABLED”=0 AND “PRINCIPALS_1”.“PRINCIPALTYPE”=‘Us
er’ AND

      "PRINCIPALS_1"."ID"<>1)

14 - filter(“CACHEDGROUPMEMBERS_2”.“MEMBERID”<>1)

Statistics
0 recursive calls
0 db block gets
5001 consistent gets
2550 physical reads
0 redo size
2379 bytes sent via SQLNet to client
384 bytes received via SQLNet from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed

Second

Execution Plan
Plan hash value: 1378103328

| 0 | SELECT STATEMENT | | 1699 | 386K| | 718
9 (3)| 00:01:27 |

| 1 | SORT ORDER BY | | 1699 | 386K| 1064K| 718
9 (3)| 00:01:27 |

|* 2 | HASH JOIN | | 1699 | 386K| | 710
0 (3)| 00:01:26 |

| 3 | VIEW | | 1699 | 6796 | | 694
4 (3)| 00:01:24 |

| 4 | HASH UNIQUE | | 1699 | 127K| 65M| 694
4 (3)| 00:01:24 |

|* 5 | HASH JOIN | | 767K| 56M| | 199
2 (4)| 00:00:24 |

| 6 | INLIST ITERATOR | | | | |
| |

|* 7 | INDEX RANGE SCAN | ACL1 | 21 | 609 | |
3 (0)| 00:00:01 |

|* 8 | HASH JOIN | | 185K| 8715K| 4944K| 198
0 (4)| 00:00:24 |

|* 9 | TABLE ACCESS FULL | GROUPS | 136K| 3340K| | 45
5 (3)| 00:00:06 |

|* 10 | HASH JOIN | | 185K| 4176K| | 96
9 (5)| 00:00:12 |

|* 11 | HASH JOIN | | 16950 | 248K| | 27
9 (5)| 00:00:04 |

|* 12 | INDEX FAST FULL SCAN| USERS_KEY | 16950 | 67800 | | 1
1 (0)| 00:00:01 |

|* 13 | TABLE ACCESS FULL | PRINCIPALS | 72207 | 775K| | 26
6 (5)| 00:00:04 |

|* 14 | INDEX FAST FULL SCAN | GROUMEM | 789K| 6164K| | 67
9 (3)| 00:00:09 |

| 15 | TABLE ACCESS FULL | USERS | 16951 | 3790K| | 15
5 (2)| 00:00:02 |

Predicate Information (identified by operation id):

2 - access(“MAIN”.“ID”=“DISTINCTQUERY”.“ID”)
5 - access(“ACL_4”.“PRINCIPALTYPE”=“GROUPS_3”.“TYPE”)
7 - access(“ACL_4”.“RIGHTNAME”=‘OwnTicket’ AND (“ACL_4”.“OBJECTTYPE”=‘RT::Que
ue’ OR

      "ACL_4"."OBJECTTYPE"='RT::System'))

8 - access(“GROUPS_3”.“ID”=“CACHEDGROUPMEMBERS_2”.“GROUPID”)
9 - filter(“GROUPS_3”.“DOMAIN”=‘RT::Queue-Role’ OR
“GROUPS_3”.“DOMAIN”=‘RT::System-Role’)
10 - access(“CACHEDGROUPMEMBERS_2”.“MEMBERID”=“PRINCIPALS_1”.“ID”)
11 - access(“PRINCIPALS_1”.“ID”=“MAIN”.“ID”)
12 - filter(“MAIN”.“ID”<>1)
13 - filter(“PRINCIPALS_1”.“DISABLED”=0 AND “PRINCIPALS_1”.“PRINCIPALTYPE”=‘Us
er’ AND

      "PRINCIPALS_1"."ID"<>1)

14 - filter(“CACHEDGROUPMEMBERS_2”.“MEMBERID”<>1)

Statistics
0 recursive calls
0 db block gets
5001 consistent gets
0 physical reads
0 redo size
2379 bytes sent via SQLNet to client
384 bytes received via SQLNet from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed

Joop · January 22, 2008, 2:26pm

Emmanuel Lacour wrote:> On Tue, Jan 22, 2008 at 01:16:26PM +0100, Joop wrote:

Emmanuel Lacour wrote:

SQL> set autotrace on
SQL> CREATE INDEX Groups3 ON Groups (LOWER(‘Type’), LOWER(‘Domain’),
Instance);
Index created.
SQL> execute dbms_utility.analyze_schema( ‘RT’, ‘estimate’);

Any particular reason to run ‘estimate’ instead of ‘compute’ ?

Yes, I follow the rt README.Oracle and I wasn’t aware of “compute”

I tried both and with compute I get better results than estimate. Have a
look at the docs to see other options of analyze and with atleast Oracle
10g it is recommended to use DBMS_STATS.GATHER_SCHEMA_STATS or one of
its relatives. It has the possibility to keep the compute timewise
within bays. My VM took about 1min to analyze the whole schema and it
does so each morning at 6:00 am so noone is bothered by it.

Further my explain plan is different. I have 3 full tablescans twice of
USERS and once of PRINCIPALS and my cost is around 480 (This is Oracle XE)

The query send by Ruslan is slighly worse then the original cost is 482
instead of 480.

Joop

Emmanuel_Lacour · January 22, 2008, 6:35pm

Emmanuel Lacour wrote:

I tried both and with compute I get better results than estimate. Have a

I just did the same, on oracle 9 it divides the by 2

Thanks very much!

look at the docs to see other options of analyze and with atleast Oracle
10g it is recommended to use DBMS_STATS.GATHER_SCHEMA_STATS or one of
its relatives. It has the possibility to keep the compute timewise
within bays. My VM took about 1min to analyze the whole schema and it
does so each morning at 6:00 am so noone is bothered by it.

I will look deeper in those options…

Ruslan_Zakirov1 · February 24, 2008, 8:57pm

Hello, guys.

I’m reviewing this again after receiving new info from various sources.

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.
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:

replace all indexes that has LOWER(‘XXX’), list of indexes we
create by default is in etc/schema.Oracle
create index on Groups(Type, Domain, Instance) instead of one I
suggested before and explain query without any LOWER calls.On Mon, Jan 21, 2008 at 7:51 PM, Ruslan Zakirov ruz@bestpractical.com wrote:

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

Best regards, Ruslan.

Emmanuel_Lacour · February 27, 2008, 9:43am

Hello, guys.

Privet Ruslan

(and thanks for commiting my patches in svn :))

I’m reviewing this again after receiving new info from various sources.

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.

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:

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.

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:

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);

Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1489 Card=1 Bytes=256)

1 0 SORT (ORDER BY) (Cost=1489 Card=1 Bytes=256)
2 1 NESTED LOOPS (Cost=1488 Card=1 Bytes=256)
3 2 VIEW (Cost=1487 Card=1 Bytes=10)
4 3 SORT (UNIQUE) (Cost=1487 Card=1 Bytes=77)
5 4 HASH JOIN (Cost=1087 Card=45693 Bytes=3518361)
6 5 INLIST ITERATOR
7 6 INDEX (RANGE SCAN) OF ‘ACL1’ (NON-UNIQUE) (Cost=1 Card=24 Bytes=696)

8 5 NESTED LOOPS (Cost=1085 Card=15527 Bytes=745296)
9 8 HASH JOIN (Cost=542 Card=54344 Bytes=1249912)
10 9 NESTED LOOPS (Cost=15 Card=19856 Bytes=297840)

11 10 TABLE ACCESS (BY INDEX ROWID) OF ‘PRINCIPALS’ (Cost=14 Card=89822 Bytes=988042)

12 11 INDEX (RANGE SCAN) OF ‘FSHPRINCIPALS1’ (NON-UNIQUE) (Cost=335 Card=179644)

13 10 INDEX (UNIQUE SCAN) OF ‘USERS_KEY’ (UNIQUE)

14 9 INDEX (FULL SCAN) OF ‘GROUMEM’ (NON-UNIQUE) (Cost=3050 Card=983335 Bytes=7866680)

15 8 TABLE ACCESS (BY INDEX ROWID) OF ‘GROUPS’ (Cost=1 Card=1 Bytes=25)

16 15 INDEX (UNIQUE SCAN) OF ‘GROUPS_KEY’ (UNIQUE)
17 2 TABLE ACCESS (BY INDEX ROWID) OF ‘USERS’ (Cost=1 Card=1 Bytes=246)

18 17 INDEX (UNIQUE SCAN) OF ‘USERS_KEY’ (UNIQUE)

Statistics
223 recursive calls
0 db block gets
924231 consistent gets
35 physical reads
0 redo size
1249 bytes sent via SQLNet to client
275 bytes received via SQLNet from client
2 SQL*Net roundtrips to/from client
8 sorts (memory)
0 sorts (disk)
0 rows processed

I can enhance a little bit with the following index:

CREATE INDEX TEST1 ON CACHEDGROUPMEMBERS(MEMBERID);

Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1161 Card=1 Bytes=256)

1 0 SORT (ORDER BY) (Cost=1161 Card=1 Bytes=256)
2 1 NESTED LOOPS (Cost=1160 Card=1 Bytes=256)
3 2 VIEW (Cost=1159 Card=1 Bytes=10)
4 3 SORT (UNIQUE) (Cost=1159 Card=1 Bytes=77)
5 4 HASH JOIN (Cost=759 Card=45693 Bytes=3518361)
6 5 INLIST ITERATOR
7 6 INDEX (RANGE SCAN) OF ‘ACL1’ (NON-UNIQUE) (Cost=1 Card=24 Bytes=696)

8 5 NESTED LOOPS (Cost=757 Card=15527 Bytes=745296)
9 8 NESTED LOOPS (Cost=214 Card=54344 Bytes=1249912)

10 9 NESTED LOOPS (Cost=15 Card=19856 Bytes=297840)

11 10 TABLE ACCESS (BY INDEX ROWID) OF ‘PRINCIPALS’ (Cost=14 Card=89822 Bytes=988042)

12 11 INDEX (RANGE SCAN) OF ‘FSHPRINCIPALS1’ (NON-UNIQUE) (Cost=335 Card=179644)

13 10 INDEX (UNIQUE SCAN) OF ‘USERS_KEY’ (UNIQUE)

14 9 TABLE ACCESS (BY INDEX ROWID) OF ‘CACHEDGROUPMEMBERS’ (Cost=1 Card=3 Bytes=24)

15 14 INDEX (RANGE SCAN) OF ‘TEST1’ (NON-UNIQUE)
16 8 TABLE ACCESS (BY INDEX ROWID) OF ‘GROUPS’ (Cost=1 Card=1 Bytes=25)

17 16 INDEX (UNIQUE SCAN) OF ‘GROUPS_KEY’ (UNIQUE)
18 2 TABLE ACCESS (BY INDEX ROWID) OF ‘USERS’ (Cost=1 Card=1 Bytes=246)

19 18 INDEX (UNIQUE SCAN) OF ‘USERS_KEY’ (UNIQUE)

Statistics
0 recursive calls
0 db block gets
1091728 consistent gets
1580 physical reads
0 redo size
1235 bytes sent via SQLNet to client
275 bytes received via SQLNet from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed

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

Ruslan_Zakirov1 · February 27, 2008, 1:41pm

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.

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.

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:

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.

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.

Emmanuel_Lacour · February 27, 2008, 3:41pm

Privet.

I will be near request-tracker.ru on saturday, flying to Tomck

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

plan with stock RT indexes

Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4153 Card=19856 Bytes=5083136)

1 0 SORT (ORDER BY) (Cost=4153 Card=19856 Bytes=5083136)
2 1 MERGE JOIN (Cost=2200 Card=19856 Bytes=5083136)
3 2 TABLE ACCESS (BY INDEX ROWID) OF ‘USERS’ (Cost=33 Card=19857 Bytes=4884822)

4 3 INDEX (FULL SCAN) OF ‘USERS_KEY’ (UNIQUE) (Cost=50 Card=19857)

5 2 SORT (JOIN) (Cost=2168 Card=19856 Bytes=198560)
6 5 VIEW (Cost=2103 Card=19856 Bytes=198560)
7 6 SORT (UNIQUE) (Cost=2103 Card=19856 Bytes=1528912)
8 7 HASH JOIN (Cost=1189 Card=45693 Bytes=3518361)
9 8 INLIST ITERATOR
10 9 INDEX (RANGE SCAN) OF ‘ACL1’ (NON-UNIQUE) (Cost=1 Card=24 Bytes=696)

11 8 NESTED LOOPS (Cost=1187 Card=15527 Bytes=745296)

12 11 HASH JOIN (Cost=644 Card=54344 Bytes=1249912

13 12 NESTED LOOPS (Cost=117 Card=19856 Bytes=297840)

14 13 TABLE ACCESS (FULL) OF ‘PRINCIPALS’ (Cost=116 Card=89822 Bytes=988042)

15 13 INDEX (UNIQUE SCAN) OF ‘USERS_KEY’ (UNIQUE)

16 12 INDEX (FULL SCAN) OF ‘GROUMEM’ (NON-UNIQUE) (Cost=3050 Card=983335 Bytes=7866680)

17 11 TABLE ACCESS (BY INDEX ROWID) OF ‘GROUPS’ (Cost=1 Card=1 Bytes=25)

18 17 INDEX (UNIQUE SCAN) OF ‘GROUPS_KEY’ (UNIQUE)

Statistics
636 recursive calls
0 db block gets
923745 consistent gets
322 physical reads
0 redo size
1249 bytes sent via SQLNet to client
275 bytes received via SQLNet from client
2 SQL*Net roundtrips to/from client
21 sorts (memory)
0 sorts (disk)
0 rows processed

we clearly need index on Principals, si plan with the folowing index:

CREATE INDEX FSHPRINCIPALS1 ON PRINCIPALS (DISABLED);

Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1480 Card=1 Bytes=256)

1 0 SORT (ORDER BY) (Cost=1480 Card=1 Bytes=256)
2 1 NESTED LOOPS (Cost=1478 Card=1 Bytes=256)
3 2 VIEW (Cost=1477 Card=1 Bytes=10)
4 3 SORT (UNIQUE) (Cost=1477 Card=1 Bytes=77)
5 4 HASH JOIN (Cost=1078 Card=45693 Bytes=3518361)
6 5 INLIST ITERATOR
7 6 INDEX (RANGE SCAN) OF ‘ACL1’ (NON-UNIQUE) (Cost=1 Card=24 Bytes=696)

8 5 NESTED LOOPS (Cost=1076 Card=15527 Bytes=745296)
9 8 HASH JOIN (Cost=532 Card=54344 Bytes=1249912)
10 9 NESTED LOOPS (Cost=6 Card=19856 Bytes=297840)

11 10 TABLE ACCESS (BY INDEX ROWID) OF ‘PRINCIPALS’ (Cost=5 Card=89822 Bytes=988042)

12 11 INDEX (RANGE SCAN) OF ‘FSHPRINCIPALS1’ (NON-UNIQUE) (Cost=13 Card=179644)

13 10 INDEX (UNIQUE SCAN) OF ‘USERS_KEY’ (UNIQUE)

14 9 INDEX (FULL SCAN) OF ‘GROUMEM’ (NON-UNIQUE) (Cost=3050 Card=983335 Bytes=7866680)

15 8 TABLE ACCESS (BY INDEX ROWID) OF ‘GROUPS’ (Cost=1 Card=1 Bytes=25)

16 15 INDEX (UNIQUE SCAN) OF ‘GROUPS_KEY’ (UNIQUE)
17 2 TABLE ACCESS (BY INDEX ROWID) OF ‘USERS’ (Cost=1 Card=1 Bytes=246)

18 17 INDEX (UNIQUE SCAN) OF ‘USERS_KEY’ (UNIQUE)

Statistics
0 recursive calls
0 db block gets
924164 consistent gets
676 physical reads
0 redo size
1249 bytes sent via SQLNet to client
275 bytes received via SQLNet from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed

plan with TEST1

Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1152 Card=1 Bytes=256)

1 0 SORT (ORDER BY) (Cost=1152 Card=1 Bytes=256)
2 1 NESTED LOOPS (Cost=1150 Card=1 Bytes=256)
3 2 VIEW (Cost=1149 Card=1 Bytes=10)
4 3 SORT (UNIQUE) (Cost=1149 Card=1 Bytes=77)
5 4 HASH JOIN (Cost=750 Card=45693 Bytes=3518361)
6 5 INLIST ITERATOR
7 6 INDEX (RANGE SCAN) OF ‘ACL1’ (NON-UNIQUE) (Cost=1 Card=24 Bytes=696)

8 5 NESTED LOOPS (Cost=748 Card=15527 Bytes=745296)
9 8 NESTED LOOPS (Cost=204 Card=54344 Bytes=1249912)

10 9 NESTED LOOPS (Cost=6 Card=19856 Bytes=297840)

11 10 TABLE ACCESS (BY INDEX ROWID) OF ‘PRINCIPALS’ (Cost=5 Card=89822 Bytes=988042)

12 11 INDEX (RANGE SCAN) OF ‘FSHPRINCIPALS1’ (NON-UNIQUE) (Cost=13 Card=179644)

13 10 INDEX (UNIQUE SCAN) OF ‘USERS_KEY’ (UNIQUE)

14 9 TABLE ACCESS (BY INDEX ROWID) OF ‘CACHEDGROUPMEMBERS’ (Cost=1 Card=3 Bytes=24)

15 14 INDEX (RANGE SCAN) OF ‘TEST1’ (NON-UNIQUE)
16 8 TABLE ACCESS (BY INDEX ROWID) OF ‘GROUPS’ (Cost=1 Card=1 Bytes=25)

17 16 INDEX (UNIQUE SCAN) OF ‘GROUPS_KEY’ (UNIQUE)
18 2 TABLE ACCESS (BY INDEX ROWID) OF ‘USERS’ (Cost=1 Card=1 Bytes=246)

19 18 INDEX (UNIQUE SCAN) OF ‘USERS_KEY’ (UNIQUE)

Statistics
0 recursive calls
0 db block gets
1091717 consistent gets
1580 physical reads
0 redo size
1249 bytes sent via SQLNet to client
275 bytes received via SQLNet from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed

plan with TEST2 instead of TEST1

Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1152 Card=1 Bytes=256)

1 0 SORT (ORDER BY) (Cost=1152 Card=1 Bytes=256)
2 1 NESTED LOOPS (Cost=1150 Card=1 Bytes=256)
3 2 VIEW (Cost=1149 Card=1 Bytes=10)
4 3 SORT (UNIQUE) (Cost=1149 Card=1 Bytes=77)
5 4 HASH JOIN (Cost=750 Card=45693 Bytes=3518361)
6 5 INLIST ITERATOR
7 6 INDEX (RANGE SCAN) OF ‘ACL1’ (NON-UNIQUE) (Cost=1 Card=24 Bytes=696)

8 5 NESTED LOOPS (Cost=748 Card=15527 Bytes=745296)
9 8 NESTED LOOPS (Cost=204 Card=54344 Bytes=1249912)

10 9 NESTED LOOPS (Cost=6 Card=19856 Bytes=297840)

11 10 TABLE ACCESS (BY INDEX ROWID) OF ‘PRINCIPALS’ (Cost=5 Card=89822 Bytes=988042)

12 11 INDEX (RANGE SCAN) OF ‘FSHPRINCIPALS1’ (NON-UNIQUE) (Cost=13 Card=179644)

13 10 INDEX (UNIQUE SCAN) OF ‘USERS_KEY’ (UNIQUE)

14 9 INDEX (RANGE SCAN) OF ‘TEST2’ (NON-UNIQUE) (Cost=1 Card=3 Bytes=24)

15 8 TABLE ACCESS (BY INDEX ROWID) OF ‘GROUPS’ (Cost=1 Card=1 Bytes=25)

16 15 INDEX (UNIQUE SCAN) OF ‘GROUPS_KEY’ (UNIQUE)
17 2 TABLE ACCESS (BY INDEX ROWID) OF ‘USERS’ (Cost=1 Card=1 Bytes=246)

18 17 INDEX (UNIQUE SCAN) OF ‘USERS_KEY’ (UNIQUE)

Statistics
47 recursive calls
0 db block gets
949255 consistent gets
1969 physical reads
0 redo size
1249 bytes sent via SQLNet to client
275 bytes received via SQLNet from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed

plan with CGM_FINAL instead of TEST1/TEST2

Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1152 Card=1 Bytes=256)

1 0 SORT (ORDER BY) (Cost=1152 Card=1 Bytes=256)
2 1 NESTED LOOPS (Cost=1150 Card=1 Bytes=256)
3 2 VIEW (Cost=1149 Card=1 Bytes=10)
4 3 SORT (UNIQUE) (Cost=1149 Card=1 Bytes=77)
5 4 HASH JOIN (Cost=750 Card=45693 Bytes=3518361)
6 5 INLIST ITERATOR
7 6 INDEX (RANGE SCAN) OF ‘ACL1’ (NON-UNIQUE) (Cost=1 Card=24 Bytes=696)

8 5 NESTED LOOPS (Cost=748 Card=15527 Bytes=745296)
9 8 NESTED LOOPS (Cost=204 Card=54344 Bytes=1249912)

10 9 NESTED LOOPS (Cost=6 Card=19856 Bytes=297840)

11 10 TABLE ACCESS (BY INDEX ROWID) OF ‘PRINCIPALS’ (Cost=5 Card=89822 Bytes=988042)

12 11 INDEX (RANGE SCAN) OF ‘FSHPRINCIPALS1’ (NON-UNIQUE) (Cost=13 Card=179644)

13 10 INDEX (UNIQUE SCAN) OF ‘USERS_KEY’ (UNIQUE)

14 9 INDEX (RANGE SCAN) OF ‘CGM_FINAL’ (NON-UNIQUE) (Cost=1 Card=3 Bytes=24)

15 8 TABLE ACCESS (BY INDEX ROWID) OF ‘GROUPS’ (Cost=1 Card=1 Bytes=25)

16 15 INDEX (UNIQUE SCAN) OF ‘GROUPS_KEY’ (UNIQUE)
17 2 TABLE ACCESS (BY INDEX ROWID) OF ‘USERS’ (Cost=1 Card=1 Bytes=246)

18 17 INDEX (UNIQUE SCAN) OF ‘USERS_KEY’ (UNIQUE)

Statistics
47 recursive calls
0 db block gets
949387 consistent gets
2129 physical reads
0 redo size
1249 bytes sent via SQLNet to client
275 bytes received via SQLNet from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed

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.

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

Ruslan_Zakirov1 · March 7, 2008, 12:49pm

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?

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);

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.