Re: Estimating OPTIMIZER_INDEX_COST_ADJ From V$SYSTEM_EVENT?

basis_consultant_at_hotmail.com (SAP BASIS Consultant) wrote in message news:<dd2036f3.0301160729.37609f97_at_posting.google.com>...

> ..... The AVERAGE_WAIT column contains the average timing, in 1/100ths
> of a second, of these events:
> EVENT AVERAGE_WAITS
> ========================= ==============
> db file sequential reads .33178629
> db file scattered reads 2.190087
>
> In this example, indexed scan I/O requests takes only 15% as long as
> each FULL table scan I/O request. So, set OPTIMIZER_INDEX_COST_ADJ to
> 15.
>
>

My only problem with this is that if you set the parameter to a new setting and measure again, you get a different value. Which means it is not an absolute measure but a relative one. It then defeats the purpose of using it as an absolute metric for adjusting the parameter.

Having said that, I've got nothing against using this or any other methodology as a way of establishing a *starting point* for your tuning. Just don't stop there. Measure again and make sure that overall you're getting a good balance and good performance. Ie, without *hot spot* sql statements.

Cheers
Nuno Souto
nsouto_at_optusnet.com.au.nospam Received on Thu Jan 16 2003 - 19:30:09 CST