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.
1. As we all know it affects cost calculations and higher the value CBO thinks FTS is cheaper.
2. 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-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Thursday, November 21, 2002 9:54 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: converting to the cost based optimizer
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
*********************************************************************This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*********************************************************************1
