Forum OpenACS Development: Re: Faster cc_users in Oracle
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