RE: optimizer settings
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 1 Mar 2004 09:58:04 -0500
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@xxxxxxxxxxx]
Sent: Monday, March 01, 2004 9:42 AM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
