| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Cost vs Rule
The following is from the Tim Gorman paper
· OPTIMIZER_INDEX_CACHING
This initialization parameter represents a percentage value, ranging between
the values of 0 and 99. The default value of 0 indicates to the CBO that 0%
of database blocks accessed using indexed access can be expected to be found
in the Buffer Cache of the Oracle SGA. This implies that all index accesses
will require a physical read from the I/O subsystem for every logical read
from the Buffer Cache, also known as a 0% hit ratio on the Buffer Cache.
This parameter applies only to the CBO's calculations of accesses for blocks
in an index, not for the blocks in the table related to the index.
· OPTIMIZER_INDEX_COST_ADJ
This initialization parameter is also a percentage value, ranging between 1
and 10000, representing a comparison between the relative cost of physical
I/O requests for indexed access and full table-scans. The default value of
100 indicates to the cost-based optimizer that indexed access is 100% as
costly (i.e., equally costly) as FULL table scan access.
As it turns out, the default values for each of these parameters are wildly
unsuitable and unrealistic. I'll prove this assertion later in this paper,
but for now, suffice to say that OPTIMIZER_INDEX_CACHING should be set to 90
and OPTIMIZER_INDEX_COST_ADJ should be set to a value which usually ranges
between 10 and 50 for most online transaction processing (OLTP) systems, For
data warehousing or other decision-support systems (DSS), it might be
prudent to simply set this parameter to 50.
-----Original Message-----
Sent: Thursday, March 07, 2002 2:54 PM
To: Multiple recipients of list ORACLE-L
just changed index_cost_adj from 100 to 1
what is index_caching?
-----Original Message-----
Sent: Thu, March 07, 2002 3:04 PM
To: Multiple recipients of list ORACLE-L
have you tried adjusting optimzer_index_caching and optimizer_index_cost_adj?
-----Original Message-----
Sent: Thursday, March 07, 2002 1:23 PM
To: Multiple recipients of list ORACLE-L
I work in a dev shop - most of the sql is canned and pretty basic. We've
been running CBO in all of our dev environments, but we have a few long txns
that just take forever. At the request of some savvy developers, I turned
on RBO, and it brought down execution times dramatically.
I've been analyzing affected tables often (we do a lot of bulk load/unload
for testing), and have played with partitioning and clustering, particularly
on one table that's just a dog. CBO will always do a FTS where RBO uses the
PK to retrieve data.
Where to go next? I've been unable to alter the costs dramatically enough
to make any real difference in execution time.
thx
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Magaliff, Bill
INET: Bill.Magaliff_at_lendware.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Mar 07 2002 - 15:43:39 CST
 |
 |