Re: why does CBO not use available indices

"Snid" <snid_at_snider.sno> wrote in message news:<blt9dn$ajp$1_at_bunyip.cc.uq.edu.au>...
> When I upgraded a database from 8.1.6.3 to 9.2.0.4 I found that some of my
> queries on the 9i database were doing full table scans instead of using
> indexes. This caused the query to run about 10 times longer. I went back and
> compared all the differences between both databases and found that if I set
> my sort_area_size
> to be the same as the 8i version (about 1.5Mb) then the execution plan
> changed and it started using the index resulting in the query running in the
> same amount of time as the 8i database.
>
> I wasn't using sort_area_size in the 9i database due to the new parameter,
> pga_aggregate_target
>
> So, I experimented by adding the pga_aggregate_target back into the 9i
> database and found it started doing full table scans again instead of index
> lookups. I then lowered the value of pga_aggregate_target down to about 30Mb
> and at this point the query started using indexes.
>
> So I learnt from this that having too much sort memory can cause problems,
> this was a big surprise to me as I thought having a large value for
> pga_aggregate_target was good - not always the case it seems. I need to
> research this a lot more, but until then I will stick with the old parameter
> of sort_area_size.

same here. went from 8.1.7.4.6 to 9.2.0.4. kicked in pga_aggregate_target = 512M.
execution plans for statemtents containing outer joins to views (nested subqueries with aggregation) went the hash-join/FTS route, consuming literally 100 times the cpu usage.

kicked in optimizer_features_enable = 8.1.7 and things reverted. (thanks AB)

Still have to fix the behavior, but it got us out of 911 mode.

Pd Received on Tue Oct 07 2003 - 19:11:21 CDT