[rt-users] Oracle 9 and Build.html performance

[Emmanuel Lacour]

On Sun, Feb 24, 2008 at 11:57:46PM +0300, Ruslan Zakirov wrote:
> Hello, guys.
> 

Privet Ruslan ;)

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

> I'm reviewing this again after receiving new info from various sources.
> 1) 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.
> 2) 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:
> 1) 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.

> 2) 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 SQL*Net to client
    275  bytes received via SQL*Net 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 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


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