Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Any quick way to remove histograms?

RE: Any quick way to remove histograms?

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Tue, 27 Mar 2007 13:41:36 -0400
Message-ID: <D1DC33E67722D54A93F05F702C99E2A9AD4C08@usahm208.amer.corp.eds.com>

 

One advantage of spooling a file and executing the file is that you can have the execution file also create a spool file with the commands and results in the output. This allows verification that the task did run successfully. Script files also allow copying and pasting of individual commands when only a partial update is desired or some other manipulation of the parameters is desired for a specific statement. In another word, flexibility.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Joel.Patterson_at_crowley.com
Sent: Tuesday, March 27, 2007 11:17 AM
To: breitliw_at_centrexcc.com; Brandon.Allen_at_OneNeck.com Cc: oracle-l_at_freelists.org
Subject: RE: Any quick way to remove histograms?

Why indeed?

:)

Joel Patterson
Database Administrator
joel.patterson_at_crowley.com
x72546
904 727-2546

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Wolfgang Breitling Sent: Tuesday, March 27, 2007 9:20 AM
To: Brandon.Allen_at_OneNeck.com
Cc: oracle-l_at_freelists.org
Subject: RE: Any quick way to remove histograms?

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


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


--
http://www.freelists.org/webpage/oracle-l
Received on Tue Mar 27 2007 - 12:41:36 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US