Re: Optimizer issue - cost of full table scans
Date: Tue, 14 Sep 2010 07:33:32 -0500
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_at_gmail.com> 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_at_yahoo.com> wrote:
> 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_at_structureddata.org> wrote:
> > From: Greg Rahn <greg_at_structureddata.org>
> > Subject: Re: Optimizer issue - cost of full table scans
> > To: "Niall Litchfield" <niall.litchfield_at_gmail.com>
> > Cc: "ORACLE-L" <oracle-l_at_freelists.org>
> > Date: Monday, September 13, 2010, 2:16 PM
> > There are no special changes as of
> > today. When I made the reference
> > to representative stats, ...
> > --
> > http://www.freelists.org/webpage/oracle-l
-- Andrew W. Kerber 'If at first you dont succeed, dont take up skydiving.' -- http://www.freelists.org/webpage/oracle-lReceived on Tue Sep 14 2010 - 07:33:32 CDT