[rt-users] Oracle 9 and Build.html performance
Emmanuel Lacour
elacour at easter-eggs.com
Wed Feb 27 10:41:10 EST 2008
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 :)
> 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
>
>
1) 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 SQL*Net to client
275 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
21 sorts (memory)
0 sorts (disk)
0 rows processed
2) 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 SQL*Net to client
275 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed
3) 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 SQL*Net to client
275 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed
4) 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 SQL*Net to client
275 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed
5) 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 SQL*Net to client
275 bytes received via SQL*Net 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 :(
More information about the rt-users
mailing list