Re: Hints

Ian,

With all due respect, please don't touch that parameter even with a 10-foot 
pole, and please don't advise anyone else to touch it either?

I am pretty sure that I'm the idiot who started the craze for the 
OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ parameters back in the 
1999 timeframe with a widely-read white paper and some presentations, and the 
cold hard fact is that practical use of this parameter became inadvisable 
starting in Oracle9i in the 2002 timeframe, when the ability to gather "system 
statistics" using DBMS_STATS replaced the functionality of this parameter 
entirely. Yes, you will still find folks who swear by the results obtained from 
changing these parameters, but in almost all cases I have found that using 
these parameters could be more effectively replaced by understanding and 
applying the DBMS_STATS package more effectively, and the negative results of 
using these parameters system-wide are subtle, wide-reaching, and almost 
universely a net negative.

I haven't unposted my paper on the "Search for Intelligent Life in the 
Cost-Based Optimizer" because several years ago I had to be sure to slather it 
with all kinds of caveats and warnings similar to these above to prevent people 
from thinking that the information conveyed by the paper was either current or 
correct.

Please forget everything about the OPTIMIZER_INDEX_COST_ADJ parameter and 
research instead what is provided with GATHER_SYSTEM_STATISITICS procedure in 
the DBMS_STATS package and the SYS.AUX_STAT$ table?

And please also forget everything about the OPTIMIZER_INDEX_CACHING parameter 
and research instead about the proper use of 
GATHER_[DATABASE|SCHEMA|TABLE|INDEX]_STATS procedures in the DBMS_STATS package?

Sorry for the rant -- we now return you to your regularly-scheduled 
programming. :-)

Thanks!

-Tim


-----Original Message-----
From: MacGregor, Ian A. [mailto:ian@xxxxxxxxxxxxxxxxx]
Sent: Monday, August 15, 2011 11:13 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Hints

It may be that Oracle has incorrect statistics concerning your I/O system, or 
even if those statistics are good, has still misjudged its capabilities. It may 
be the OS statistics were gathered at an inappropriate time. I am of the 
opinion that Oracle still overrates full table scans as compared to index 
lookups. There is an initialization parameter which addresses 
this.optimizer_index_cost_adjI would not change this parameter unless you are 
sure that the OS statistics were taken under an appropriate load, and the 
non-optimal FTS choices be made by the optimizer are noticeably slowing the 
system. Obviously changing the parameter can lead to an index being used when 
an FTS is optimal.-----Original Message-----From: oracle-l-bounce@xxxxxxxxxxxxx 
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Orlando LSent: Monday, 
August 15, 2011 9:38 AMTo: Ric Van DykeCc: oracle-l@xxxxxxxxxxxxxxxxxxxx: Re: 
HintsThank you Ric and others. I have few queries that run slow, but I have to 
force them to use indexes. With the use of indexes they run much faster, with 
statistics present. I am left with the classic question of why is the optimizer 
not using my indexes and why do I have to force it. 
--http://www.freelists.org/webpage/oracle-l

Other related posts: