Forum OpenACS Development: Re: calendar in .LRN really slow

Collapse
11: Re: calendar in .LRN really slow (response to 1)
Posted by Bernt Pettersen on 07/08/03 11:49 PM
Ah! Thanks for the link.

Here's the table sizes 

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
ACS_ACTIVITIES                       2946
TIME_INTERVALS                      29110
TIMESPANS                           14555
ACS_EVENTS                          11736
TIMEZONES                              52
CALENDARS                           28858
CAL_ITEM_TYPES                         43
CAL_ITEMS                           11736

And the execution plan: 

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=79 Card=1 Bytes=136)
   1    0   NESTED LOOPS (Cost=79 Card=1 Bytes=136)
   2    1     NESTED LOOPS (Cost=78 Card=1 Bytes=125)
   3    2       HASH JOIN (Cost=77 Card=1 Bytes=56)
   4    3         HASH JOIN (Cost=66 Card=59 Bytes=2714)
   5    4           HASH JOIN (Cost=44 Card=73 Bytes=1898)
   6    5             TABLE ACCESS (FULL) OF 'TIME_INTERVALS' (Cost=33 Card=73 Bytes=1314)
   7    5             TABLE ACCESS (FULL) OF 'TIMESPANS' (Cost=10 Card=14555 Bytes=116440)
   8    4           TABLE ACCESS (FULL) OF 'ACS_EVENTS' (Cost=21 Card=11736 Bytes=234720)
   9    3         TABLE ACCESS (FULL) OF 'CAL_ITEMS' (Cost=10 Card=103 Bytes=1030)
  10    2       TABLE ACCESS (BY INDEX ROWID) OF 'ACS_ACTIVITIES' (Cost=1 Card=2946 Bytes=203274)
  11   10         INDEX (UNIQUE SCAN) OF 'ACS_ACTIVITIES_PK' (UNIQUE)
  12    1     TABLE ACCESS (BY INDEX ROWID) OF 'CAL_ITEMS' (Cost=1 Card=11736 Bytes=129096)
  13   12       INDEX (UNIQUE SCAN) OF 'CAL_ITEM_CAL_ITEM_ID_PK' (UNIQUE)


Statistics
----------------------------------------------------------
     114430  recursive calls
     228875  db block gets
     286537  consistent gets
          0  physical reads
          0  redo size
        716  bytes sent via SQL*Net to client
        319  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
         22  sorts (memory)
          0  sorts (disk)
          0  rows processed
Collapse
12: Re: calendar in .LRN really slow (response to 11)
Posted by Dirk Gomez on 07/09/03 11:18 AM

Do you have indexes on time_intervals and timespans? The first thing that Oracle does while executing this statement is joining those two tables - with a full table scan.

It's a bit hard to say whether a full table scan here is inevitable because I don't understand the semantics of them. It could well be that Oracle needs to touch a big enough part of rows in these tables so that a full-table scan appears (is?) cheaper.

What happens if you remove both tables from query. Just performance-wise...we have to check the semantics a little later then :)