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 <[EMAIL PROTECTED]>
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
=====
St�phane Paquette
DBA Oracle, consultant entrep�t de donn�es
Oracle DBA, datawarehouse consultant
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jonathan Lewis
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
