Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: performance in 10g

Re: performance in 10g

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Thu, 25 Jan 2007 07:30:51 -0700
Message-Id: <20070125142949.8C0F75B3E0A@turing.freelists.org>


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



This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jan 25 2007 - 08:30:51 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US