Re: Help: Why does a query go from 4 secs to 2 hrs due to using NOT IN instead of NOT EXISTS

"Joel Garry" <joel-garry_at_home.com> wrote in message news:91884734.0407021055.479b3152_at_posting.google.com...
> Sybrand Bakker <sybrandb_at_hccnet.nl> wrote in message
news:<09q9e09gjo3nfg0h06s31ljk9709gcu0p0_at_4ax.com>...
>
> > In CBO, using the /*+HASH_AJ*/ hint on not in subqueries should help.
> > Maybe you should also very optimizer_index_caching and
> > optimizer_index_cost_adj. Their default values will result in weird
> > execution plans. Optimizer_index_caching should be set to your cache
> > hit ratio.
>
> I'm not sure I quite see why a parameter for costing index scans
> should be set to a hit ratio of a buffer that has both index and data
> in it. How would such a ratio relate to the percentage of index
> blocks in the cache? Or am I missing a joke?

The rationale goes like this.

O_I_C represents to the optimizer the likelihood of finding an index block in memory instead of on disk. Thus the default of 0 tells the optimizer that every index access will be a physical read. As we are dealing with index accesses on a system wide level we have no real way of knowing what the actual likelihood is for indexes on average, but the BCHR would be a good start. Received on Sat Jul 03 2004 - 16:19:05 CDT