[Rt-devel] Re: SetOwner speed
Joop van de Wege
JoopvandeWege at mococo.nl
Sun Jun 18 15:45:00 EDT 2006
On Sat, 17 Jun 2006 12:00:06 -0400 (EDT)
rt-devel-request at lists.bestpractical.com wrote:
> Alexey G Misyurenko wrote:
> > Ruslan Zakirov wrote:
> >> On 6/15/06, Alexey G Misyurenko <mag at 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
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 at mococo.nl>
More information about the Rt-devel
mailing list