Re: Oracle Performance Issue

Roland Lohse" <"roland.lohse[antispam] wrote:
> Hi all,
>
> we have an application running an Oracle's 8.1.7. - unfortunally the
> code is wrapped and we do not have any support from the software
> company that made this application.
>
> We have following SQL query that slows down the whole system (taken
> from TOAD's "kill/trace"):
>
> SELECT user_group_id
> FROM user_group_ou
> GROUP BY user_group_id
> HAVING COUNT (*) = :b1 - 1

Did you look at the execution plan?

> The table user_group_id contains 3.5 million records; it has tree
> coloumns, two of them are foreign keys, one of them is a VARCHAR2
> flag.

What's that? A boolean value stored in a VARCHAR?

> So the problem is that we cannot change the code that slows down the
> application - does anybody have a hint how to speed up this query?

An index on user_group_id might help - depending on the record size. In that case Oracle can do an index scan instead of a table scan (which I assume it's doing right now).

The of course you have all the other options that improve IO performance such as distributing data on several disks, adjusting cache sizes etc. Difficult to tell with the info provided so far.

Kind regards

    robert Received on Wed Sep 07 2005 - 05:11:58 CDT