Re: 11G Statistics Collection
Date: Sun, 15 Apr 2012 17:58:26 -0700 (PDT)
Message-ID: <e6a4d904-6b19-401f-9def-d644a11d0154_at_x5g2000pbl.googlegroups.com>
On Apr 16, 8:17 am, Mladen Gogala <gogala.mla..._at_gmail.com> wrote:
>
> I thought that dynamic sampling was the right way to deal with the
> temporary tables? If you enable dynamic sampling, you will add yet
> another element of unpredictability to your system. There may be more
> than the STALE_PERCENT of changes and Oracle will decide that it doesn't
> really want to use the stats and histograms you provided, it will sample
> it dynamically, which will make the application performance dynamically
> f**ed.
>
Absolutely. The correct way - which has ALWAYS been so - is for the application(s) to do stats calc when it is appropriate, timely and meaningful.
This notion of Oracle that somehow automatic db stats calc will know what to do and when to do it, is beyond stupid! Add-on the silly bindvariable peeking and plonk dynamic sampling on top and you got the recipe for a performance yoyo...
It would help as well if Peoplesoft (and other apps including Oracle's) was a little bit more Oracle-aware and did not go and do an ANALYZE: the blessed command has been deprecated now for I don't know how many years and what do the Peoplesoft developers go and do?...
Same goes for Toad: I 've lost count of the sudden "problems" we've had in our DW and when I go and investigate, invariably someone has done a Toad analyze instead of dbms_stats.gather_table_stats!
The best way would be for Oracle to provide a means to "alias" ANALYZE to a dba-specified flavour of dbms_stats.gather_table_stats. But given the status of Oracle db nowadays and how its features are decided upon, I don't expect that to ever happen.
As John pointed out, there is always special cases that need attention from the dba. But just doing the right thing from the application perspective to start with wouldn't hurt!
Yeah, what we really need is more silly "automation" and more silly ideas like bind-variable peeking...
But what am I talking about? The fault is always those "expensive" dbas'! Received on Sun Apr 15 2012 - 19:58:26 CDT