Forum OpenACS Development: Faster cc_users in Oracle

Request notifications

Collapse
Posted by Doug Harris on
As our site's membership has grown, the performance of any query using cc_users has steadily decreased. On an as-needed basis, we've rewritten some queries to simply join on parties, users, etc. to get the necessary data.

Today I ran into a query that needed tuning that rewriting to use base tables didn't fix, so I finally found the culprit... it's the "acs.magic_object_id('registered_users')" in the where clause of the definition of cc_users. After reading some articles from Oracle guru Tom Kyte about pl/sql lookup tables, at the bottom of this page, he simply says "If you can do it in a join in the first place, rather then lookup lookup lookup all of the time -- do it that way."

I tried that. It worked. I'll post the code I tested and statistics in a follow-up to this post.

(Note: we're running Oracle 9.2.0.3 and ACS, not OpenACS -- but the change should work for OpenACS on oracle... maybe even postgresql, too)

Collapse
Posted by Doug Harris on
This is basically "select count(*) from cc_users", but I've copied the source for the view into the query. Performing the query with just the view name gave similar results.
SQL> select count(*) 
      from (select o.*, pa.*, pe.*, u.*, mr.member_state, mr.rel_id, u.rowid as row_id
from acs_objects o, parties pa, persons pe, users u, group_member_map m, membership_rels mr
where o.object_id = pa.party_id
and pa.party_id = pe.person_id
and pe.person_id = u.user_id
and u.user_id = m.member_id
and m.group_id = acs.magic_object_id('registered_users')
and m.rel_id = mr.rel_id
and m.container_id = m.group_id) cc_users;
SQL>   2    3    4    5    6    7    8    9   10  
  COUNT(*)
----------
     45603

Elapsed: 00:00:44.51

Execution Plan
----------------------------------------------------------
   0	  SELECT STATEMENT Optimizer=CHOOSE (Cost=85 Card=1 Bytes=51)
   1	0   SORT (AGGREGATE)
   2	1     NESTED LOOPS (Cost=85 Card=1 Bytes=51)
   3	2	NESTED LOOPS (Cost=85 Card=1 Bytes=47)
   4	3	  NESTED LOOPS (Cost=85 Card=1 Bytes=43)
   5	4	    NESTED LOOPS (Cost=85 Card=1 Bytes=39)
   6	5	      NESTED LOOPS (Cost=85 Card=1 Bytes=35)
   7	6		INDEX (FAST FULL SCAN) OF 'GROUP_ELEMENT_INDEX
	  _PK' (UNIQUE) (Cost=85 Card=1 Bytes=31)

   8	6		INDEX (UNIQUE SCAN) OF 'USERS_PK' (UNIQUE)
   9	5	      INDEX (UNIQUE SCAN) OF 'PERSONS_PK' (UNIQUE)
  10	4	    INDEX (UNIQUE SCAN) OF 'PARTIES_PK' (UNIQUE)
  11	3	  INDEX (UNIQUE SCAN) OF 'MEMBERSHIP_REL_REL_ID_PK' (U
	  NIQUE)

  12	2	INDEX (UNIQUE SCAN) OF 'ACS_OBJECTS_PK' (UNIQUE)




Statistics
----------------------------------------------------------
     113786  recursive calls
	  0  db block gets
     456883  consistent gets
	  0  physical reads
	  0  redo size
	381  bytes sent via SQL*Net to client
	503  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  2  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

Now, I've changed the query to join on acs_magic_objects. Query time drops from 44.5 seconds to 8.6 seconds!

SQL> select count(*) 
      from (select o.*, pa.*, pe.*, u.*, mr.member_state, mr.rel_id, u.rowid as row_id
from acs_objects o, parties pa, persons pe, users u, group_member_map m, membership_rels mr, acs_magic_objects mo
where o.object_id = pa.party_id
and pa.party_id = pe.person_id
and pe.person_id = u.user_id
and u.user_id = m.member_id
and m.group_id = mo.object_id
and mo.name = 'registered_users'
and m.rel_id = mr.rel_id
and m.container_id = m.group_id) cc_users;

SQL>   2    3    4    5    6    7    8    9   10   11  
  COUNT(*)
----------
     45603

Elapsed: 00:00:08.62

Execution Plan
----------------------------------------------------------
   0	  SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=71)
   1	0   SORT (AGGREGATE)
   2	1     NESTED LOOPS (Cost=5 Card=1 Bytes=71)
   3	2	NESTED LOOPS (Cost=5 Card=1 Bytes=67)
   4	3	  NESTED LOOPS (Cost=5 Card=1 Bytes=63)
   5	4	    NESTED LOOPS (Cost=5 Card=1 Bytes=59)
   6	5	      NESTED LOOPS (Cost=5 Card=1 Bytes=55)
   7	6		NESTED LOOPS (Cost=5 Card=1 Bytes=51)
   8	7		  TABLE ACCESS (BY INDEX ROWID) OF 'ACS_MAGIC_
	  OBJECTS' (Cost=1 Card=1 Bytes=20)

   9	8		    INDEX (UNIQUE SCAN) OF 'ACS_MAGIC_OBJECTS_
	  PK' (UNIQUE)

  10	7		  INDEX (UNIQUE SCAN) OF 'GROUP_ELEMENT_INDEX_
	  PK' (UNIQUE) (Cost=1 Card=1 Bytes=31)

  11   10		    INDEX (RANGE SCAN) OF 'GROUP_ELEM_IDX_GROU
	  P_IDX' (NON-UNIQUE) (Cost=1 Card=348)

  12	6		INDEX (UNIQUE SCAN) OF 'USERS_PK' (UNIQUE)
  13	5	      INDEX (UNIQUE SCAN) OF 'PERSONS_PK' (UNIQUE)
  14	4	    INDEX (UNIQUE SCAN) OF 'PARTIES_PK' (UNIQUE)
  15	3	  INDEX (UNIQUE SCAN) OF 'MEMBERSHIP_REL_REL_ID_PK' (U
	  NIQUE)

  16	2	INDEX (UNIQUE SCAN) OF 'ACS_OBJECTS_PK' (UNIQUE)




Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
     273823  consistent gets
	  0  physical reads
	  0  redo size
	381  bytes sent via SQL*Net to client
	503  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

Collapse
Posted by Dave Bauer on
Doug,

Great idea. I don't think joining on acs_magic_objects is a problem in this case. The cc_users view is part of the core, so we don't really need that level of abstraction. Indeed, we can't afford it if the query speeds up that much.

I think this should go back into the toolkit.

Collapse
Posted by Don Baccus on
Actually Jeff Davis and I talked about this a month or more ago and had meant to make this change in 4.6.2.

Oops :(

Doug submit a patch to the bug tracker so we don't forget again ...

Collapse
Posted by Doug Harris on
OK, I've submitted a enhancement report with an attached patch. I'll mark this as my first official contribution to the OpenACS effort (although some code I've done my current client has made its way into the code base through other channels (e.g. /acs-admin/www/cache/*))

Doug

Collapse
Posted by Dirk Gomez on
PL/SQL in SQL is dangerous :)
Collapse
Posted by Brian Fenton on
I notice this change definitely speeds up the view but for some reason I now get an ORA-01445 (cannot select ROWID from a join view without a key-preserved table) when banning a user.
Can anyone see why?
Collapse
Posted by C. R. Oldham on
I just banned a user on our test system and didn't experience this.  We are on Oracle 8.1.7.4.