Oracle-L: RE: converting to the cost based optimizer

From: <Cherie_Machler_at_gelco.com>
Date: Thu, 21 Nov 2002 08:25:10 -0800
Message-ID: <F001.00508F59.20021121082510@fatcity.com>

Raj,

I'm just curious whether you also have reset your optimizer_index_ parameters in these databases where you have changed and/or are considering changing the db_file_multiblock_read_count? In particular, has it been set on the production database where you set db_file_multiblock_read_count to 4?

Thanks,

Cherie

                                                                                                                     
                    "Jamadagni,                                                                                      
                    Rajendra"                 To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>    
                    <Rajendra.Jamadagni       cc:                                                                    
                    @espn.com>                Subject:     RE: converting to the cost based optimizer                
                    Sent by:                                                                                         
                    root_at_fatcity.com                                                                                 
                                                                                                                     
                                                                                                                     
                    11/21/02 09:40 AM                                                                                
                    Please respond to                                                                                
                    ORACLE-L

Cherie,

We are actually stuck at a value of 32 ... don't know how it got in there, but it was probably us who made the mistake. That's how we tested it and that's how we are staying put. We have initiated a test process to bring it down to a value of 4 (most likely candidate) or 8. There are more than one reasons to do this.

As we all know it affects cost calculations and higher the value CBO thinks FTS is cheaper.
On AIX RAC, it also affects GC traffic, and we were looking at a high number of 'socket buffer overflows' (visible through netstat -p udp) which indicated GC traffic and buffer writing problems. The solution is to raise the udp_rcvsize and udp_sendsize to a higher value or reduce db_file_mb_count parameter to a respectable value of 4 or 8.

Also as we are watching the application and see that it is choosing wrong indexes with current setting of 32, and choosing different indexes at the setting of 4. We have seen this behavior in multiple cases. So we are leaning towards a value of 4. But as are nowhere near that as of now.

We did however change this parameter on one of the production instances, so far we haven't seen huge performance changes, the global cache traffic is much more manageable and we don't see as many socket buffer overflows ..

Bottom line, in the instance where we made the change to 4, it not faster, but it is behaving better (i.e. choosing right indexes) ... We did 10046 traces before and after the changes, looked at explain plans for critical processes and found that after bringing down the variable, things are better. How much you may ask? I don't know ... we haven't been able to quantify that 'how much?' yet. Some day we might, but at the moment other fires are keeping us busy. And the d^Hmanagement hasn't asked us that question yet as well.

And our db is a OLTP instance (all the way), I know these methods are no where near as scientific, but based on our observation .. it works for us.

Raj

Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!

-----Original Message-----
Sent: Thursday, November 21, 2002 9:54 AM To: Multiple recipients of list ORACLE-L

Raj,

Can you elaborate on the process that you used to determine the optimal value for db_file_multiblock_read_count?

We are doing some performance testing on a new generation of our product with some new databases. We increased our database block size from 8k to 16k (on Sun Solaris 2.6). db_file_multiblock_read_count is defaulted to 8. I wonder if we need to half the value to 4. We have both a small OLTP database and a medium-size DSS database that are on 9.2.0.1.

I've read through the documentation on db_file_multiblock_read_count that I

can find on Metalink but I can't find any specifics on calculating what value it should optimally be set at or how to test if it is correct.

What method did you use for setting the value and then for testing for optimal afterwards?

Thanks for sharing your experience with us.

Cherie Machler
Oracle DBA
Gelco Information Network (See attached file: ESPN_Disclaimer.txt)

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Cherie_Machler_at_gelco.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

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

application/octet-stream attachment: ESPN_Disclaimer.txt

Received on Thu Nov 21 2002 - 10:25:10 CST