RE: optimizer settings

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Mon, 1 Mar 2004 09:58:04 -0500
Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CFECFC27@bosmail00.bos.il.pqe>

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_at_hotmail.com] Sent: Monday, March 01, 2004 9:42 AM
To: oracle-l_at_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_at_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_at_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

Received on Mon Mar 01 2004 - 08:55:07 CST