Re: System statistics
Date: Mon, 19 Aug 2013 22:08:57 +0100
Message-ID: <CAOCOAV+LKtAeDMN8j5_epQsSxCSsyBF4OfUvs6id=jm-H0PKqg_at_mail.gmail.com>
I believe that the 'official' line is: you should leave system statistics at their default values unless using exedata, where there is a specific exedata system statistics gathering mode. The justifications for this are:
- While it is relatively easy to gather representative OLTP statistics, it is much harder to gather representative DW statistics.
- Oracle does no regression testing of the optimiser in point releases against customised system statistics. I.e you are more likely to encounter obscure bugs or unexpected behaviour if you have custom system statistics.
- If you raise performance problems with Oracle, there will be an added layer of complexity for the support staff if non standard system statistics are employed.
Unfortunately I haven't seen these recomendations in writing.
I think that there is an obvious insight here that Oracle support has obviously encountered issues where problems have been caused by bad or inappropriate system statistics. Or to quote, Oracle believe they may have created a monster.
While obviously custom system stats can be useful, I think Oracle are implying that the level of expertise required to use this feature safely is higher than they may have implied in the past.
Dave
PS. Still think your joint session with Jonathan Lewis on hints is the best
UKOUG session I have ever attended!
On 17 Aug 2013 17:33, "Tony Hasler" <tony_at_aberdour.demon.co.uk> wrote:
> I am trying to get solicit some opinions on the apparently divisive topic
> of
> system statistics.
>
>
> There seems to be two areas of dispute. The first mildly contentious area
> involves whether gathering system statistics, workload or no-workload, is
> likely to generate reasonable results. Let us not discuss that. If the
> figures aren't right you can set them. For me, the more interesting
> question is: why bother at all?
>
>
>
> We know from experience that when the CBO makes a cardinality error of a
> factor of 2 or 3 then no great harm is likely to befall us; normally such
> errors either have no effect on the selected plan or the change in plan
> brought on by the cardinality error isn't particularly disastrous.
> Disastrous execution plans are typically only brought about by cardinality
> errors of a factor of 10 or, more usually, much more.
>
>
>
> Given our tolerance for cardinality errors why are we so concerned about
> accurately quantifying the size and performance of multi-block reads? Why
> not just delete system statistics and set DB_FILE_MULTIBLOCK_READ_COUNT to
> 0
> (implying the maximum possible I/O size) and be done with it? The CBO
> might
> be out by a factor of 2 or 3 in costing a full table scan but then
> cardinality errors are likely to have a similar effect on the costing of
> indexed access.
>
>
>
> --Tony Hasler
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Aug 19 2013 - 23:08:57 CEST