Re: gather stale and Histograms

JusungYang_at_yahoo.com (Jusung Yang) wrote in message news:<130ba93a.0305071155.7dec6df_at_posting.google.com>...
> dday1960_at_hotmail.com (Dana Day) wrote in message news:<7cf47471.0305070519.3ad3fd7e_at_posting.google.com>...
> > Hello,
> >
> > I manage several databases and have recently converted to using
> > dbms_stats.gather_schema_stats with the gather stale option.
> > A significant number of tables/columns also have histograms which are
> > reset after dbms_stats is run.
> >
> > What I want to do is have a routine that will dynamically gather stale
> > and immediately after, regenerate histograms if needed.
> > A couple of thoughts I have had would be to generate a temp table for
> > this purpose and use %_histograms to re-gather histograms after
> > running dbms_stats.
> >
> > Is anyone else doing similar, and would you share the wisdom?
> >
> > TIA,
> >
> > Dana Day
> > Phoenix, AZ
> > Environment: AIX 4.3/8.1.7.2, W2K/9.2.0, AIX 4.3/9.2.0.2.
>
>
> Have not gotten around to test this... But you can recreate histograms
> with "method_opt" while you GATHER STALE for the objects with stale
> statistics at the same time, no?
>
> - Jusung Yang

In 9i this operation is simple. Unfortunately, 95% of production databases I manage remain on 8.1.7.2 and AIX 4.3. Jusung-
You are correct about method_opt, which in 9i includes SKEWONLY option, the REPEAT option, both of which may do the job. Connor-
Oracle Docs list objlist as object type, so the CAST would work. Other alternatives suggest themselves as well. DBMS_STATS.%_STATS_TABLE operations seem to be suited to this, but I will try and let you know.
Geomancer-
To your point, we used to do nightly estimate using analyze. Did not work as well since the distribution of rows/tables/changes was quite different.
Most databases I manage I would consider hybrid; the applications use OLTP, but many batch processes run in background. Row counts range from a few hundred to a few million, with the smaller tables often changed very much while the larger tables change 10% only after many weeks. So gather_stale has worked very well for this environment.

To all-
Thank you very much for the ideas. I will do more research and return with conclusions/results.

Dana Day
Phoenix, AZ
Environment: AIX 4.3/8.1.7.2, W2K/9.2.0, AIX 4.3/9.2.0.2. Received on Thu May 08 2003 - 11:04:26 CDT