|
|
 |
 |
 |
 |
Parameter to influence Oracle's Idea of IO Cost?
Hi,
this is on 10.2.0.1.0. I think I remember that there is a parameter
that will affect how Oracle costs IO but can't seem to find it (searched
DB Reference, Performance Tuning Guide, PL/SQL Package Reference, Web).
Does my memory fail me?
Do you know other ways to influence how Oracle costs single block reads
vs. multi block reads (apart from DB_FILE_MULTIBLOCK_READ_COUNT and
hinting that is)? Thank you!
Kind regards
robert
On 9 mayo, 12:48, Robert Klemme <shortcut @googlemail.com> wrote: > Hi, > this is on 10.2.0.1.0. I think I remember that there is a parameter
> that will affect how Oracle costs IO but can't seem to find it (searched
> DB Reference, Performance Tuning Guide, PL/SQL Package Reference, Web).
> Does my memory fail me?
> Do you know other ways to influence how Oracle costs single block reads
> vs. multi block reads (apart from DB_FILE_MULTIBLOCK_READ_COUNT and
> hinting that is)? Thank you!
> Kind regards
> robert
OPTIMIZER_INDEX_COST_ADJ HTH
Cheers
Carlos.
-----------------------------------------------Reply-----------------------------------------------
On May 9, 6:48 am, Robert Klemme <shortcut @googlemail.com> wrote: > Hi, > this is on 10.2.0.1.0. I think I remember that there is a parameter
> that will affect how Oracle costs IO but can't seem to find it (searched
> DB Reference, Performance Tuning Guide, PL/SQL Package Reference, Web).
> Does my memory fail me?
> Do you know other ways to influence how Oracle costs single block reads
> vs. multi block reads (apart from DB_FILE_MULTIBLOCK_READ_COUNT and
> hinting that is)? Thank you!
> Kind regards
> robert
There are several parameters, here are a few: OPTIMIZER_INDEX_CACHING: Helps correct the optimizer's assumption that all index reads are physical reads. This has an effect when multiple tables are joined using nested loops with index access for the inner (second) table, but also has an effect for cost calculation of in-list iteration. This parameter does not affect the cost of a single table indexed access path. OPTIMIZER_INDEX_COST_ADJ: Reduces the cost of single block reads,
which tends to reduce the tendency of excessive tablescans. The
downside is that due to rounding errors, the optimizer may decide to
use the wrong index. This is a percentage multiplier of the original
cost of the index access cost: 6891 * 68% = new cost of 4685.88
CPU costing statistics, which are easily visible in a 10053 trace
file. If CPU costing statistics are gathered during a period of
limited activity, the system may generate odd numbers, which will
affect costing of single block versus multiblock reads. For instance:
-------------------------------------------------------------------
SYSTEM STATISTICS INFORMATION
*****************************
Using WORKLOAD Stats
CPUSPEED: 486 millions instructions/sec
SREADTIM: 1 milliseconds
MREADTIM: 10 millisecons
MBRC: 13.000000 blocks
MAXTHR: 18690048 bytes/sec
-------------------------------------------------------------------
The above will lead the cost based optimizer to believe that a single
block read will require 1ms, while a multiblock read will require
10ms, and that 13 blocks can be retrieved in a single multiblock
read. I don't believe that DB_FILE_MULTIBLOCK_READ_COUNT is used for
cost calculations in Oracle 10g, but instead MBRC is used.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
-----------------------------------------------Reply-----------------------------------------------
On May 9, 6:48 am, Robert Klemme <shortcut @googlemail.com> wrote: > Hi, > this is on 10.2.0.1.0. I think I remember that there is a parameter
> that will affect how Oracle costs IO but can't seem to find it (searched
> DB Reference, Performance Tuning Guide, PL/SQL Package Reference, Web).
> Does my memory fail me?
> Do you know other ways to influence how Oracle costs single block reads
> vs. multi block reads (apart from DB_FILE_MULTIBLOCK_READ_COUNT and
> hinting that is)? Thank you!
> Kind regards
> robert
See Tom Kyte's Effective Oracle by Design chapter 6 ... OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ I think are what you are referring to. Tom's not a big believer in silver bullets or giving blind
recommendations but notes the default settings may not be good for
many systems and also notes that perhaps data warehouses might benefit
from values of 0 and 100 for those but oltp workloads might find 90
and 25 more appropriate ( perhaps ).
-----------------------------------------------Reply-----------------------------------------------
On 09.05.2007 13:06, Charles Hooper wrote:
> On May 9, 6:48 am, Robert Klemme <shortcut @googlemail.com> wrote: >> Hi, >> this is on 10.2.0.1.0. I think I remember that there is a parameter
>> that will affect how Oracle costs IO but can't seem to find it (searched
>> DB Reference, Performance Tuning Guide, PL/SQL Package Reference, Web).
>> Does my memory fail me?
>> Do you know other ways to influence how Oracle costs single block reads
>> vs. multi block reads (apart from DB_FILE_MULTIBLOCK_READ_COUNT and
>> hinting that is)? Thank you!
> There are several parameters, here are a few:
> OPTIMIZER_INDEX_CACHING: Helps correct the optimizer's assumption that
> all index reads are physical reads. This has an effect when multiple
> tables are joined using nested loops with index access for the inner
> (second) table, but also has an effect for cost calculation of in-list
> iteration. This parameter does not affect the cost of a single table
> indexed access path.
> OPTIMIZER_INDEX_COST_ADJ: Reduces the cost of single block reads,
> which tends to reduce the tendency of excessive tablescans. The
> downside is that due to rounding errors, the optimizer may decide to
> use the wrong index. This is a percentage multiplier of the original
> cost of the index access cost: 6891 * 68% = new cost of 4685.88
Yeah, I found those but believed there was a different parameter that directly affects SBR vs. MBR. Probably a case of stale memory. :-) However, your additional explanation is greatly appreciated as the Oracle docs are a bit sparse IMHO. Thank you! > CPU costing statistics, which are easily visible in a 10053 trace
> file. If CPU costing statistics are gathered during a period of
> limited activity, the system may generate odd numbers, which will
> affect costing of single block versus multiblock reads.
So are you suggesting to gather statistics during normal operation?
> For instance: > ------------------------------------------------------------------- > SYSTEM STATISTICS INFORMATION > ***************************** > Using WORKLOAD Stats > CPUSPEED: 486 millions instructions/sec > SREADTIM: 1 milliseconds > MREADTIM: 10 millisecons > MBRC: 13.000000 blocks > MAXTHR: 18690048 bytes/sec > ------------------------------------------------------------------- > The above will lead the cost based optimizer to believe that a single
> block read will require 1ms, while a multiblock read will require
> 10ms, and that 13 blocks can be retrieved in a single multiblock
> read. I don't believe that DB_FILE_MULTIBLOCK_READ_COUNT is used for
> cost calculations in Oracle 10g, but instead MBRC is used.
So you are saying that the optimizer does not use the DB parameter but what it gathers from the statistics? Did I get that right? Thanks again!
Kind regards
robert
-----------------------------------------------Reply-----------------------------------------------
On May 9, 2:30 pm, Robert Klemme <shortcut @googlemail.com> wrote:
> On 09.05.2007 13:06, Charles Hooper wrote: > > On May 9, 6:48 am, Robert Klemme <shortcut@googlemail.com> wrote:
> >> Hi,
> >> this is on 10.2.0.1.0. I think I remember that there is a parameter
> >> that will affect how Oracle costs IO but can't seem to find it (searched
> >> DB Reference, Performance Tuning Guide, PL/SQL Package Reference, Web).
> >> Does my memory fail me?
> >> Do you know other ways to influence how Oracle costs single block reads
> >> vs. multi block reads (apart from DB_FILE_MULTIBLOCK_READ_COUNT and
> >> hinting that is)? Thank you!
> > There are several parameters, here are a few:
> > OPTIMIZER_INDEX_CACHING: Helps correct the optimizer's assumption that
> > all index reads are physical reads. This has an effect when multiple
> > tables are joined using nested loops with index access for the inner
> > (second) table, but also has an effect for cost calculation of in-list
> > iteration. This parameter does not affect the cost of a single table
> > indexed access path.
> > OPTIMIZER_INDEX_COST_ADJ: Reduces the cost of single block reads,
> > which tends to reduce the tendency of excessive tablescans. The
> > downside is that due to rounding errors, the optimizer may decide to
> > use the wrong index. This is a percentage multiplier of the original
> > cost of the index access cost: 6891 * 68% = new cost of 4685.88
> Yeah, I found those but believed there was a different parameter that
> directly affects SBR vs. MBR. Probably a case of stale memory. :-)
> However, your additional explanation is greatly appreciated as the
> Oracle docs are a bit sparse IMHO. Thank you!
> > CPU costing statistics, which are easily visible in a 10053 trace
> > file. If CPU costing statistics are gathered during a period of
> > limited activity, the system may generate odd numbers, which will
> > affect costing of single block versus multiblock reads.
> So are you suggesting to gather statistics during normal operation?
> > For instance:
> > -------------------------------------------------------------------
> > SYSTEM STATISTICS INFORMATION
> > *****************************
> > Using WORKLOAD Stats
> > CPUSPEED: 486 millions instructions/sec
> > SREADTIM: 1 milliseconds
> > MREADTIM: 10 millisecons
> > MBRC: 13.000000 blocks
> > MAXTHR: 18690048 bytes/sec
> > -------------------------------------------------------------------
> > The above will lead the cost based optimizer to believe that a single
> > block read will require 1ms, while a multiblock read will require
> > 10ms, and that 13 blocks can be retrieved in a single multiblock
> > read. I don't believe that DB_FILE_MULTIBLOCK_READ_COUNT is used for
> > cost calculations in Oracle 10g, but instead MBRC is used.
> So you are saying that the optimizer does not use the DB parameter but
> what it gathers from the statistics? Did I get that right?
> Thanks again!
> Kind regards
> robert- Hide quoted text -
> - Hide quoted text -
>
system statistics invalidate those two parameters. Tom Kyte, in one his volumes, states system statistics supersede those parameters and are a better solution. --
Sybrand Bakker
Senior Oracle DBA
-----------------------------------------------Reply-----------------------------------------------
On May 9, 8:30 am, Robert Klemme <shortcut @googlemail.com> wrote:
> On 09.05.2007 13:06, Charles Hooper wrote: > > On May 9, 6:48 am, Robert Klemme <shortcut @googlemail.com> wrote: > >> Hi, > >> this is on 10.2.0.1.0. I think I remember that there is a parameter
> >> that will affect how Oracle costs IO but can't seem to find it (searched
> >> DB Reference, Performance Tuning Guide, PL/SQL Package Reference, Web).
> >> Does my memory fail me?
> >> Do you know other ways to influence how Oracle costs single block reads
> >> vs. multi block reads (apart from DB_FILE_MULTIBLOCK_READ_COUNT and
> >> hinting that is)? Thank you!
> > There are several parameters, here are a few:
> > OPTIMIZER_INDEX_CACHING: Helps correct the optimizer's assumption that
> > all index reads are physical reads. This has an effect when multiple
> > tables are joined using nested loops with index access for the inner
> > (second) table, but also has an effect for cost calculation of in-list
> > iteration. This parameter does not affect the cost of a single table
> > indexed access path.
> > OPTIMIZER_INDEX_COST_ADJ: Reduces the cost of single block reads,
> > which tends to reduce the tendency of excessive tablescans. The
> > downside is that due to rounding errors, the optimizer may decide to
> > use the wrong index. This is a percentage multiplier of the original
> > cost of the index access cost: 6891 * 68% = new cost of 4685.88
> Yeah, I found those but believed there was a different parameter that
> directly affects SBR vs. MBR. Probably a case of stale memory. :-)
> However, your additional explanation is greatly appreciated as the
> Oracle docs are a bit sparse IMHO. Thank you!
> > CPU costing statistics, which are easily visible in a 10053 trace
> > file. If CPU costing statistics are gathered during a period of
> > limited activity, the system may generate odd numbers, which will
> > affect costing of single block versus multiblock reads.
> So are you suggesting to gather statistics during normal operation?
> > For instance:
> > -------------------------------------------------------------------
> > SYSTEM STATISTICS INFORMATION
> > *****************************
> > Using WORKLOAD Stats
> > CPUSPEED: 486 millions instructions/sec
> > SREADTIM: 1 milliseconds
> > MREADTIM: 10 millisecons
> > MBRC: 13.000000 blocks
> > MAXTHR: 18690048 bytes/sec
> > -------------------------------------------------------------------
> > The above will lead the cost based optimizer to believe that a single
> > block read will require 1ms, while a multiblock read will require
> > 10ms, and that 13 blocks can be retrieved in a single multiblock
> > read. I don't believe that DB_FILE_MULTIBLOCK_READ_COUNT is used for
> > cost calculations in Oracle 10g, but instead MBRC is used.
> So you are saying that the optimizer does not use the DB parameter but
> what it gathers from the statistics? Did I get that right?
> Thanks again!
> Kind regards
> robert
Paraphrased during a second read through of "Cost-Based Oracle Fundamentals": Oracle uses MBRC from SYS.AUX_STATS$ for cost calculations, but the runtime engine tries to use DB_FILE_MULTIBLOCK_READ_COUNT to retrieve table data. Several of the other explanations were obtained from the same book or from Cary Millsap's "Optimizing Oracle Performance" book. Definitely gather system statistics when Oracle is under a normal to
heavy load. As the above shows, you may obtain wild statistics
otherwise.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
-----------------------------------------------Reply-----------------------------------------------
On May 9, 6:48 am, Robert Klemme <shortcut @googlemail.com> wrote: > Hi, > this is on 10.2.0.1.0. I think I remember that there is a parameter
> that will affect how Oracle costs IO but can't seem to find it (searched
> DB Reference, Performance Tuning Guide, PL/SQL Package Reference, Web).
> Does my memory fail me?
> Do you know other ways to influence how Oracle costs single block reads
> vs. multi block reads (apart from DB_FILE_MULTIBLOCK_READ_COUNT and
> hinting that is)? Thank you!
> Kind regards
> robert
What is the problem you try to address by chaging the optimizer behavior? -----------------------------------------------Reply-----------------------------------------------
On 09.05.2007 16:22, Valentin Minzatu wrote:
> On May 9, 6:48 am, Robert Klemme <shortcut @googlemail.com> wrote: >> Hi, >> this is on 10.2.0.1.0. I think I remember that there is a parameter
>> that will affect how Oracle costs IO but can't seem to find it (searched
>> DB Reference, Performance Tuning Guide, PL/SQL Package Reference, Web).
>> Does my memory fail me?
>> Do you know other ways to influence how Oracle costs single block reads
>> vs. multi block reads (apart from DB_FILE_MULTIBLOCK_READ_COUNT and
>> hinting that is)? Thank you!
>> Kind regards
>> robert
> What is the problem you try to address by chaging the optimizer
> behavior?
I'm trying to bias the CBO towards multi block reads. Kind regards
robert
-----------------------------------------------Reply-----------------------------------------------
On May 9, 10:37 am, Robert Klemme <shortcut @googlemail.com> wrote:
> On 09.05.2007 16:22, Valentin Minzatu wrote: > > On May 9, 6:48 am, Robert Klemme <shortcut@googlemail.com> wrote:
> >> Hi,
> >> this is on 10.2.0.1.0. I think I remember that there is a parameter
> >> that will affect how Oracle costs IO but can't seem to find it (searched
> >> DB Reference, Performance Tuning Guide, PL/SQL Package Reference, Web).
> >> Does my memory fail me?
> >> Do you know other ways to influence how Oracle costs single block reads
> >> vs. multi block reads (apart from DB_FILE_MULTIBLOCK_READ_COUNT and
> >> hinting that is)? Thank you!
> >> Kind regards
> >> robert
> > What is the problem you try to address by chaging the optimizer
> > behavior?
> I'm trying to bias the CBO towards multi block reads.
> Kind regards
> robert- Hide quoted text -
>
Why do you need to do that? (i.e. is this a DW system? ) -----------------------------------------------Reply-----------------------------------------------
On 09.05.2007 16:58, Valentin Minzatu wrote:
> On May 9, 10:37 am, Robert Klemme <shortcut @googlemail.com> wrote: >> On 09.05.2007 16:22, Valentin Minzatu wrote: >>> On May 9, 6:48 am, Robert Klemme <shortcut@googlemail.com> wrote:
>>>> Hi,
>>>> this is on 10.2.0.1.0. I think I remember that there is a parameter
>>>> that will affect how Oracle costs IO but can't seem to find it (searched
>>>> DB Reference, Performance Tuning Guide, PL/SQL Package Reference, Web).
>>>> Does my memory fail me?
>>>> Do you know other ways to influence how Oracle costs single block reads
>>>> vs. multi block reads (apart from DB_FILE_MULTIBLOCK_READ_COUNT and
>>>> hinting that is)? Thank you!
>>>> Kind regards
>>>> robert
>>> What is the problem you try to address by chaging the optimizer
>>> behavior?
>> I'm trying to bias the CBO towards multi block reads.
>> Kind regards
>> robert- Hide quoted text -
>>
> Why do you need to do that? (i.e. is this a DW system? )
Si. And experiments have shown that plans with MBR's perform significantly better than those without. And I'd prefer to use DB parameters or other means over hints. Regards
robert
-----------------------------------------------Reply-----------------------------------------------
On May 9, 11:51 am, Robert Klemme <shortcut @googlemail.com> wrote:
> On 09.05.2007 16:58, Valentin Minzatu wrote: > > On May 9, 10:37 am, Robert Klemme <shortcut@googlemail.com> wrote:
> >> On 09.05.2007 16:22, Valentin Minzatu wrote:
> >>> On May 9, 6:48 am, Robert Klemme <shortcut@googlemail.com> wrote:
> >>>> Hi,
> >>>> this is on 10.2.0.1.0. I think I remember that there is a parameter
> >>>> that will affect how Oracle costs IO but can't seem to find it (searched
> >>>> DB Reference, Performance Tuning Guide, PL/SQL Package Reference, Web).
> >>>> Does my memory fail me?
> >>>> Do you know other ways to influence how Oracle costs single block reads
> >>>> vs. multi block reads (apart from DB_FILE_MULTIBLOCK_READ_COUNT and
> >>>> hinting that is)? Thank you!
> >>>> Kind regards
> >>>> robert
> >>> What is the problem you try to address by chaging the optimizer
> >>> behavior?
> >> I'm trying to bias the CBO towards multi block reads.
> >> Kind regards
> >> robert- Hide quoted text -
> >>
> > Why do you need to do that? (i.e. is this a DW system? )
> Si. And experiments have shown that plans with MBR's perform
> significantly better than those without. And I'd prefer to use DB
> parameters or other means over hints.
> Regards
> robert- Hide quoted text -
>
You should have a very strong case when you make changes at the instance level as they impact everything else in ways that are not always predictable. The reason I asked you what are you trying to achieve was to see if there are no other ways to accomplish the same goal without impacting the whole (i.e. build another level on the top of the building without having to make changes to the foundation). If you are only to look at how much testing would involve validating the one change at the database level - that is a lot. Unless you have a single application against that database I would try to look into addressing the issue somehow else. -----------------------------------------------Reply-----------------------------------------------
> from values of 0 and 100 for those but oltp workloads might find 90
> and 25 more appropriate ( perhaps ).
Again generics... What about walking the parameter settings from for optimizer_index_cost_adj from 1-10000. Strange things happen at different numbers. A plan might be good at 42 but then bad at 43 and good again at 2000. It's all about the plan and the response time. Mr. Kyte has many
generics in his stuff, but he counters this by testing..... Don't use
Tom's or anyone else's numbers. Test for yourself.
What about permutations:
TABLE_NAME TABLE_TYPE COMMENTS
------------------------------ -----------
--------------------------------------------------------------------------- --------------
OPT1 TABLE The range of values is 1 to
10000
OPT2 TABLE The range of values 0 to
100
OPT3 TABLE possible values on in test
laptop 2,4,8,16,32,64,128
OPT4 TABLE possible values - all_rows,
first_rows, first_rows_n. n can equal 1,10,100,1000
Take the above settings and the possible permutations is:
N
----------------
42,420,000
That's a lot to test :)
So how do we apply generics to the possible 42,420,000 parameter
combinations represented.
I don't like generics...Everything is singular...so the only way to
know is to characterize the workload, but know that every change
creates a new system.
-----------------------------------------------Reply-----------------------------------------------
On May 9, 3:48 am, Robert Klemme <shortcut @googlemail.com> wrote: > Hi, > this is on 10.2.0.1.0. I think I remember that there is a parameter
> that will affect how Oracle costs IO but can't seem to find it (searched
> DB Reference, Performance Tuning Guide, PL/SQL Package Reference, Web).
> Does my memory fail me?
> Do you know other ways to influence how Oracle costs single block reads
> vs. multi block reads (apart from DB_FILE_MULTIBLOCK_READ_COUNT and
> hinting that is)? Thank you!
> Kind regards
> robert
The following google web search can be quite informative: costs single block read "jonathan lewis"
jg
--
@home.com is bogus. "This is why I conceived of Web Zero.0, Anti-
Social Software. I don't need more ways for people to network with me
via the internet, I need more ways to make them leave me alone. ASS
would eliminate all incentives to game the system, it recognizes that
the "collective intelligence" is full of annoying junk and tries to
block it from reaching the user. Subtract all the Web 2.0 crap from
the Internet and you're left with the remainder, Web Zero.0, which
must contain the few useful things not yet spoiled by mass
commercialization." - Charles
-----------------------------------------------Reply-----------------------------------------------
Robert Klemme wrote: > Hi,
> this is on 10.2.0.1.0. I think I remember that there is a parameter
> that will affect how Oracle costs IO but can't seem to find it (searched
> DB Reference, Performance Tuning Guide, PL/SQL Package Reference, Web).
> Does my memory fail me?
> Do you know other ways to influence how Oracle costs single block reads
> vs. multi block reads (apart from DB_FILE_MULTIBLOCK_READ_COUNT and
> hinting that is)? Thank you!
> Kind regards
> robert
short answer - optimizer_*** parameters as suggested before - tend to scale costs
down and result in more ties.
- system statistics as also suggested before - tend to scale costs up
and reflect your real hardware, both of which are good, but take much
more effort to collect in a way that is reasonable.
My guess is you might well get better immediate success with the
optimizer_... parameters, but better longer term success with system stats.
--
Niall Litchfield
Oracle DBA
http://www.orawin.info/services
-----------------------------------------------Reply-----------------------------------------------
On May 9, 6:48 am, Robert Klemme <shortcut @googlemail.com> wrote: > Hi, > this is on 10.2.0.1.0. I think I remember that there is a parameter
> that will affect how Oracle costs IO but can't seem to find it (searched
> DB Reference, Performance Tuning Guide, PL/SQL Package Reference, Web).
> Does my memory fail me?
> Do you know other ways to influence how Oracle costs single block reads
> vs. multi block reads (apart from DB_FILE_MULTIBLOCK_READ_COUNT and
> hinting that is)? Thank you!
> Kind regards
> robert
What does cost have to do with response time? Are you looking to speed something up or is this just like "tuning the cache hit ratio". -----------------------------------------------Reply-----------------------------------------------
On 10.05.2007 04:10, EscVector wrote:
> On May 9, 6:48 am, Robert Klemme <shortcut @googlemail.com> wrote: >> Hi, >> this is on 10.2.0.1.0. I think I remember that there is a parameter
>> that will affect how Oracle costs IO but can't seem to find it (searched
>> DB Reference, Performance Tuning Guide, PL/SQL Package Reference, Web).
>> Does my memory fail me?
>> Do you know other ways to influence how Oracle costs single block reads
>> vs. multi block reads (apart from DB_FILE_MULTIBLOCK_READ_COUNT and
>> hinting that is)? Thank you!
>> Kind regards
>> robert
> What does cost have to do with response time?
> Are you looking to speed something up or is this just like "tuning the
> cache hit ratio".
assumed IO cost => Plan => Performance where "=>" means influences.
robert
-----------------------------------------------Reply-----------------------------------------------
On 09.05.2007 17:59, Valentin Minzatu wrote:
> On May 9, 11:51 am, Robert Klemme <shortcut @googlemail.com> wrote: >> On 09.05.2007 16:58, Valentin Minzatu wrote: >>> On May 9, 10:37 am, Robert Klemme <shortcut@googlemail.com> wrote:
>>>> On 09.05.2007 16:22, Valentin Minzatu wrote:
>>>>> On May 9, 6:48 am, Robert Klemme <shortcut@googlemail.com> wrote:
>>>>>> Hi,
>>>>>> this is on 10.2.0.1.0. I think I remember that there is a parameter
>>>>>> that will affect how Oracle costs IO but can't seem to find it (searched
>>>>>> DB Reference, Performance Tuning Guide, PL/SQL Package Reference, Web).
>>>>>> Does my memory fail me?
>>>>>> Do you know other ways to influence how Oracle costs single block reads
>>>>>> vs. multi block reads (apart from DB_FILE_MULTIBLOCK_READ_COUNT and
>>>>>> hinting that is)? Thank you!
>>>>>> Kind regards
>>>>>> robert
>>>>> What is the problem you try to address by chaging the optimizer
>>>>> behavior?
>>>> I'm trying to bias the CBO towards multi block reads.
>>>> Kind regards
>>>> robert- Hide quoted text -
>>>>
>>> Why do you need to do that? (i.e. is this a DW system? )
>> Si. And experiments have shown that plans with MBR's perform
>> significantly better than those without. And I'd prefer to use DB
>> parameters or other means over hints.
> You should have a very strong case when you make changes at the
> instance level as they impact everything else in ways that are not
> always predictable. The reason I asked you what are you trying to
> achieve was to see if there are no other ways to accomplish the same
> goal without impacting the whole (i.e. build another level on the top
> of the building without having to make changes to the foundation). If
> you are only to look at how much testing would involve validating the
> one change at the database level - that is a lot. Unless you have a
> single application against that database I would try to look into
> addressing the issue somehow else.
It's a single application but you are right: changing global parameters will require more testing to make sure there are no negative effects. Thanks
robert
-----------------------------------------------Reply-----------------------------------------------
On May 10, 4:44 am, Robert Klemme <shortcut @googlemail.com> wrote:
> On 10.05.2007 04:10, EscVector wrote: > > On May 9, 6:48 am, Robert Klemme <shortcut@googlemail.com> wrote:
> >> Hi,
> >> this is on 10.2.0.1.0. I think I remember that there is a parameter
> >> that will affect how Oracle costs IO but can't seem to find it (searched
> >> DB Reference, Performance Tuning Guide, PL/SQL Package Reference, Web).
> >> Does my memory fail me?
> >> Do you know other ways to influence how Oracle costs single block reads
> >> vs. multi block reads (apart from DB_FILE_MULTIBLOCK_READ_COUNT and
> >> hinting that is)? Thank you!
> >> Kind regards
> >> robert
> > What does cost have to do with response time?
> > Are you looking to speed something up or is this just like "tuning the
> > cache hit ratio".
> assumed IO cost => Plan => Performance
> where "=>" means influences.
> robert
So this is like tuning the cache hit ratio. A larger plan cost does not necessarily mean worse response time.
I see examples every day where a high cost plan out performs a lower
cost plan.
This is why I ask about Response Time.
It is all well and good to analyze at intervals if the understanding
is that the analysis creates a new system each time. Plans can and
will change with each new analysis. Plans will change with each
setting tweak. This introduces lots of instability.
All necessary settings and analysis is easily performed via ALTER
SESSION and tracing so that no assumptions are necessary.
I'm a Method-R proponent. "Tuning" by assumption is as good as
tweaking the cache hit ratio.
Rather than working with unknowns, start by identifying if the system
needs to be optimized, determine bottlenecks, and then work to
understand and mitigate the slowness.
Working on non-bottlenecks can lead to CTD and impact your system in
non-good ways. Don't guess or assume, determine the worst bottleneck
and establish the optimization goal prior to start. Work towards the
goal.
A good book:
Optimizing Oracle Performance
http://www.bookpool.com/sm/059600527X
Notice the title does not include the word "Tune" which refers to a
single finite piece. It uses optimize and refers to the entire
system, soup to nuts, by focusing on response time and working on only
on the processes that need optimization.
If I/O latency or single-block reads is actually the problem, then ok,
but make sure that it is before spending lots of time. Also make sure
you check out http://www.bookpool.com/sm/1590596366
Cost-Based Oracle Fundamentals. Chapter 2 has some good information
on cost.
-----------------------------------------------Reply-----------------------------------------------
On 10.05.2007 16:59, EscVector wrote:
> On May 10, 4:44 am, Robert Klemme <shortcut @googlemail.com> wrote: >> On 10.05.2007 04:10, EscVector wrote: >>> On May 9, 6:48 am, Robert Klemme <shortcut@googlemail.com> wrote:
>>>> Hi,
>>>> this is on 10.2.0.1.0. I think I remember that there is a parameter
>>>> that will affect how Oracle costs IO but can't seem to find it (searched
>>>> DB Reference, Performance Tuning Guide, PL/SQL Package Reference, Web).
>>>> Does my memory fail me?
>>>> Do you know other ways to influence how Oracle costs single block reads
>>>> vs. multi block reads (apart from DB_FILE_MULTIBLOCK_READ_COUNT and
>>>> hinting that is)? Thank you!
>>>> Kind regards
>>>> robert
>>> What does cost have to do with response time?
>>> Are you looking to speed something up or is this just like "tuning the
>>> cache hit ratio".
>> assumed IO cost => Plan => Performance
>> where "=>" means influences.
>> robert
> So this is like tuning the cache hit ratio.
> A larger plan cost does not necessarily mean worse response time.
> I see examples every day where a high cost plan out performs a lower
> cost plan.
> This is why I ask about Response Time.
> It is all well and good to analyze at intervals if the understanding
> is that the analysis creates a new system each time. Plans can and
> will change with each new analysis. Plans will change with each
> setting tweak. This introduces lots of instability.
> All necessary settings and analysis is easily performed via ALTER
> SESSION and tracing so that no assumptions are necessary.
> I'm a Method-R proponent. "Tuning" by assumption is as good as
> tweaking the cache hit ratio.
> Rather than working with unknowns, start by identifying if the system
> needs to be optimized, determine bottlenecks, and then work to
> understand and mitigate the slowness.
> Working on non-bottlenecks can lead to CTD and impact your system in
> non-good ways. Don't guess or assume, determine the worst bottleneck
> and establish the optimization goal prior to start. Work towards the
> goal.
> A good book:
> Optimizing Oracle Performance
> http://www.bookpool.com/sm/059600527X
> Notice the title does not include the word "Tune" which refers to a
> single finite piece. It uses optimize and refers to the entire
> system, soup to nuts, by focusing on response time and working on only
> on the processes that need optimization.
> If I/O latency or single-block reads is actually the problem, then ok,
> but make sure that it is before spending lots of time. Also make sure
> you check out http://www.bookpool.com/sm/1590596366
> Cost-Based Oracle Fundamentals. Chapter 2 has some good information
> on cost.
Thanks for the info! I can assure you that it is pretty well established that the plans including FIS perform significantly better than the ones that lead to a lot of individual block reads. IO waits make up for 90+% of the query execution time. This was tested via hints and now I am looking into methods that will do without hints as the IO latency is a general property of the IO system (an NFS mounted filer). Kind regards
robert
-----------------------------------------------Reply-----------------------------------------------
"Robert Klemme" <shortcut @googlemail.com> wrote in message ... > Thanks for the info! I can assure you that it is pretty well established
> that the plans including FIS perform significantly better than the ones
> that lead to a lot of individual block reads. IO waits make up for 90+%
> of the query execution time. This was tested via hints and now I am
> looking into methods that will do without hints as the IO latency is a
> general property of the IO system (an NFS mounted filer).
> Kind regards
> robert
Have a look at the following: http://jonathanlewis.wordpress.com/2007/04/30/system-statistics/ Based on your last comment, you could do the following:
set the db_file_multiblock_read_count to 128
Run a test (outside of Oracle) which does 8KB random reads
from a very large file, then does 1MB random reads, and record
the average read time. Use dbms_stats.set_system_stats to set:
MBRC to 128
sreadtim to the 8KB read time
mreadtim to the 1MB read time
This should have allowed you to measure the capability of
your NFS filer, and tell Oracle about it so that it factors time
into its plans more appropriately.
Since the filer might do readahead on large reads in real
tablescans, you might also measure the speed of doing
(say) 4MB random reads, and using that time, divided by 4
as the mreadtim for 128 blocks.
(I am assuming you have a block size of 8K).
--
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
-----------------------------------------------Reply-----------------------------------------------
Robert Klemme wrote: >> What does cost have to do with response time? >> Are you looking to speed something up or is this just like "tuning the >> cache hit ratio". > assumed IO cost => Plan => Performance
> where "=>" means influences.
> robert
A very weak assumption. I would recommend the books written by Tom Kyte, Cary Milsap, and Jonathan Lewis as therapy. -- Daniel A. Morgan University of Washington damor @x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org -----------------------------------------------Reply-----------------------------------------------
On May 10, 12:16 pm, DA Morgan <damor @psoug.org> wrote:
> Robert Klemme wrote: > >> What does cost have to do with response time? > >> Are you looking to speed something up or is this just like "tuning the > >> cache hit ratio". > > assumed IO cost => Plan => Performance
> > where "=>" means influences.
> > robert
> A very weak assumption. I would recommend the books written by Tom Kyte,
> Cary Milsap, and Jonathan Lewis as therapy.
> --
> Daniel A. Morgan
> University of Washington
> damor@x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org
I believe that I read Robert's comment differently than you. A 10053 trace seems to indicate that the Cost Based optimizer in Oracle determines the "best" plan based on the "assumed IO cost" - a plan with a higher "assumed IO cost" is discarded as soon as the plan exceeds the previously generated "assumed IO cost" for a previously generated plan. If Oracle is determining the actual cost incorrectly, that will potentially drive Oracle to produce a less than ideal plan, that will then adversely affect performance. It appears that Robert would like to correct Oracle's assumed IO cost so that it automatically generates appropriate plans (most of the time). I apologize to Robert if I misunderstood his comment. If the above is not what Robert intended, then I second the book
recommendations.
Robert, what is the value of OPTIMIZER_MODE?
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
-----------------------------------------------Reply-----------------------------------------------
On 10.05.2007 18:40, Charles Hooper wrote: > On May 10, 12:16 pm, DA Morgan <damor @psoug.org> wrote: >> Robert Klemme wrote: >>>> What does cost have to do with response time? >>>> Are you looking to speed something up or is this just like "tuning the >>>> cache hit ratio". >>> assumed IO cost => Plan => Performance >>> where "=>" means influences. >>> robert >> A very weak assumption. I would recommend the books written by Tom Kyte, >> Cary Milsap, and Jonathan Lewis as therapy. Daniel, I am not sure why you call this a "weak assumption". Of course assumed IO cost is not the only variable that the CBO takes into account - but I don't believe that it will ignore it. And the fact that the chosen plan influences performance seems to be well established. > I believe that I read Robert's comment differently than you. A 10053
> trace seems to indicate that the Cost Based optimizer in Oracle
> determines the "best" plan based on the "assumed IO cost" - a plan
> with a higher "assumed IO cost" is discarded as soon as the plan
> exceeds the previously generated "assumed IO cost" for a previously
> generated plan. If Oracle is determining the actual cost incorrectly,
> that will potentially drive Oracle to produce a less than ideal plan,
> that will then adversely affect performance. It appears that Robert
> would like to correct Oracle's assumed IO cost so that it
> automatically generates appropriate plans (most of the time). I
> apologize to Robert if I misunderstood his comment.
No, you're spot on! > If the above is not what Robert intended, then I second the book > recommendations. > Robert, what is the value of OPTIMIZER_MODE?
all_rows Thanks to all for the suggestions and feedback! It'll take me some time
to look into Jonathan suggestions.
Kind regards
robert
-----------------------------------------------Reply-----------------------------------------------
DA Morgan wrote: > Robert Klemme wrote: >>> What does cost have to do with response time?
>>> Are you looking to speed something up or is this just like "tuning the
>>> cache hit ratio".
>> assumed IO cost => Plan => Performance
>> where "=>" means influences.
>> robert
> A very weak assumption. I would recommend the books written by Tom Kyte,
> Cary Milsap, and Jonathan Lewis as therapy.
I'm not sure that I would call it a very weak assumption, in fact I'd call it really quite a good assumption. Up until very recently Oracle's costing model was based pretty much entirely on attempting to cost things in units of IO. Now these days there are also attempts to cost the CPU resources being used if system stats have been gathered, but never the less IO cost is a very large contributor to most calculated costs, the cost drives the plan which is adopted and plan choice can hugely influence performance. --
Niall Litchfield
Oracle DBA
http://www.orawin.info/services
|
 |
 |
 |
 |
|
Add to del.icio.us |
Digg this |
Stumble it |
Powered by Megasolutions Inc