No .... I should have clarified ... so far we have made changes to o_i_c_a parameter at session level only ... we haven't dared it change at system level. I read through Tim's paper ('Searching for ...') and try to came up with an appropriate value, but for my db this is the (always) scenario ...
oraclei@ariel-NCS2> sys
SQL*Plus: Release 9.2.0.1.0 - Production on Thu Nov 21 11:36:43 2002
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected.
SQL> SELECT EVENT, AVERAGE_WAIT FROM V$SYSTEM_EVENT WHERE EVENT LIKE 'db file s%';
EVENT AVERAGE_WAIT
---------------------------------------------------------------- ------------
db file sequential read 0
db file scattered read 0
db file single write 0
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
So, looks like until we hit 9202 we can't use this ratio (sequential/scattered) reads... does anyone know of a equiv sql using x$ tables ??
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 11:22 AM
To: [EMAIL PROTECTED]
Cc: Jamadagni, Rajendra
Subject: RE: converting to the cost based optimizer
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 <[EMAIL PROTECTED]>
<Rajendra.Jamadagni cc:
@espn.com> Subject: RE: converting to the cost based optimizer
Sent by:
[EMAIL PROTECTED]
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.
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 (See attached file: ESPN_Disclaimer.txt)
********************************************************************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.*********************************************************************2
