Re: Unnecessary full table scans

Steve-

Just a couple of quick things to think about.

1. What is db_file_multiblock_read_count set to? If it is an inaccurately high value, full table scans look cheaper to the optimizer.
2. You can run the query with the event 10053 set and get a huge trace file that will tell you what paths the optimizer considered and what it thought each cost. CBO will always choose the cheapest plan, but when the permutations are high, it may not consider all of them.

From sql*plus,

alter session set events '10053 trace name context forever';

...should to it. Crank the query and exit, then go find the trace file. Bitch to read and figure out, but everything is there. :-)

There are probably other simple sanity things to consider, like what schema/login combo you use, other parm settings - optimizer mode, compatibility, etc. I'm sure you checked all of that stuff.

Of course, you could just leave the hints in.

• ricky

Steve Grant wrote:

> Hi,
> Has anyone come across this before and is it a known problem or a
> configuration issue ? We are running a datamart on 8.1.6 on AIX. All our
> fact tables have a composite primary key and bitmap indexes on the indidual
> columns of the primary keys. Statistics are adequate and up-to-date.
> A lot of our queries are however defaulting to a full table scan which is
> leading to long query times.
>
> An example of of timings would be
>
> A - Run query with no hints
> Time to run - 6 mins +
> Cost - 13736
>
> B - Run query forcing it to use composite index.
> Time to run - 1 min +
> Cost - 35558
>
> C - Run query forcing it to use bitmap indexes.
> Time to run - 10 secs
> Cost - 15478
>
> Any views or insights to this would be welcome.
> Thanks
>
> Steve
Received on Tue Apr 10 2001 - 13:34:27 CDT