Re: gather stale and Histograms

Dana Day wrote:
>
> 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.

I haven't checked (ie I'm currently forced to do newgroups on a PC that does not have Oracle installed) whether the OUT param is an object type or a package type.

If its the former you can do:

select *
from TABLE ( cast( out_variable as dbms_stats_table_type) )

hth
connor

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"