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
"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 07:49 AM
Please respond to
ORACLE-L
Me thinks (based on my conversion from RBO->CBO) over last two months ... )
one major factor is achieving success is as follows
1. Train your developers/designers
2. See point one
3. Lather - rinse - Repeat
Some people just don't get it ... lot of people (in my limited experience)
think that CBO is the new RBO ... so if it worked perfect in RBO, it will
work better in CBO. Some also think that there are some 'hidden' parameters
that DBAs can set and all of a sudden my application will be flying (at
scramjet speeds).
We just ran into a problem with Spatial queries in 9201 database ... it
chokes with making a HJ ... so now we are putting in a on-logon trigger for
that specific user to do a alter session and set HJ_enabled to false.
Some things that we found ...
1. In 9201 there is a bug related to query_unnesting (yeah the same one
they discovered in 8071 and claimed to have fixed in 8174). we ended up
billing customers 1.5Million instead of 150K (luckily it was a test run and
was fixed before the actual run was done).
2. The spatial problem as mentioned above
3. Carefully (I mean very carefully) decide on
db_file_multiblock_read_count value ...
3.1 As it affects CBO to take different path
3.2 If you are using RAC, it also affects Global cache traffic
the bug is (claimed to be fixed in 9202).
4. You will see some queries drive like 5mph in the left lane ...
after careful analysis we have found much success with
optimizer_index_cost_adj parameter. Trust me I experimented
about 15 times before deciding a value of 10;
5. Decide BEFORE YOUR TESTING PHASE STARTS on what your statistics
collection be,
5.1 old 10% is out,
5.2 oracle recommends 25%
5.3 rather dbms_stats.auto_sample seems to be the current recommendation
5.4 if you'd like to do 100% i.e. COMPUTE.
Once you decide STICK TO IT NO MATTER WHAT because if you change that
CBO will think otherwise.
6. Use DBMS_STATS ... don't use ANALYZE ... also when doing
gather_table_stats
make sure you specify CASCADE => TRUE (I can't imagine why it is FALSE
by default).
7. Based on my emails earlier this year and a very good explanation from
Tim Gorman
and a couple of others on this list, we decided to put hints ONLY in
places where
CBO is consistently making wrong decisions.
8. If you want to use OUTLINES, make sure that the SQL HAS TO BE EXACT ...
else it won't work.
9. One important lesson I learned in tuning CBO queries is to TUNE THE SQL
WITH BIND VARIABLES ...
CBO at times chooses different path based on bind values or hard-coded
values.
there is more ... but it is enough to scare someone off ... personally I am
very comfortable with CBO, but it is the RBO tunes application that has
problems with CBO and my colleagues and I are working hard to make them
(the application and CBO) like each other.
Okay time to get me off my soapbox and do some work ... and yes I have
tapped the collective brain power on this list ... and the wisdom never
fails to amaze me. Thank you all..
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! (See
attached file: ESPN_Disclaimer.txt)
ESPN_Disclaimer.txt
Description: Binary data
