SetOwner speed

RT Developers
1

Hello!

Tracing why take ticked (from nobody) operation take so long time
we found that mostly cost operation is call SetOwner() (from Take()) .
And most cost operation on SetOwner id

my ( $del_id, $del_msg ) = $self->OwnerGroup->MembersObj->First->Delete();

Can some body explain for what this is needed?

WBR, Alexey G Misyurenko ( MAG-RIPE | MMAGG-RIPN )
CTO of Caravan ISP http://www.caravan.ru
Phone: +7 095 3632252 Cell: +7 095 5082794

2

Hello!

Tracing why take ticked (from nobody) operation take so long time
we found that mostly cost operation is call SetOwner() (from Take()) .
And most cost operation on SetOwner id

my ( $del_id, $del_msg ) = $self->OwnerGroup->MembersObj->First->Delete();

Because the Owner group is used for ACL decisions on the owner role.

3

Alexey G Misyurenko wrote:

Hello!

Tracing why take ticked (from nobody) operation take so long time
we found that mostly cost operation is call SetOwner() (from Take()) .
And most cost operation on SetOwner id

my ( $del_id, $del_msg ) =
$self->OwnerGroup->MembersObj->First->Delete();

Can some body explain for what this is needed?

And can this call (… First->Delete() ) be omitted when cureent owner
is ‘Nobody’
and new owner is not ‘Nobody’

WBR, Alexey G Misyurenko ( MAG-RIPE | MMAGG-RIPN )
CTO of Caravan ISP http://www.caravan.ru
Phone: +7 095 3632252 Cell: +7 095 5082794

4

Hello!

Tracing why take ticked (from nobody) operation take so long time
we found that mostly cost operation is call SetOwner() (from Take()) .
And most cost operation on SetOwner id

my ( $del_id, $del_msg ) = $self->OwnerGroup->MembersObj->First->Delete();

Can some body explain for what this is needed?
Yep, there is could be only one owner for a ticket. You want to delete
previouse member from the ticket owner group.

–
WBR, Alexey G Misyurenko ( MAG-RIPE | MMAGG-RIPN )
CTO of Caravan ISP http://www.caravan.ru
Phone: +7 095 3632252 Cell: +7 095 5082794

List info: The rt-devel Archives

Best Practical is hiring! Come hack Perl for us: Careers — Best Practical Solutions

Best regards, Ruslan.

5

Alexey G Misyurenko wrote:

Hello!

Tracing why take ticked (from nobody) operation take so long time
we found that mostly cost operation is call SetOwner() (from Take()) .
And most cost operation on SetOwner id

my ( $del_id, $del_msg ) =
$self->OwnerGroup->MembersObj->First->Delete();

Can some body explain for what this is needed?

And can this call (… First->Delete() ) be omitted when cureent owner
is ‘Nobody’
and new owner is not ‘Nobody’
no, as nobody is also user :slight_smile:

–
WBR, Alexey G Misyurenko ( MAG-RIPE | MMAGG-RIPN )
CTO of Caravan ISP http://www.caravan.ru
Phone: +7 095 3632252 Cell: +7 095 5082794

List info: The rt-devel Archives

Best Practical is hiring! Come hack Perl for us: Careers — Best Practical Solutions

Best regards, Ruslan.

6

Ruslan Zakirov wrote:> On 6/15/06, Alexey G Misyurenko mag@caravan.ru wrote:

Alexey G Misyurenko wrote:

Hello!

Tracing why take ticked (from nobody) operation take so long time
we found that mostly cost operation is call SetOwner() (from Take()) .
And most cost operation on SetOwner id

my ( $del_id, $del_msg ) =
$self->OwnerGroup->MembersObj->First->Delete();

Can some body explain for what this is needed?

And can this call (… First->Delete() ) be omitted when cureent owner
is ‘Nobody’
and new owner is not ‘Nobody’
no, as nobody is also user :slight_smile:

Ok.

May be is possible to just UPDATE owner of ticked
not DELETE and INSTERT?

WBR, Alexey G Misyurenko ( MAG-RIPE | MMAGG-RIPN )
CTO of Caravan ISP http://www.caravan.ru
Phone: +7 095 3632252 Cell: +7 095 5082794

7

Ruslan Zakirov wrote:

Alexey G Misyurenko wrote:

Hello!

Tracing why take ticked (from nobody) operation take so long time
we found that mostly cost operation is call SetOwner() (from Take()) .
And most cost operation on SetOwner id

my ( $del_id, $del_msg ) =
$self->OwnerGroup->MembersObj->First->Delete();

Can some body explain for what this is needed?

And can this call (… First->Delete() ) be omitted when cureent owner
is ‘Nobody’
and new owner is not ‘Nobody’
no, as nobody is also user :slight_smile:

Ok.

May be is possible to just UPDATE owner of ticked
not DELETE and INSTERT?
No. Can you grab list of queries that RT executes while this operation?

–
WBR, Alexey G Misyurenko ( MAG-RIPE | MMAGG-RIPN )
CTO of Caravan ISP http://www.caravan.ru
Phone: +7 095 3632252 Cell: +7 095 5082794

List info: The rt-devel Archives

Best Practical is hiring! Come hack Perl for us: Careers — Best Practical Solutions

Best regards, Ruslan.

8

Ruslan Zakirov wrote:> On 6/15/06, Alexey G Misyurenko mag@caravan.ru wrote:

Ruslan Zakirov wrote:

On 6/15/06, Alexey G Misyurenko mag@caravan.ru wrote:

Alexey G Misyurenko wrote:

Hello!

Tracing why take ticked (from nobody) operation take so long time
we found that mostly cost operation is call SetOwner() (from
Take()) .
And most cost operation on SetOwner id

my ( $del_id, $del_msg ) =
$self->OwnerGroup->MembersObj->First->Delete();

Can some body explain for what this is needed?

And can this call (… First->Delete() ) be omitted when cureent
owner
is ‘Nobody’
and new owner is not ‘Nobody’
no, as nobody is also user :slight_smile:

Ok.

May be is possible to just UPDATE owner of ticked
not DELETE and INSTERT?
No. Can you grab list of queries that RT executes while this operation?
I try and send is as soon as posible.

WBR, Alexey G Misyurenko ( MAG-RIPE | MMAGG-RIPN )
CTO of Caravan ISP http://www.caravan.ru
Phone: +7 095 3632252 Cell: +7 095 5082794

9

Ruslan Zakirov wrote:

Tracing why take ticked (from nobody) operation take so long time

[skip]

And can this call (… First->Delete() ) be omitted when cureent
owner
is ‘Nobody’
and new owner is not ‘Nobody’
no, as nobody is also user :slight_smile:

Ok.

May be is possible to just UPDATE owner of ticked
not DELETE and INSTERT?
No. Can you grab list of queries that RT executes while this operation?

Sorry, have a bit problems with grabbing SQL request
but I trace next time costly call

in file lib/RT/GroupMember_Overlay.pm

$err = $self->MemberObj->_CleanupInvalidDelegations(InsideTransaction => 1)

WBR, Alexey G Misyurenko ( MAG-RIPE | MMAGG-RIPN )
CTO of Caravan ISP http://www.caravan.ru
Phone: +7 095 3632252 Cell: +7 095 5082794

10

Alexey G Misyurenko wrote:

Ruslan Zakirov wrote:

Tracing why take ticked (from nobody) operation take so long time

[skip]

And can this call (… First->Delete() ) be omitted when cureent
owner
is ‘Nobody’
and new owner is not ‘Nobody’
no, as nobody is also user :slight_smile:

Ok.

May be is possible to just UPDATE owner of ticked
not DELETE and INSTERT?
No. Can you grab list of queries that RT executes while this operation?

Sorry, have a bit problems with grabbing SQL request
but I trace next time costly call

in file lib/RT/GroupMember_Overlay.pm

$err = $self->MemberObj->_CleanupInvalidDelegations(InsideTransaction
=> 1)

I modify GroupMember_Overlay.pm to

$RT::Handle->LogSQLStatements(1);
$RT::Handle->ClearSQLStatementLog;

$err =

$self->MemberObj->_CleanupInvalidDelegations(InsideTransaction => 1);

my @log = $RT::Handle->SQLStatementLog;
$RT::Handle->ClearSQLStatementLog;
$RT::Handle->LogSQLStatements(0);

And sniff next SQLs

== query id: 1
time: 1150323765.6947
duration: 0.000481843948364258
sql is (SELECT * FROM Principals WHERE lower(PrincipalType) = ? AND
ObjectId = ?)

== query id: 2
time: 1150323797.4394
duration: 31.7433941364288

sql is (SELECT * FROM ( SELECT limitquery.*,rownum limitrownum FROM (
SELECT ACL.id from ACL, Groups, Principals, CachedGroupMembers WHERE
(ACL.RightName = ‘SuperUser’ OR ACL.RightName = ‘DelegateRights’) AND
Principals.Disabled = 0 AND CachedGroupMembers.Disabled = 0 AND
Principals.id = Groups.id AND Principals.PrincipalType = ‘Group’ AND
Principals.id = CachedGroupMembers.GroupId AND
CachedGroupMembers.MemberId = 10 AND ((ACL.ObjectType = ‘RT::System’ AND
ACL.ObjectId = 1) OR (ACL.ObjectType = ‘RT::System’)) AND
ACL.PrincipalId = Principals.id AND ACL.PrincipalType = ‘Group’ )
limitquery WHERE rownum <= 1 ) WHERE limitrownum >= 1)

== query id: 3
time: 1150323808.1467
duration: 10.7063798904419
sql is (SELECT * FROM ( SELECT limitquery.*,rownum limitrownum FROM (
SELECT ACL.id from ACL, Groups, Principals, CachedGroupMembers WHERE
(ACL.RightName = ‘SuperUser’ OR ACL.RightName = ‘DelegateRights’) AND
Principals.Disabled = 0 AND CachedGroupMembers.Disabled = 0 AND
Principals.id = Groups.id AND Principals.PrincipalType = ‘Group’ AND
Principals.id = CachedGroupMembers.GroupId AND
CachedGroupMembers.MemberId = 10 AND ((ACL.ObjectType = ‘RT::System’ AND
ACL.ObjectId = 1) OR (ACL.ObjectType = ‘RT::System’)) AND
ACL.PrincipalType = Groups.Type AND ((Groups.Domain = ‘RT::System-Role’
AND Groups.Instance = ‘1’) OR (Groups.Domain = ‘RT::System-Role’)) )
limitquery WHERE rownum <= 1 ) WHERE limitrownum >= 1)

== query id: 4
time: 1150323815.5475
duration: 7.39867401123047
sql is (SELECT main.* FROM ( SELECT DISTINCT main.id FROM ACL main ,
CachedGroupMembers CachedGroupMembers_1 WHERE
((CachedGroupMembers_1.MemberId = ‘10’)) AND ((main.PrincipalId =
CachedGroupMembers_1.GroupId)) AND ((main.RightName =
‘DelegateRights’)) ) distinctquery, ACL main WHERE (main.id =
distinctquery.id) )

== query id: 5
time: 1150323815.55046
duration: 0.000711202621459961
sql is (SELECT main.* FROM ACL main WHERE ((main.DelegatedBy = ‘10’)) )

WBR, Alexey G Misyurenko ( MAG-RIPE | MMAGG-RIPN )
CTO of Caravan ISP http://www.caravan.ru
Phone: +7 095 3632252 Cell: +7 095 5082794

11

Ok, I think index on CachedGroupMembers(MemberId, GroupId, Disabled)
should help you with query 4, but to help you with other queries I
need to see EXPLAINs…

Please, make explains before you’ll add index and after.

Alexey G Misyurenko wrote:

Ruslan Zakirov wrote:

Tracing why take ticked (from nobody) operation take so long time

[skip]

And can this call (… First->Delete() ) be omitted when cureent
owner
is ‘Nobody’
and new owner is not ‘Nobody’
no, as nobody is also user :slight_smile:

Ok.

May be is possible to just UPDATE owner of ticked
not DELETE and INSTERT?
No. Can you grab list of queries that RT executes while this operation?

Sorry, have a bit problems with grabbing SQL request
but I trace next time costly call

in file lib/RT/GroupMember_Overlay.pm

$err = $self->MemberObj->_CleanupInvalidDelegations(InsideTransaction
=> 1)

I modify GroupMember_Overlay.pm to

$RT::Handle->LogSQLStatements(1);
$RT::Handle->ClearSQLStatementLog;

$err =

$self->MemberObj->_CleanupInvalidDelegations(InsideTransaction => 1);

my @log = $RT::Handle->SQLStatementLog;
$RT::Handle->ClearSQLStatementLog;
$RT::Handle->LogSQLStatements(0);

And sniff next SQLs

[snip]

== query id: 2
time: 1150323797.4394
duration: 31.7433941364288

sql is (SELECT * FROM ( SELECT limitquery.*,rownum limitrownum FROM (
SELECT ACL.id from ACL, Groups, Principals, CachedGroupMembers WHERE
(ACL.RightName = ‘SuperUser’ OR ACL.RightName = ‘DelegateRights’) AND
Principals.Disabled = 0 AND CachedGroupMembers.Disabled = 0 AND
Principals.id = Groups.id AND Principals.PrincipalType = ‘Group’ AND
Principals.id = CachedGroupMembers.GroupId AND
CachedGroupMembers.MemberId = 10 AND ((ACL.ObjectType = ‘RT::System’ AND
ACL.ObjectId = 1) OR (ACL.ObjectType = ‘RT::System’)) AND
ACL.PrincipalId = Principals.id AND ACL.PrincipalType = ‘Group’ )
limitquery WHERE rownum <= 1 ) WHERE limitrownum >= 1)

== query id: 3
time: 1150323808.1467
duration: 10.7063798904419
sql is (SELECT * FROM ( SELECT limitquery.*,rownum limitrownum FROM (
SELECT ACL.id from ACL, Groups, Principals, CachedGroupMembers WHERE
(ACL.RightName = ‘SuperUser’ OR ACL.RightName = ‘DelegateRights’) AND
Principals.Disabled = 0 AND CachedGroupMembers.Disabled = 0 AND
Principals.id = Groups.id AND Principals.PrincipalType = ‘Group’ AND
Principals.id = CachedGroupMembers.GroupId AND
CachedGroupMembers.MemberId = 10 AND ((ACL.ObjectType = ‘RT::System’ AND
ACL.ObjectId = 1) OR (ACL.ObjectType = ‘RT::System’)) AND
ACL.PrincipalType = Groups.Type AND ((Groups.Domain = ‘RT::System-Role’
AND Groups.Instance = ‘1’) OR (Groups.Domain = ‘RT::System-Role’)) )
limitquery WHERE rownum <= 1 ) WHERE limitrownum >= 1)

== query id: 4
time: 1150323815.5475
duration: 7.39867401123047
sql is (SELECT main.* FROM ( SELECT DISTINCT main.id FROM ACL main ,
CachedGroupMembers CachedGroupMembers_1 WHERE
((CachedGroupMembers_1.MemberId = ‘10’)) AND ((main.PrincipalId =
CachedGroupMembers_1.GroupId)) AND ((main.RightName =
‘DelegateRights’)) ) distinctquery, ACL main WHERE (main.id =
distinctquery.id) )

[snip]
Best regards, Ruslan.

12

Ruslan Zakirov wrote:

Ruslan Zakirov wrote:

Ok, I think index on CachedGroupMembers(MemberId, GroupId, Disabled)
should help you with query 4, but to help you with other queries I
need to see EXPLAINs…

Sorry for the pause with answer - second oracle installation take a time…

Please, make explains before you’ll add index and after.

WITH INDEX
SQL Statement from editor:

SELECT *
FROM (SELECT limitquery.*, ROWNUM limitrownum
FROM (SELECT acl.ID
FROM acl, GROUPS, principals, cachedgroupmembers
WHERE ( acl.rightname = ‘SuperUser’
OR acl.rightname = ‘DelegateRights’
)
AND principals.disabled = 0
AND cachedgroupmembers.disabled = 0
AND principals.ID = GROUPS.ID
AND principals.principaltype = ‘Group’
AND principals.ID = cachedgroupmembers.groupid
AND cachedgroupmembers.memberid = 10
AND ( (acl.objecttype = ‘RT::System’ AND
acl.objectid = 1
)
OR (acl.objecttype = ‘RT::System’)
)
AND acl.principalid = principals.ID
AND acl.principaltype = ‘Group’) limitquery
WHERE ROWNUM <= 1)
WHERE limitrownum >= 1

Statement Id=4203132 Type=
Cost=2,64017018715267E-308 TimeStamp=17-06-06::00::53:49

   (1)  SELECT STATEMENT  CHOOSE
 Est. Rows: 1  Cost: 9
   (13)  VIEW (Embedded SQL)
 Est. Rows: 1  Cost: 9
       (12)  COUNT STOPKEY
           (11)  TABLE ACCESS BY INDEX ROWID RTADMIN.ACL  [Analyzed]
           (11)   Blocks: 13 Est. Rows: 1 of 1 086  Cost: 2
                Tablespace: USERS
               (10)  NESTED LOOPS
                    Est. Rows: 1  Cost: 9
                   (8)  NESTED LOOPS
                        Est. Rows: 1  Cost: 7
                       (6)  NESTED LOOPS
                            Est. Rows: 1  Cost: 6
                           (3)  TABLE ACCESS BY INDEX ROWID

RTADMIN.CACHEDGROUPMEMBERS [Analyzed]
(3) Blocks: 21 317 Est. Rows: 1 of
4 538 707 Cost: 4
Tablespace: USERS
(2) NON-UNIQUE INDEX RANGE SCAN
RTADMIN.CACHEDGR_NDX [Analyzed]
Est. Rows: 1 Cost: 3
(5) TABLE ACCESS BY INDEX ROWID
RTADMIN.PRINCIPALS [Analyzed]
(5) Blocks: 7 174 Est. Rows: 1 of
2 164 662 Cost: 2
Tablespace: USERS
(4) UNIQUE INDEX UNIQUE SCAN
RTADMIN.PRINCIPALS_KEY [Analyzed]
Est. Rows: 1 Cost: 1
(7) UNIQUE INDEX UNIQUE SCAN
RTADMIN.GROUPS_KEY [Analyzed]
Est. Rows: 1 Cost: 1
(9) NON-UNIQUE INDEX RANGE SCAN
RTADMIN.QUEST_SX_D297E41F3CD1F7F [Not Analyzed]
Est. Rows: 30 Cost: 1

WITHOUT INDEX

SQL Statement from editor:

SELECT *
FROM (SELECT limitquery.*, ROWNUM limitrownum
FROM (SELECT acl.ID
FROM acl, GROUPS, principals, cachedgroupmembers
WHERE ( acl.rightname = ‘SuperUser’
OR acl.rightname = ‘DelegateRights’
)
AND principals.disabled = 0
AND cachedgroupmembers.disabled = 0
AND principals.ID = GROUPS.ID
AND principals.principaltype = ‘Group’
AND principals.ID = cachedgroupmembers.groupid
AND cachedgroupmembers.memberid = 10
AND ( (acl.objecttype = ‘RT::System’ AND
acl.objectid = 1
)
OR (acl.objecttype = ‘RT::System’)
)
AND acl.principalid = principals.ID
AND acl.principaltype = ‘Group’) limitquery
WHERE ROWNUM <= 1)
WHERE limitrownum >= 1

Statement Id=1460648 Type=
Cost=2,64017018715267E-308 TimeStamp=17-06-06::01::02:32

   (1)  SELECT STATEMENT  CHOOSE
 Est. Rows: 1  Cost: 9
   (13)  VIEW (Embedded SQL)
 Est. Rows: 1  Cost: 9
       (12)  COUNT STOPKEY
           (11)  TABLE ACCESS BY INDEX ROWID RTADMIN.ACL  [Analyzed]
           (11)   Blocks: 13 Est. Rows: 1 of 1 086  Cost: 2
                Tablespace: USERS
               (10)  NESTED LOOPS
                    Est. Rows: 1  Cost: 9
                   (8)  NESTED LOOPS
                        Est. Rows: 1  Cost: 7
                       (6)  NESTED LOOPS
                            Est. Rows: 1  Cost: 6
                           (3)  TABLE ACCESS BY INDEX ROWID

RTADMIN.CACHEDGROUPMEMBERS [Analyzed]
(3) Blocks: 21 317 Est. Rows: 1 of
4 538 707 Cost: 4
Tablespace: USERS
(2) NON-UNIQUE INDEX RANGE SCAN
RTADMIN.CACHEDGR_NDX [Analyzed]
Est. Rows: 1 Cost: 3
(5) TABLE ACCESS BY INDEX ROWID
RTADMIN.PRINCIPALS [Analyzed]
(5) Blocks: 7 174 Est. Rows: 1 of
2 164 662 Cost: 2
Tablespace: USERS
(4) UNIQUE INDEX UNIQUE SCAN
RTADMIN.PRINCIPALS_KEY [Analyzed]
Est. Rows: 1 Cost: 1
(7) UNIQUE INDEX UNIQUE SCAN
RTADMIN.GROUPS_KEY [Analyzed]
Est. Rows: 1 Cost: 1
(9) NON-UNIQUE INDEX RANGE SCAN
RTADMIN.QUEST_SX_D297E41F3CD1F7F [Not Analyzed]
Est. Rows: 30 Cost: 1

WBR, Alexey G Misyurenko ( MAG-RIPE | MMAGG-RIPN )
CTO of Caravan ISP http://www.caravan.ru
Phone: +7 095 3632252 Cell: +7 095 5082794

13

Alexey G Misyurenko wrote:

Ruslan Zakirov wrote:

Ruslan Zakirov wrote:

Ok, I think index on CachedGroupMembers(MemberId, GroupId, Disabled)
should help you with query 4, but to help you with other queries I
need to see EXPLAINs…

Sorry for the pause with answer - second oracle installation take a
time…

Please, make explains before you’ll add index and after.

WITH INDEX

SQL Statement from editor:

SQL Tunning toolkit for oracle just finish analise what can be done with
original
SQL request generated by RT .

SELECT *
FROM (SELECT limitquery.*, ROWNUM limitrownum
FROM (SELECT acl.ID
FROM acl, GROUPS, principals, cachedgroupmembers
WHERE ( acl.rightname = ‘SuperUser’
OR acl.rightname = ‘DelegateRights’
)
AND principals.disabled = 0
AND cachedgroupmembers.disabled = 0
AND principals.ID = GROUPS.ID
AND principals.principaltype = ‘Group’
AND principals.ID = cachedgroupmembers.groupid
AND cachedgroupmembers.memberid = 10
AND ( (acl.objecttype = ‘RT::System’ AND
acl.objectid = 1
)
OR (acl.objecttype = ‘RT::System’)
)
AND acl.principalid = principals.ID
AND acl.principaltype = ‘Group’) limitquery
WHERE ROWNUM <= 1)
WHERE limitrownum >= 1

which is run 9,353s, and propose next SQL edition

SELECT /+ PARALLEL(, 2) /
FROM (SELECT /+ PARALLEL(LIMITQUERY, 2) /
limitquery., ROWNUM limitrownum
FROM (SELECT /+ PARALLEL(CACHEDGROUPMEMBERS, 2) */
acl.ID
FROM acl, GROUPS, principals, cachedgroupmembers
WHERE 0 = principals.disabled
AND 0 = cachedgroupmembers.disabled
AND GROUPS.ID = principals.ID
AND ‘Group’ = principals.principaltype
AND cachedgroupmembers.groupid = principals.ID
AND 10 = cachedgroupmembers.memberid
AND principals.ID = acl.principalid
AND ‘Group’ = acl.principaltype
AND principals.disabled = cachedgroupmembers.disabled
AND cachedgroupmembers.groupid = GROUPS.ID
AND acl.principalid = GROUPS.ID
AND principals.principaltype = acl.principaltype
AND acl.principalid = cachedgroupmembers.groupid
AND ( ‘RT::System’ = acl.objecttype AND 1 =
acl.objectid
OR ‘RT::System’ = acl.objecttype
)
AND ( ‘SuperUser’ = acl.rightname
OR ‘DelegateRights’ = acl.rightname
)) limitquery
WHERE 1 >= ROWNUM)
WHERE 1 <= limitrownum

which take 5.391s to run.

WBR, Alexey G Misyurenko ( MAG-RIPE | MMAGG-RIPN )
CTO of Caravan ISP http://www.caravan.ru
Phone: +7 095 3632252 Cell: +7 095 5082794

14

I spotted "AND ( (acl.objecttype = ‘RT::System’ AND acl.objectid =

  1. OR (acl.objecttype = ‘RT::System’) )" which is really wierd and
    should be fixed in RT code. This should be just “AND (acl.objecttype =
    ‘RT::System’ AND acl.objectid = 1)”, without OR … part, but I don’t
    think this change would give much speed improvement.

Also, I didn’t know you’re using Oracle. I can’t read its explains,
sorry. From oracle’s docs I see that it has output formats of explains
which have numbers of rows oracle scans. Such explain could be more
informative.On 6/17/06, Alexey G Misyurenko mag@caravan.ru wrote:

Alexey G Misyurenko wrote:

Ruslan Zakirov wrote:

On 6/15/06, Alexey G Misyurenko mag@caravan.ru wrote:

Ruslan Zakirov wrote:

Ok, I think index on CachedGroupMembers(MemberId, GroupId, Disabled)
should help you with query 4, but to help you with other queries I
need to see EXPLAINs…

Sorry for the pause with answer - second oracle installation take a
time…

Please, make explains before you’ll add index and after.

WITH INDEX

SQL Statement from editor:

SQL Tunning toolkit for oracle just finish analise what can be done with
original
SQL request generated by RT .

SELECT *
FROM (SELECT limitquery.*, ROWNUM limitrownum
FROM (SELECT acl.ID
FROM acl, GROUPS, principals, cachedgroupmembers
WHERE ( acl.rightname = ‘SuperUser’
OR acl.rightname = ‘DelegateRights’
)
AND principals.disabled = 0
AND cachedgroupmembers.disabled = 0
AND principals.ID = GROUPS.ID
AND principals.principaltype = ‘Group’
AND principals.ID = cachedgroupmembers.groupid
AND cachedgroupmembers.memberid = 10
AND ( (acl.objecttype = ‘RT::System’ AND
acl.objectid = 1
)
OR (acl.objecttype = ‘RT::System’)
)
AND acl.principalid = principals.ID
AND acl.principaltype = ‘Group’) limitquery
WHERE ROWNUM <= 1)
WHERE limitrownum >= 1

which is run 9,353s, and propose next SQL edition

SELECT /+ PARALLEL(, 2) /
*
FROM (SELECT /+ PARALLEL(LIMITQUERY, 2) /
limitquery., ROWNUM limitrownum
FROM (SELECT /+ PARALLEL(CACHEDGROUPMEMBERS, 2) */
acl.ID
FROM acl, GROUPS, principals, cachedgroupmembers
WHERE 0 = principals.disabled
AND 0 = cachedgroupmembers.disabled
AND GROUPS.ID = principals.ID
AND ‘Group’ = principals.principaltype
AND cachedgroupmembers.groupid = principals.ID
AND 10 = cachedgroupmembers.memberid
AND principals.ID = acl.principalid
AND ‘Group’ = acl.principaltype
AND principals.disabled = cachedgroupmembers.disabled
AND cachedgroupmembers.groupid = GROUPS.ID
AND acl.principalid = GROUPS.ID
AND principals.principaltype = acl.principaltype
AND acl.principalid = cachedgroupmembers.groupid
AND ( ‘RT::System’ = acl.objecttype AND 1 =
acl.objectid
OR ‘RT::System’ = acl.objecttype
)
AND ( ‘SuperUser’ = acl.rightname
OR ‘DelegateRights’ = acl.rightname
)) limitquery
WHERE 1 >= ROWNUM)
WHERE 1 <= limitrownum

which take 5.391s to run.

–
WBR, Alexey G Misyurenko ( MAG-RIPE | MMAGG-RIPN )
CTO of Caravan ISP http://www.caravan.ru
Phone: +7 095 3632252 Cell: +7 095 5082794

List info: The rt-devel Archives

Best Practical is hiring! Come hack Perl for us: Careers — Best Practical Solutions

Best regards, Ruslan.

15

Alexey G Misyurenko wrote:

Ruslan Zakirov wrote:

Ruslan Zakirov wrote:

Ok, I think index on CachedGroupMembers(MemberId, GroupId, Disabled)
should help you with query 4, but to help you with other queries I
need to see EXPLAINs…

Sorry for the pause with answer - second oracle installation take a
time…

Please, make explains before you’ll add index and after.

WITH INDEX

SQL Statement from editor:

SQL Tunning toolkit for oracle just finish analise what can be done with
original
SQL request generated by RT .

SELECT *
FROM (SELECT limitquery.*, ROWNUM limitrownum
FROM (SELECT acl.ID
FROM acl, GROUPS, principals, cachedgroupmembers
WHERE ( acl.rightname = ‘SuperUser’
OR acl.rightname = ‘DelegateRights’
)
AND principals.disabled = 0
AND cachedgroupmembers.disabled = 0
AND principals.ID = GROUPS.ID
AND principals.principaltype = ‘Group’
AND principals.ID = cachedgroupmembers.groupid
AND cachedgroupmembers.memberid = 10
AND ( (acl.objecttype = ‘RT::System’ AND
acl.objectid = 1
)
OR (acl.objecttype = ‘RT::System’)
)
AND acl.principalid = principals.ID
AND acl.principaltype = ‘Group’) limitquery
WHERE ROWNUM <= 1)
WHERE limitrownum >= 1

which is run 9,353s, and propose next SQL edition

SELECT /+ PARALLEL(, 2) /
*
FROM (SELECT /+ PARALLEL(LIMITQUERY, 2) /
limitquery., ROWNUM limitrownum
FROM (SELECT /+ PARALLEL(CACHEDGROUPMEMBERS, 2) */
acl.ID
FROM acl, GROUPS, principals, cachedgroupmembers
WHERE 0 = principals.disabled
AND 0 = cachedgroupmembers.disabled
AND GROUPS.ID = principals.ID
AND ‘Group’ = principals.principaltype
AND cachedgroupmembers.groupid = principals.ID
AND 10 = cachedgroupmembers.memberid
AND principals.ID = acl.principalid
AND ‘Group’ = acl.principaltype
AND principals.disabled = cachedgroupmembers.disabled
AND cachedgroupmembers.groupid = GROUPS.ID
AND acl.principalid = GROUPS.ID
AND principals.principaltype = acl.principaltype
AND acl.principalid = cachedgroupmembers.groupid
AND ( ‘RT::System’ = acl.objecttype AND 1 =
acl.objectid
OR ‘RT::System’ = acl.objecttype
)
AND ( ‘SuperUser’ = acl.rightname
OR ‘DelegateRights’ = acl.rightname
)) limitquery
WHERE 1 >= ROWNUM)
WHERE 1 <= limitrownum

which take 5.391s to run.

–
WBR, Alexey G Misyurenko ( MAG-RIPE | MMAGG-RIPN )
CTO of Caravan ISP http://www.caravan.ru
What version of Oracle?
When did you last run: ?
BEGIN
SYS.DBMS_STATS.GATHER_SCHEMA_STATS (
OwnName => ‘RT_USER’
,Granularity => ‘DEFAULT’
,Options => ‘GATHER’
,Gather_Temp => FALSE
,Method_Opt => 'FOR ALL INDEXED COLUMNS SIZE 1 ’
,Degree => 4
,Cascade => TRUE
,No_Invalidate => FALSE);
END;

I ask because our Oracle instance takes 80msec for this query. Explain
plan looks the same, for the first query. adding parallel does nothing
for timing albeit it does for the cost of the query.
Version Oracle10g (Oracle XE).
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS 1 9
VIEW 1 26 9
COUNT STOPKEY
NESTED LOOPS 1 74 9
NESTED LOOPS 1 69 9
NESTED LOOPS 2 114 5
INLIST ITERATOR
TABLE ACCESS BY INDEX ROWID RT_USER.ACL 2 86 3
INDEX RANGE SCAN RT_USER.ACL1 2 2
TABLE ACCESS BY INDEX ROWID RT_USER.PRINCIPALS 1 14 1
INDEX UNIQUE SCAN RT_USER.PRINCIPALS_KEY 1 0
TABLE ACCESS BY INDEX ROWID RT_USER.CACHEDGROUPMEMBERS 1 12 2
INDEX RANGE SCAN RT_USER.GROUMEM 1 1
INDEX UNIQUE SCAN RT_USER.GROUPS_KEY 1 5 0

Oracle 9.2.0.1.0:
Speed is the same but explain plan is different, dataset is the same
since XE is a test environment with a day old copy of production
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 1 8
VIEW 1 26 8
COUNT STOPKEY
TABLE ACCESS BY INDEX ROWID RT_USER.ACL 1 43 2
NESTED LOOPS 1 74 8
NESTED LOOPS 2 62 4
NESTED LOOPS 2 52 4
TABLE ACCESS BY INDEX ROWID RT_USER.CACHEDGROUPMEMBERS 2 24 2
INDEX RANGE SCAN RT_USER.MEMBERID_IDX 2 1
TABLE ACCESS BY INDEX ROWID RT_USER.PRINCIPALS 1 14 1
INDEX UNIQUE SCAN RT_USER.PRINCIPALS_KEY 4
INDEX UNIQUE SCAN RT_USER.GROUPS_KEY 82 K 403 K
INLIST ITERATOR
INDEX RANGE SCAN RT_USER.ACL1 1 1

Joop

Joop van de Wege JoopvandeWege@mococo.nl

16

Ruslan Zakirov wrote:

I spotted "AND ( (acl.objecttype = ‘RT::System’ AND acl.objectid =

  1. OR (acl.objecttype = ‘RT::System’) )" which is really wierd and
    should be fixed in RT code. This should be just “AND (acl.objecttype =
    ‘RT::System’ AND acl.objectid = 1)”, without OR … part, but I don’t
    think this change would give much speed improvement.

This part of SQL query is result of next function sequince call

Take → SetOwner → _CleanupInvalidDelegations → HasRight(Right =>
‘DelegateRights’,
Object => $RT::System) → _HasRight

Looks like condition
$self->can(‘_IsOverrideGlobalACL’) && $self->_IsOverrideGlobalACL(
$args{Object})

is allways false and push( @objects, ‘RT::System’ ) is allways called.

=== lib/RT/Principal_Overlay.pm
push( @objects, ‘RT::System’ )
unless $self->can(‘_IsOverrideGlobalACL’)
&& $self->_IsOverrideGlobalACL( $args{Object} );

Also in lib/RT/Principal_Overlay.pm I see strange code constructions

right after building arrays @role_clauses and @object_clauses
is placed next code

   $check_roles .= join ' OR ', @role_clauses;
   $check_objects = join ' OR ', @object_clauses;

Why value of $check_roles is appended by join and $check_objects
is overwritten ??