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

[Emmanuel Lacour]

On Mon, Jan 21, 2008 at 07:51:11PM +0300, Ruslan Zakirov wrote:
> I'm not that good in oracle's optimizer, but I'm pretty sure that the
> following index will help you:
> CREATE INDEX Groups3 ON Groups (LOWER('Type'), LOWER('Domain'), Instance);
> 
> Can you add it, generate plan again and sent it to the list, so I can
> check that it really helps in the way I think it should :)
> 

Thanks for your help Ruslan! Unfortunatly, it didn't solve anything :(

An interesting think is that I tried the same request on an Oracle 10g, with
same content ... and here no problem of performances with the stock RT indexes.
So maybe we would better try to upgrade to Oracle 10g.

I did:

SQL> set autotrace on
SQL> CREATE INDEX Groups3 ON Groups (LOWER('Type'), LOWER('Domain'), Instance);
Index created.
SQL>   execute dbms_utility.analyze_schema( 'RT', 'estimate');

then here is the plan for the same request:

Execution Plan
----------------------------------------------------------
   0	  SELECT STATEMENT Optimizer=CHOOSE (Cost=11446 Card=1 Bytes=2
	  54)

   1	0   SORT (ORDER BY) (Cost=11446 Card=1 Bytes=254)
   2	1     NESTED LOOPS (Cost=11444 Card=1 Bytes=254)
   3	2	VIEW (Cost=11443 Card=1 Bytes=10)
   4	3	  SORT (UNIQUE) (Cost=11443 Card=1 Bytes=77)
   5	4	    NESTED LOOPS (Cost=1231 Card=1170677 Bytes=9014212
	  9)

   6	5	      HASH JOIN (Cost=1230 Card=20646030161 Bytes=1424
	  576081109)

   7	6		TABLE ACCESS (FULL) OF 'GROUPS' (Cost=195 Card
	  =227456 Bytes=5686400)

   8	6		NESTED LOOPS (Cost=205 Card=453847 Bytes=19969
	  268)

   9	8		  MERGE JOIN (CARTESIAN) (Cost=204 Card=209510
	  2 Bytes=83804080)

  10	9		    INLIST ITERATOR
  11   10		      INDEX (RANGE SCAN) OF 'ACL1' (NON-UNIQUE
	  ) (Cost=1 Card=23 Bytes=667)

  12	9		    BUFFER (SORT) (Cost=203 Card=90683 Bytes=9
	  97513)

  13   12		      TABLE ACCESS (BY INDEX ROWID) OF 'PRINCI
	  PALS' (Cost=9 Card=90683 Bytes=997513)

  14   13			INDEX (RANGE SCAN) OF 'FSHPRINCIPALS1'
	   (NON-UNIQUE)

  15	8		  INDEX (UNIQUE SCAN) OF 'USERS_KEY' (UNIQUE)
  16	5	      INDEX (RANGE SCAN) OF 'GROUMEM' (NON-UNIQUE)
  17	2	TABLE ACCESS (BY INDEX ROWID) OF 'USERS' (Cost=1 Card=
	  1 Bytes=244)

  18   17	  INDEX (UNIQUE SCAN) OF 'USERS_KEY' (UNIQUE)




Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
    2699012  consistent gets
	  0  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
	  3  sorts (memory)
	  0  sorts (disk)
	  0  rows processed


Also here is some statistics for this database:

SQL> SELECT count(*) FROM Attachments;
    627770
SQL> SELECT count(*) FROM Queues;
       334
SQL> SELECT count(*) FROM Links;
      1737
SQL> SELECT count(*) FROM Principals;
    358744
SQL> SELECT count(*) FROM Groups;
    338358
SQL> SELECT count(*) FROM ScripConditions;
	10
SQL> SELECT count(*) FROM Transactions;
   1361371
SQL> SELECT count(*) FROM Scrips;
       164
SQL> SELECT count(*) FROM ACL;
      4124
SQL> SELECT count(*) FROM GroupMembers;
    330161
SQL> SELECT count(*) FROM CachedGroupMembers;
    981829
SQL> SELECT count(*) FROM Users;
     19645
SQL> SELECT count(*) FROM Tickets;
     76012
SQL> SELECT count(*) FROM ScripActions;
	17
SQL> SELECT count(*) FROM Templates;
	58
SQL> SELECT count(*) FROM ObjectCustomFields;
      1198
SQL> SELECT count(*) FROM ObjectCustomFieldValues;
    101937
SQL> SELECT count(*) FROM CustomFields;
       176
SQL> SELECT count(*) FROM CustomFieldValues;
      1244
SQL> SELECT count(*) FROM Attributes;
      1316
SQL> SELECT count(*) FROM sessions;
	 0