Re: Perf tuning OPTIMIZER_INDEX_COST

From: <John.Dailey_at_ing-fsi-na.com>
Date: Thu, 09 Aug 2001 18:08:25 -0700
Message-ID: <F001.0036690E.20010809173534@fatcity.com>

Rich, It sounds like you just started your instance up not too long ago.... the statistics in v$system_event are going to be badly skewed. I'd definitely wait for some period of time to allow for more usable statistics to build. Remember, each time you bounce the instance you are going to reset the wait stats. Since your scattered read time is so high I'd look to the actual # of waits compared to the rest of your events.... it is probably very low. If this database HAS been up for a while and you do a lot of full table scans, you probably have an I/O throughput or data distribution problem causing such high average times. It's hard to make much of a determination based off a newly started instance.

On a related note, I read the same white paper a while back and messed around with the parameters a little and took some timings.... it didn't help us too much here (marginal improvement for a few major queries I tested it against). I can give you more details if you are interested. I really didn't give it a full workout though; I'm sure it is useful in the right environment. We are on 8.0.5 and AIX 4.3.3.

I'd say tune the SQL as best as you can before you touch these parameters. You'll get more bang for your buck and I'm sure there's lots of queries that ran good under RBO that are suddenly going to be dogs in CBO and vice-versa. Good old-fashioned tuning fun!

HTH and YMMV....

John Dailey
Oracle DBA
Atlanta, GA

All opinions are my own yadda yadda

                                                                                       
                              
                    "Jesse, Rich"                                                      
                              
                    <Rich.Jesse_at_qti        To:     Multiple recipients of list 
ORACLE-L <ORACLE-L_at_fatcity.com>       
                    world.com>             cc:                                         
                              
                    Sent by:               Subject:     Perf tuning 
OPTIMIZER_INDEX_COST_ADJ                         
                    root_at_fatcity.co                                                    
                              
                    m                                                                  
                              
                                                                                       
                              
                                                                                       
                              
                    08/09/2001                                                         
                              
                    12:55 PM                                                           
                              
                    Please respond                                                     
                              
                    to ORACLE-L

After just migrating from 8.0.6 to 8.1.7 and RBO to CBO, I've been researching about perf tuning and have come across the init.ora parameter OPTIMIZER_INDEX_COST_ADJ. The guide, by Tim Gorman of evdbt.com, explained that it's default value is 100 (percent), but should probably be in the 10-50 range for OLTP systems. It recommended using the following query as a
guideline to set this:

           SELECT event, average_wait
           FROM v$system_event
           WHERE event LIKE 'db file s%read';

...and to take the ratio of "db file sequential read" to "db file scattered read" as a value for OPTIMIZER_INDEX_COST_ADJ. That may be fine for most systems, but our ratio is around .08%. No, it's not 8% -- and it's not a decimal problem. I'm getting this:

           db file sequential read        1.83687542789106
           db file scattered read         2151.12743289383

I'm reasonably certain that the seemingly outrageous "2151.127" is due to our use of HP's AutoRAID (SLOWWWWWW!) -- we won't be getting one in our next
box.

So, I'm a little skiddish about changing the default value from 100 to 1 or even 10, based on the above query alone. The scattered read average wait seems to be dropping steadily (it's now down to ~1900 over a period of 14 hours), so I'm trying to provide a "good" value for this parm without adversely affecting the optimizer.

Anyone have any ideas?

TIA!

Rich Jesse                          System/Database Administrator
Rich.Jesse_at_qtiworld.com             Quad/Tech International, Sussex, WI USA

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.com

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).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: John.Dailey_at_ing-fsi-na.com

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 Thu Aug 09 2001 - 20:08:25 CDT

Re: Perf tuning OPTIMIZER_INDEX_COST_ADJ