From oracle-l-bounce@freelists.org Tue Mar 9 23:27:38 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i2A5Rcf11564 for ; Tue, 9 Mar 2004 23:27:38 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i2A5RTo11559 for ; Tue, 9 Mar 2004 23:27:29 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id ACA0C3951BB; Wed, 10 Mar 2004 00:26:14 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 10 Mar 2004 00:25:11 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from medo.adelphia.net (ct-seymour3a-133.wtrbct.adelphia.net [24.49.189.133]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 535AA394A6A for ; Wed, 10 Mar 2004 00:25:06 -0500 (EST) Received: from medo.adelphia.net (localhost [127.0.0.1]) by medo.adelphia.net (8.12.8/8.12.8) with ESMTP id i2A5TvkA008992 for ; Wed, 10 Mar 2004 00:29:57 -0500 Date: Wed, 10 Mar 2004 00:29:56 -0500 From: Mladen Gogala To: oracle-l@freelists.org Subject: Re: Re: optimizer_index_cost_adj and optimizer_index_caching Message-ID: <20040310052956.GA8969@medo.adelphia.net> References: Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Disposition: inline In-Reply-To: (from Jared.Still@radisys.com on Wed, Mar 10, 2004 at 00:13:46 -0500) X-Mailer: Balsa 2.0.16 Lines: 180 X-archive-position: 266 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: mgogala@adelphia.net Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l I have a feeling that you were playing a bit with the event 10053 to learn what statistics the optimizer is looking into and that gave you the clue about which histogram you needed. I also have problems with hints, although they're considered a "scalpel" and not a "katana" type tool. My problem with hints is that if the background engine changes, my hints can actually mess things up instead of improving them. In version 7.3.2, I've had quite a few /*+ USE_NL INDEX */ and /*+ USE_MERGE */ hints. In 7.3.4 hash join became a a good alternative. Guess what happened to my "hinted" queries? On 03/10/2004 12:13:46 AM, Jared.Still@radisys.com wrote: > If I correctly understand your post, what you are saying is that > the init.ora parms O_I_C_A and O_I_C were set on all your > databases because it helped a problem on two of them. > > This is not generally considered a good practice, that is, making > changes to a parameter with global implications without a specific > reason for doing so, and the testing to back it up. > > I once set these parameters on a database that houses an application > of somewhat questionable design. It did help, but I was really only > trying to fix a single query. > > So, I put the shotgun back in the cabinet, pulled out the laser scalpal > and created a histogram on a single column of a single table and > solved the problem. > > HTH > > Jared > > > > > > > > "Juan Cachito Reyes Pacheco" > Sent by: oracle-l-bounce@freelists.org > 03/09/2004 12:25 PM > Please respond to oracle-l > > > To: > cc: > Subject: Re: Re: optimizer_index_cost_adj and optimizer_index_caching > > > I asked and this how it happened > one database gave problem, then we found setting this parameters solved > this > problem, then we set > them ONLY in one database > after about one year other database had the same problem, after some > months > other, > then we decided to set it to all at once. > because the worload is between and oltp and a dss, we don't have to change > it. > That is why I suggest to set it, is better to have a more acurrate value > set > in the database. in 9.2 windows > ----- Original Message ----- > From: > To: > Sent: Tuesday, March 09, 2004 9:36 AM > Subject: Re: Re: optimizer_index_cost_adj and optimizer_index_caching > > > > the debate on this topic is exactly why we need to keep this list going. > Thanks for all your help guys. > > > > > > From: "Juan Cachito Reyes Pacheco" > > > Date: 2004/03/09 Tue AM 08:19:01 EST > > > To: > > > Subject: Re: optimizer_index_cost_adj and optimizer_index_caching > > > > > > I'm using, because in a specific database a query gave trouble, and I > fixed > > > it setting this parameters. Curiously in other production databases > (more > > > than 15) similar to that, didn't gave that problem. > > > But we decided to set it any way. > > > I think is better to set them, even when in most situation there is > not > > > problems. > > > :) > > > > > > ----- Original Message ----- > > > From: "Niall Litchfield" > > > To: > > > Sent: Tuesday, March 09, 2004 5:01 AM > > > Subject: RE: optimizer_index_cost_adj and optimizer_index_caching > > > > > > > > > Thanks Joze and Wolfgang > > > > > > I'm in a position where gathering system stats seems to me to be the > Right > > > Thing (tm) to do, certainly an advance on setting parameters based on > > > guesses/measurements ahead of time. I hadn't until now seen any > indications > > > as to whether the feature worked as advertised or if in fact gathering > > > system stats introduced for example unexpected and unwanted plan > changes, > > > curious bugs etc etc. In fact I hadn't seen *any* feedback at all > which > made > > > me suspicious that anyone was actually using it - I guess folks recall > the > > > introduction of the CBO which was equally the right thing to do but.. > > > > > > Niall Litchfield > > > Oracle DBA > > > Audit Commission > > > +44 117 975 7805 > > > > > > > > > > > > ********************************************************************** > > > This email contains information intended for > > > the addressee only. It may be confidential > > > and may be the subject of legal and/or > > > professional privilege. Any dissemination, > > > distribution, copyright or use of this > > > communication without prior permission of > > > the sender is strictly prohibited. > > > ********************************************************************** > > > > > > ---------------------------------------------------------------- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > > ---------------------------------------------------------------- > > > To unsubscribe send email to: oracle-l-request@freelists.org > > > put 'unsubscribe' in the subject line. > > > -- > > > Archives are at http://www.freelists.org/archives/oracle-l/ > > > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html > > > ----------------------------------------------------------------- > > > > > > > > > ---------------------------------------------------------------- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > > ---------------------------------------------------------------- > > > To unsubscribe send email to: oracle-l-request@freelists.org > > > put 'unsubscribe' in the subject line. > > > -- > > > Archives are at http://www.freelists.org/archives/oracle-l/ > > > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html > > > ----------------------------------------------------------------- > > > > > > > ---------------------------------------------------------------- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > ---------------------------------------------------------------- > > To unsubscribe send email to: oracle-l-request@freelists.org > > put 'unsubscribe' in the subject line. > > -- > > Archives are at http://www.freelists.org/archives/oracle-l/ > > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html > > ----------------------------------------------------------------- > > > ---------------------------------------------------------------- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > ---------------------------------------------------------------- > To unsubscribe send email to: oracle-l-request@freelists.org > put 'unsubscribe' in the subject line. > -- > Archives are at http://www.freelists.org/archives/oracle-l/ > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html > ----------------------------------------------------------------- > > > -- Mladen Gogala Oracle DBA ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------