Re: Low hit ratio, but only for indexes

You haven't given an Oracle version, but if you are using 8.1 you can get hints into the system through the back door by using stored outlines.

use 'create stored outline = XXX'

for a while to collect stored outlines,
then transfer them to a test system
and fiddle with the init.ora parameters
and indexes using

rebuild stored outline YYY

until you get the plan you want as a stored outline, then transport the good outines back to the production environment and tell the system to 'use stored outlines'.

There are a few details to fill in, but I'm sure you get the general idea.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now running 3-day intensive seminars
http://www.jlcomp.demon.co.uk/seminar.html

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

Author of:
Practical Oracle 8i: Building Efficient Databases


Andy wrote in message <8d4033cd.0201141354.ac05289_at_posting.google.com>...


>Actually it was the v$librarycache view, but never mind.

>

>We are running SIEBEL.

>

>I have been advised that it's probably because the queries aren't

>using indexes, which is what I suspected. I ran an explain plan on one

>of their slow queries and it does 2 full table scans of a 7M row

>table. Unfortunately they can't use hints (SIEBEL doesn't let them,

>something about platform independence), and the query has a '<>' in

>it, which I beleive won't use indexes.