From oracle-l-bounce@freelists.org Fri Apr 22 16:37:01 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j3MLb0qU006748 for ; Fri, 22 Apr 2005 16:37:00 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j3MLao4Z006726 for ; Fri, 22 Apr 2005 16:36:51 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9B6821862DD; Fri, 22 Apr 2005 15:34:01 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 17765-03; Fri, 22 Apr 2005 15:34:01 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 230D11863CF; Fri, 22 Apr 2005 15:34:01 -0500 (EST) X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit Subject: RE: optimizer_ ??? Date: Fri, 22 Apr 2005 15:31:16 -0500 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: optimizer_ ??? Thread-Index: AcVHeRLfhL/kf7czTR+c4FpURyu6zgAAFs3g From: "Post, Ethan" To: , Cc: X-OriginalArrivalTime: 22 Apr 2005 20:32:09.0596 (UTC) FILETIME=[5B75DBC0:01C5477A] X-archive-position: 18882 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Ethan.Post@ps.net Precedence: normal Reply-To: Ethan.Post@ps.net X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=0.0 required=5.0 tests=AWL autolearn=ham version=2.63 OK, this just made me of something else we need (someone at @oracle.com watching I hope). select /*+PARAMETER optimizer_max_permutations=3D20 optimizer_index_caching=3D80 optimizer_index_cost_adj=3D20 */ from blah where foo=3D123; This would allow us to tweak session variables at the SQL statement level. Currently we have to use some sort of triggering event to determine which sessions might be about to run the SQL and then use that event to set the parameters at the session level. I like the method above much better. -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Wolfgang Breitling Sent: Friday, April 22, 2005 3:22 PM To: robyn.sands@gmail.com Cc: oracle-l@freelists.org Subject: Re: optimizer_ ??? Robyn, that's how I remember it too. I don't deny that some people have had success lowering oica or raising=20 oic - and for individual sql I have used them too ( I remember one case=20 where I deliberately set oica to 1 so that differences in index costs=20 would disappear and the index be determined by the tie-breaking rule=20 which was the index I needed) but I really don't like the idea of=20 setting them system-wide. Robyn wrote: > I attended Wolfgang's presentation on oica and oic at hotsos, and > towards the end of the presentation, he said using system statistics > would provide better results than adjusting oica and oic. This was >=20 > Of course, if I've misremembered the presentation, someone please let me kn=3D > ow. >=20 --=20 Regards Wolfgang Breitling Centrex Consulting Corporation www.centrexcc.com -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l