| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: performance in 10g
Forget about optimizer_index_cost_adj parameter. Gather system
statistics. Otherwise Oracle 10g will use noworkload system stats.
Then look at the system stats. What was your
db_file_multiblock_read_count setting? Make sure the system statistic
MBRC is not too far off that. Make sure mreadtim is greater than
sreadtim. Whether it is or is not, make the ratio correspond at least
to the multiblock read fudge factor for MBRC - e.g. for MBRC=8 the
ratio would be 1.21, higher for higher MBRC values. Since you are
coming from rule-based you may want to favour index accesses, i.e.
single block reads, so you may want to make mreadtim 10-20 time
sreadtim. Another/additional way to favour index access and NL joins
is to set optimizer_mode=first_rows_n.
At 06:23 AM 1/25/2007, David Boyd wrote:
>Hi List,
>
>We upgraded our 9.2.0.6 database to 10.2.0.2. Our database is kind
>of data warehouse. We see worse performance for many queries that
>we benchmarked in 10g. We used rule-base optimizer in 9i and
>expected we have to tune some queries with cost-base optimizer
>individually. I was wondering what we can tune at database level
>first. We have done testing at various optimizer_index_cost_adj
>values and no big difference found. Any advice will be highly appreciated.
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jan 25 2007 - 08:30:51 CST
 |
 |