Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Estimating OPTIMIZER_INDEX_COST_ADJ From V$SYSTEM_EVENT?
Great Concept! Is it possible to intelligently determine the optimal
setting for optimizer_index_cost_adj?
Let's examine the issue. Please correct me if I'm wrong here as I'm on my 15th cup of coffee and totally wired! The relative speed of performing a full-table (SOFTS) scan depends on:
Assuming:
Here is what I get when I apply these assumptions to a script:
col c1 heading 'Average Waits for|Full Scan Read I/O' format
999.999
col c2 heading 'Average Waits for|Index Read I/O' format
999.999
col c3 heading 'Percent of| I/O Waits|for Full Scans' format
9.99
col c4 heading 'Percent of| I/O Waits|for Index Scans' format
9.99
col c5 heading 'Starting|Value|for|optimizer|index|cost|adj' format
999
select
a.average_wait c1, b.average_wait c2,a.total_waits /(a.total_waits + b.total_waits) c3, b.total_waits /(a.total_waits + b.total_waits) c4, (b.average_wait / a.average_wait)*100 c5 -- a.total_waits /(a.total_waits + b.total_waits) * -- (b.average_wait / a.average_wait)*100 c6 from
v$system_event a,
v$system_event b
where
a.event = 'db file scattered read'
and
b.event = 'db file sequential read'
;
Starting
Value
for
optimizer
Percent of Percent of index Average Waits for Average Waits for I/O Waits I/O Waitscost
adj
------------------ ----------------- -------------- --------------- --------- 1.473 .289 .02 .9820
As you can see from the comments in my script, I wonder how we can "weight" this starting value for optimizer_index_cost_adj because I see that my system has 98% waits on index scans (and therefore a typical OLTP system with few full-table scans). I want to weight the starting value lower, with a lower limit of 10.
Any ideas? Received on Sat Jan 18 2003 - 18:39:21 CST