Andy wrote:
>
> I am assisting with a SIEBEL implementation, and the performance is
> terrible. I have narrowed it down to INDEX usage (or lack thereof) but
> can't get the queries to use the indexes. This is from the
> v$librarycache view:
>
> NAMESPACE Hit Ratio Pin Hit Ratio Reloads
> --------------- ---------- ------------- ----------
> SQL AREA 98 99 198
> TABLE/PROCEDURE 91 93 28
> BODY 93 92 0
> TRIGGER 100 100 0
> INDEX 19 3 1588
> CLUSTER 99 99 0
> OBJECT 100 100 0
> PIPE 100 100 0
>
> Their queries are taking ages to run, and I did an explain plan on one
> of the problem ones and discovered it's doing 2 full scans of a 7M row
> table. The query contains a '<>' in the where clause, which I believe
> won't use an index. The column isn't the first one in the index
> either.
>
> The SIEBEL consultant (hah!) isn't any good, all he knows about are a
> couple of initialisation parameters.
>
> For further info:
>
> Oracle V8163 on Solaris
> shared_pool_size = 600M
> 8k block size
> sort area size=500m
> sort area retained size=250M
>
> Any ideas? Please? Help me...
What is your optimizer mode? When was the last time statistics were
gathered? How many db_block_buffers? Have you looked into stored
outlines? etc., etc.
--
Ron Reidy
Oracle DBA
Received on Mon Jan 14 2002 - 17:51:16 CST
