Re: Oracle Performance Issue

From: Roland Lohse <_at_hansenet.de>
Date: Wed, 07 Sep 2005 13:10:19 +0200
Message-ID: <3o804rF4jv48U1@uni-berlin.de>

Robert Klemme schrieb:

>>
>>SELECT   user_group_id
>>     FROM user_group_ou
>>GROUP BY user_group_id
>>   HAVING COUNT (*) = :b1 - 1

>
>
> Did you look at the execution plan?
>

Yes; Oracle performs a full table scan.

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

It's a VARCHAR2(1) field containing the same value for all rows.

>
> 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).
>

We'll try that. Seems reasonable.

> 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.

IO is not a big deal - as we should have lots of that (an EMC Symmetrix is direct attached). We do not have performance issues with other SQL - that is well written. Querieng our biggest table - which contains over 1.000.000.000 records - are really fast - when those queries use proper indices.

Thanks Roland Received on Wed Sep 07 2005 - 06:10:19 CDT