Performance problem: anything else I can do to make this faster?

From: Steve C <sgcjr_at_yahoo.com>
Date: 2 Mar 2005 12:04:42 -0800
Message-ID: <1109793882.803053.240440@l41g2000cwc.googlegroups.com>

Hi,

I'm on Oracle 9i. In short, I have a query that does a few joins, where a couple of the tables involved have on the order of 1 million rows. I use this query in two forms - getting a page of records and a record count - to build a typical record listing page - "records 11-20 out of 700,000" etc.

I've gotten a few performance leaps out of using explain plan, and building indexes such that I have one compound index for each table in the plan, unique where possible. Explain plan reports one range scan per table now, but performance still is not acceptable (3 seconds for the count query, 5 seconds for the page of records). To top it all off, I have a couple more tables that I'll need to join to in the query pretty soon.

I fear I'm hitting the limit of what I can easily do to improve performance. Is it expected that performance with queries like mine with the scale I'm dealing with would take seconds (vs. milliseconds)? Are there any obvious strategies to improve performance I've missed? (i.e., oracle table config settings, oracle system settings, indexing, etc?).

My setup:

employee (id, type_id, department_id, name) : ~ 1 million rows

non-unique index on department_id, type_id (E_DEP_TYPE_IDX...this took count query perf from 20 to 10 seconds)
unique index on id (EMPID_IDX)
non-unique index on type_id
non-unique index on name employee_category_link (employee_id, category_id) : at least 1 million rows
unique index on employee_id, category_id (EC_EMPID_CATID_IDX...this took count query perf from 10 to 3 seconds)

The count query (~3 seconds):

SELECT count(*) FROM employee
INNER JOIN employee_category_link
ON employee.id = employee_category_link.employee_id WHERE ((employee.department_id = 40101)
AND (employee_category_link.category_id = 7) AND (employee.type_id = 2));

The record/page query (~5 seconds):

select * from (
SELECT count(*) FROM employee
INNER JOIN employee_category_link
ON employee.id = employee_category_link.employee_id WHERE ((employee.department_id = 40101)
AND (employee_category_link.category_id = 7) AND (employee.type_id = 2))
order by employee.name
) where rownum <=10;

The latest results from autotrace:

For the count query:

Elapsed: 00:00:03.03

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE)

   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (BY INDEX EMPID_IDX) OF 'EMPLOYEE'
   4    3         INDEX (RANGE SCAN) OF 'E_DEP_TYPE_IDX' (NON-UNIQ
          UE)

   5    2       INDEX (UNIQUE SCAN) OF 'EC_EMPID_CATID_IDX' (UNIQUE)

Statistics

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

For the page of records query:

Elapsed: 00:00:05.03

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE 1 0 COUNT (STOPKEY)

   2    1     VIEW
   3    2       SORT (ORDER BY STOPKEY)
   4    3         NESTED LOOPS
   5    4           TABLE ACCESS (BY INDEX EMPID_IDX) OF 'EMPLOYEE'
   6    5             INDEX (RANGE SCAN) OF 'E_DEP_TYPE_IDX' (NON-
          UNIQUE)

   7    4           INDEX (UNIQUE SCAN) OF 'EC_EMPID_CATID_IDX'

(UNIQUE) Statistics

          0  recursive calls
          0  db block gets
    1405941  consistent gets
          0  physical reads
          0  redo size
       2109  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         10  rows processed

Any help is very much appreciated (!).

-Steve Received on Wed Mar 02 2005 - 14:04:42 CST