Re: gather stale and Histograms

From: Dana Day <dday1960_at_hotmail.com>
Date: 7 May 2003 10:24:01 -0700
Message-ID: <7cf47471.0305070924.3be41851@posting.google.com>

Connor McDonald <connor_mcdonald_at_yahoo.com> wrote in message news:<3EB91488.2D50_at_yahoo.com>...
> Dana Day wrote:
> >
> > 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.
>
> Doesn't gather_stale have an OUT parameter which lists the objects
> processed? You could use this to subsequently loop through to do any
> additional processing you require.
>
> hth
> connor

Connor,
There is an OUT parameter. So something along the lines of: select owner,

       table_name, --table listing from OUT PARAMETER?
       column_name, 
       count(*) 

from dba_histograms
where owner = l_owner_being_analyzed
and table_name IN(OUT LIST)
GROUP
    BY OWNER,

       TABLE_NAME, 
       COLUMN_NAME

HAVING COUNT(*)>2 --where I know it has been histogram'd /

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 Wed May 07 2003 - 12:24:01 CDT