Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Estimating OPTIMIZER_INDEX_COST_ADJ From V$SYSTEM_EVENT?

Re: Estimating OPTIMIZER_INDEX_COST_ADJ From V$SYSTEM_EVENT?

From: Don Burleson <don_at_burleson.cc>
Date: 18 Jan 2003 16:39:21 -0800
Message-ID: <998d28f7.0301181639.65cab741@posting.google.com>


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 Waits   
  cost
Full Scan Read I/O Index Read I/O for Full Scans for Index Scans

   adj

------------------ ----------------- -------------- ---------------
---------
             1.473              .289            .02             .98   
    20

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US