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

My ISP seems to be dropping news items
all over the place, so I've missed at least a couple of posts on this item. Apologies if all this has been said before.

How much data are you actually trying to acquire ? In the sample query, how many
rows are there with:

              employee.department_id = 40101     and employee.type_id = 2

I can see that the optimizer has estimated about 700,000 (card = 696739), and the statistics suggest that this is probably close (consistent gets = 1.4M - assume that's 2 for every index access into the second table).

For estimating time to completion, I use 10,000 Logical I/Os per 100 MHz of CPU per second for simple logical I/Os - which means your 2 seconds would be 'typical' with a CPU speed of 700MHz, and 'unsurprising' for any speed between about 350MHz and 1.4GHz. (It's only a ballpark guide). It's not going to be possible to make this very much faster.

In another post, I think you said something about reporting the relevant employees in pages of 10, in name order.

If the number of employees for a given department is very large, then the cost of acquiring all of them to sort them will be the major cost of the query. If this is the case, it is possible for the optimizer to avoid the sort by using a suitable index. In outline:

select {details}
from employee emp

where     emp.dept_no = 99
and        emp.dept_type = 'X'
order by
           emp.name

If you only have an index on dept_no, then Oracle will probably use it, then sort the data by name.

If you also have an index on name, then it is possible to access the data through this index, so that it can be acquired in order without sorting.

For queries where you want the first few pages of 10 rows, when the final selection is going to be 700,000, you might need to implement a solution that depends on this alternative execution path.

As far getting the count is concerned, you might want to consider a fast refresh
materialized view, updated every few minutes, perhaps, that records the counts. If you only want to report roughly the number of rows that will be returned, then this may be a good trade between accuracy and performance.

For more ideas on paged output, and prior counts, Tom Kyte's has some useful examples and comments.

    http://asktom.oracle.com

I saw a couple of titles that looked as if they might be relevant when I did a search on

    page web
e.g.

getting rows N through M of a result set paging through results on the web.

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Public Appearances - schedule updated Jan 21st 2005

"Steve C" <sgcjr_at_yahoo.com> wrote in message news:1109872625.480751.155340_at_g14g2000cwa.googlegroups.com...
> Quick follow-up. I tried dropping the composite index that was getting
> a fast full scan, and the count query plan is back to looking just like
> record query (full table scan of EMPLOYEE) - and performance is
> unchanged. Again here's the query + plan + stats:
>
> alter session set optimizer_index_cost_adj = 50;
> alter session set optimizer_index_caching = 90;
>
> 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));
>
> Elapsed: 00:00:02.07
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1048 Card=1 Bytes=22
> )
>
> 1 0 SORT (AGGREGATE)
> 2 1 NESTED LOOPS (Cost=1048 Card=696739 Bytes=15328258)
> 3 2 TABLE ACCESS (FULL) OF 'EMPLOYEE' (Cost=1046 Ca
> rd=696739 Bytes=9754346)
>
> 4 2 INDEX (UNIQUE SCAN) OF 'EC_EMPID_CATID_IDX' (UNIQUE)
>
>
>
>
> Statistics
> ----------------------------------------------------------
> 327 recursive calls
> 0 db block gets
> 1404347 consistent gets
> 150 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
> 6 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
> The weird thing is I have indexes on each of columns referred to in the
> where clause, but the CBO insists on a full table scan. Any ideas as
> to why?
>
> -Steve
>
Received on Fri Mar 04 2005 - 04:22:42 CST