Re: Hints

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_at_slac.stanford.edu] Sent: Monday, August 15, 2011 11:13 AM
To: oracle-l_at_freelists.org
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Orlando LSent: Monday, August 15, 2011 9:38 AMTo: Ric Van DykeCc: oracle-l_at_freelists.orgSubject: 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

--

http://www.freelists.org/webpage/oracle-l Received on Mon Aug 15 2011 - 14:14:10 CDT