RE: Any quick way to remove histograms?

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Tue, 27 Mar 2007 07:19:36 -0600
Message-Id: <20070327131825.3C8896813C5@turing.freelists.org>

Why spool, capture and then execute when you can do it all in one:

declare

   l_ndv number;
   l_dummy number;
   l_srec dbms_stats.statrec;

begin

   for r in (select owner, table_name, column_name, null partition_name from DBA_TAB_COL_STATISTICS

         histogram <> 'NONE' [ and possibly other criteria ]
       union all
       select owner, table_name, column_name, partition_name from 
DBA_PART_COL_STATISTICS
         histogram <> 'NONE' [ and possibly other criteria ]
     ) loop
     dbms_stats.get_column_stats (
       ownname => r.owner,
       tabname => r.table_name,
       colname => r.column_name,
       partname => r.partition_name,
       stattab => null,
       statid => null,
       distcnt => l_ndv,
       density => l_dummy,
       nullcnt => l_dummy,
       srec => l_srec,
       avgclen => l_dummy);
     dbms_stats.set_column_stats (
       ownname => r.owner,
       tabname => r.table_name,
       colname => r.column_name,
       partname => r.partition_name,
       distcnt => l_ndv);

   end loop;
end;

and no fighting with quotes :-)

I believe if you have subpartitions you will need to export the statistics to a stattab table, manipulate the statistics there and then re-import as there is no set_column_stats for subpartitions.

At 06:23 PM 3/26/2007, Allen, Brandon wrote:
>At first I was thinking that Alberto's procedure would still be required
>for retrieving the current stats and then resetting just the distcnt
>value. However, after thinking it through for a while, I realized what
>you were probably both thinking the whole time, which is that with this
>simplified method, you can just use a simple SELECT statement on
>all_tab_col_statistics to retrieve the current num_distinct value and
>plug it into the set_column_stats procedure for all columns that
>currently have histograms, thereby creating a script that can be used to
>very quickly remove all existing histograms. It took me a while to get
>all the single quotes just right, but here is the working version for
>anyone else that might find it helpful:
>
>select 'execute dbms_stats.set_column_stats(''' || owner || ''',''' ||
>table_name || ''',''' || column_name || ''', distcnt=>''' ||
>num_distinct || ''');' from all_tab_col_statistics where histogram <>
>'NONE';
>
>Sample Output:
>
>----------------------------------------------------------------------
>execute dbms_stats.set_column_stats('SCOTT','T','C1', distcnt=>'2421');
>execute dbms_stats.set_column_stats('SCOTT','T','C2', distcnt=>'2425');
>execute dbms_stats.set_column_stats('SCOTT','T','C3', distcnt=>'2421');
>
>
>Thanks again to Wolfgang and Alberto!
>
>Regards,
>Brandon
>
>
>
>
>-----Original Message-----
>From: Alberto Dell'Era [mailto:alberto.dellera_at_gmail.com]
>Sent: Friday, March 16, 2007 4:43 PM
>To: breitliw_at_centrexcc.com
>Cc: Allen, Brandon; Oracle-L_at_freelists.org
>Subject: Re: Any quick way to remove histograms?
>
>Much simpler :) - interesting.
>
>
>On 3/16/07, Wolfgang Breitling <breitliw_at_centrexcc.com> wrote:
> >
> > I also did a quick check and just using
> >
> > exec
> > dbms_stats.set_column_stats(user,'table_name',colname=>'column_name',d
> > istcnt=>
> > <num_distinct>);
> >
> > will remove the histogram without removing the low_value and
>high_value.
> >
> >
> >
> > At 01:40 PM 3/16/2007, Alberto Dell'Era wrote:
> > >On 3/16/07, Allen, Brandon <Brandon.Allen_at_oneneck.com> wrote:
> > >>Is there any faster way to remove histograms other than re-analyzing
>
> > >>the table? I want to keep the existing table, index & column stats,
>
> > >>but with only 1 bucket (i.e. no histograms).
> > >
> > >You might try the attached script, that reads the stats using
> > >dbms_stats.get_column_stats and re-sets them, minus the histogram,
> > >using dbms_stats.set_column_stats.
> > >
> > >I haven't fully tested it - it's only 10 minutes old, even if I have
> > >slightly modified for you another script I've used for quite some
> > >time - and the spool on 10.2.0.3 seems to confirmthat the histogram
> > >is, indeed, removed, while all the other statistics are preserved. I
> > >have also reset density to 1/num_distinct, that is the value you get
> > >if no histogram is collected.
>
>Privileged/Confidential Information may be contained in this message
>or attachments hereto. Please advise immediately if you or your
>employer do not consent to Internet email for messages of this kind.
>Opinions, conclusions and other information in this message that do
>not relate to the official business of this company shall be
>understood as neither given nor endorsed by it.
>
>--
>http://www.freelists.org/webpage/oracle-l

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

---

This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email

---

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Mar 27 2007 - 08:19:36 CDT