Re: Optimizer issue - cost of full table scans
- From: Andrew Kerber <andrew.kerber@xxxxxxxxx>
- To: Kerry Osborne <kerry.osborne@xxxxxxxxxxx>
- Date: Tue, 14 Sep 2010 08:44:49 -0500
I did have a conversation with an oracle consultant who has worked on
exadata several months ago. He (smugly) told me that the first step in
tuning oracle for exadata is removing indexes... So, if the index is
causing a problem, why not drop it?
On Tue, Sep 14, 2010 at 8:36 AM, Kerry Osborne <kerry.osborne@xxxxxxxxxxx>wrote:
> Well I hate to say I agree with using that parameter, because in general I
> am dead set against "big knob" tuning like this, but in this case I think it
> might be a valid approach. Brad and I had an offline discussion and he has a
> done a pretty thorough analysis of the issue. It does appear that the
> optimizer will favor a NL join using an index over a hash join using full
> table scans once it gets past a certain threshold. Unfortunately, the hash
> join is still light years faster when it crosses that threshold. I believe
> that the optimizer is completely unaware of how much better full scans can
> be when using Exadata storage (someone please correct me if I'm wrong on
> this point). But if that's the case, then it stands to reason that there
> will be situations where the optimizer will favor index usage where it
> probably shouldn't. That's because the optimizer just doesn't know how fast
> the table scans can be. Removing indexes is a valid option (or making them
> invisible), but in some mixed usage scenarios this is not really possible.
> So I'm thinking that this may actually work pretty well. The parameter
> basically applies a fudge factor to the optimizer's calculations saying
> index access is faster or slower. So setting it to a high value makes it
> look like index access is slower. In this type of mixed use Exadata
> environment this seems to make some sense and in fact Brad has had some
> success with setting this parameter at the session level for this particular
> set of statements already. I need to do some testing though to verify the
> theory though.
>
> Feel free to jump in and correct me anywhere I mispoke Brad.
>
> Kerry Osborne
> Enkitec
> blog: kerryosborne.oracle-guy.com
>
>
>
>
>
>
> On Sep 14, 2010, at 7:33 AM, Andrew Kerber wrote:
>
> Did someone already mention the optimizer_index_cost_adj setting? Sets the
> relative cost of index use versus table scans, Set it at something like 200
> would probably force full table scans. I have never tried that though.
>
> On Tue, Sep 14, 2010 at 4:05 AM, Niall Litchfield <
> niall.litchfield@xxxxxxxxx> wrote:
>
>> Well, system stats generally boil down to what happens to sreadtim and
>> mreadtim and the ratio between them. I'm not clear what would happen on a
>> Database Machine, but I can imagine it *might* break the assumptions of the
>> system stat costing model, not least that Multi Block reads are slower than
>> single block reads.
>>
>> I personally on a non-exadata enabled machine would deal with the
>> stability issue that Greg mentions by grabbing system stats repeatedly over
>> a period (a week or a month most likely) to a user defined stattab and then
>> sticking them in a spreadsheet to determine what values are representative
>> of the system. You can then just set the stats once and for all. After all
>> I/O and CPU capability *rarely *changes in a normal server (VMs of course
>> blow this assumption out of the water). I may be overly optimistic but I
>> personally believe (and it is mostly a matter of logic/faith sadly I don't
>> have enough actual data to say one way or the other) that representative
>> system statistics are likely to be a sensible choice for *most* systems -
>> certainly ones where the OICA and OIC tricks are being used.
>>
>> On 13 Sep 2010 21:43, "Job Miller" <jobmiller@xxxxxxxxx> wrote:
>>
>> Niall,
>>
>> I don't know how system stats are calculated, but if they are measured
>> from the perspective of the database server, wouldn't they reflect the
>> benefits of exadata storage?
>>
>>
>> The server knows it needs to do a FTS, the high water mark says that
>> requires reading N blocks, exadata storage skips 50% of the blocks via the
>> storage indexes, and the multi-block read times from the servers perspective
>> look really fast because storage optimized away some of the i/o.
>>
>> If that's true, the system stats would measure the typical system benefit
>> from exadata storage experienced during the system stats workload capture.
>>
>> Does that make sense? What am I missing?
>>
>>
>> > How does one maintain how much of a table is on disk vs memory? A
>> > costing model that considers all those cases becomes increasingly more
>>
>> > difficult to program and manage.
>> The costing model doesn't really attempt to consider all that though does
>> it? I just thought that it measures the average system-wide performance
>> based on your workload during capture. Hopefully what happens during that
>> workload represents your cache ratios for data, your data placement ratios
>> and your true typical multi-block average read rates.
>>
>> That average system-wide multi-block read should lead to pretty good plans
>> for everything. instead of good only for a few special cases.
>>
>> --- On Mon, 9/13/10, Greg Rahn <greg@xxxxxxxxxxxxxxxxxx> wrote:
>>
>> > From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
>> > Subject: Re: Optimizer issue - cost of full table scans
>> > To: "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
>> > Cc: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
>> > Date: Monday, September 13, 2010, 2:16 PM
>>
>> > There are no special changes as of
>> > today. When I made the reference
>> > to representative stats, ...
>>
>> > --
>> > //www.freelists.org/webpage/oracle-l
>> >
>> >
>> >
>>
>>
>>
>>
>>
>
>
> --
> Andrew W. Kerber
>
> 'If at first you dont succeed, dont take up skydiving.'
>
>
>
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
Other related posts:
