From oracle-l-bounce@freelists.org Mon Mar 1 08:55:07 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i21Et7O03727 for ; Mon, 1 Mar 2004 08:55:07 -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 i21Et6o03722 for ; Mon, 1 Mar 2004 08:55:07 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A2F973950AF; Mon, 1 Mar 2004 09:55:50 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 01 Mar 2004 09:54:42 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from tera.umi.com (tera.umi.com [192.195.245.144]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7481039502D for ; Mon, 1 Mar 2004 09:54:32 -0500 (EST) Received: from bosmail02.bos.il.pqe (bosmail02.bos.il.pqe [172.24.3.66]) by tera.umi.com (8.11.6/8.11.6) with ESMTP id i21Ew4903672 for ; Mon, 1 Mar 2004 09:58:04 -0500 Received: from bosmail00.bos.il.pqe ([172.24.3.64]) by bosmail02.bos.il.pqe with Microsoft SMTPSVC(5.0.2195.6713); Mon, 1 Mar 2004 09:58:04 -0500 X-MimeOLE: Produced By Microsoft Exchange V6.0.6249.0 content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by Ecartis Subject: RE: optimizer settings Date: Mon, 1 Mar 2004 09:58:04 -0500 Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CFECFC27@bosmail00.bos.il.pqe> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: optimizer settings Thread-Index: AcP/nGZO+2t/a4pdR3Ofa4o79PKcswAADJlg From: "Bobak, Mark" To: X-OriginalArrivalTime: 01 Mar 2004 14:58:04.0524 (UTC) FILETIME=[9968DAC0:01C3FF9D] X-archive-position: 93 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Mark.Bobak@il.proquest.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l Well, it really depends on how your system is currently behaving. optimizer_index_cost_adj is used to estimate the relative cost of an indexed single block read vs. a full scan multiblock read. At a setting of 100, the optimizer assumes they are equal. You can reduce it, to make the optimizer favor index reads, or increase it, to make the optimizer favor full scans. optimizer_max_permutations controls how many join order permutations the optimizer attempts when optimizing a SQL statement. The default was 80,000 through 8i and reduced to 2,000 starting w/ 9i. You can reduce it, which will cause the optimizer to spend less time parsing, at the possible cost of sub-optimal execution plans, or you may increase it, causing the optimizer to spend more time parsing, hopefully with the result being better execution plans. My gut feeling tells me this is NOT the first place to begin tuning. I'm leave it alone unless you have a convincing argument to change it. -----Original Message----- From: Syed Jaffar Hussain [mailto:sjaffarhussain@hotmail.com] Sent: Monday, March 01, 2004 9:42 AM To: oracle-l@freelists.org Subject: optimizer settings Hi List, After installing oracle 9.2.0.1 on HPUX 11i bit 64, I found the following optimizer settings optimizer_index_cost_adj integer 100 optimizer_max_permutations integer 2000 Are these parameters set by default to match most of the system requirments or do we need to change it? Rgds Jaffar ---------------------------------------------------------------- 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 -----------------------------------------------------------------