[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