| 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: good value for optimizer_index_cost_adj
Best place to look is probably Tim Gorman's paper titled something like 'The search for intelligent life'. To be found on www.evdbt.com
His argument, which I think is very sound, is that the "most correct" value for the parameter is the relative cost of a single block read compared to a multi block read.
This is captured in oracle 9 through system_stats where you can capture for a given time period:
average single block read time
average multiblock read time
average actual size of multiblock read.
I'll leave it to Tim's paper to give you guidelines on estimating the average times and average multiblock read size.
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Author of:
Practical Oracle 8i: Building Efficient Databases
Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html
Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html
-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Date: 24 April 2002 16:02
Hi,
Oracle 817/Solaris 8.
Users are doing select joining using the PKs of 2 partitionned tables. Partitionned key and the primary key are the same.
The access plan is a nested loop with a full table
scan on the first table which hold 700 000 rows.
The block size is 16K, I assume that's why Oracle is
doing FTS.
By using optimizer_index_cost_adj, I can make Oracle
use the PK of the first table. I've used 50 as a value
for optimizer_index_cost_adj.
Is that too much ?
Where can I get some metrics on that parameter ?
TIA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jonathan Lewis
INET: jonathan_at_jlcomp.demon.co.uk
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 Wed Apr 24 2002 - 12:28:34 CDT
 |
 |