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

On 2 Mar 2005 12:04:42 -0800, "Steve C" <sgcjr_at_yahoo.com> wrote:

>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

From your explain plans it looks like you are still using the Rule Based Optimizer. There is no advantage in doing so. If you would be using the Cost Based Optimizer you would compress the composite indexes, and you would also convert the junction table (employee_category_link) from a heap-organized table into an index organized table.
You definitely need to verify whether the optimizer is using *both* columns of the index on department_id,type_id. Now it looks like the optimizer is ignoring the type_id column. You should also verify which of the 2 columns involved is the most selective. If type_id is the most selective making this the leading column in the index (especially if you compress the index on the first column) should provide better results.

Hth

--
Sybrand Bakker, Senior Oracle DBA