Re: fake stats to a particular partition

From: Riyaj Shamsudeen <rshamsud_at_jcpenney.com>
Date: Thu, 10 Aug 2006 13:08:56 -0500
Message-ID: <44DB7638.8060901@jcpenney.com>

Syed

It's quite easy to do that. You could take one of the partition statistics as representative statistics and copy from the partition to other partitions. Here are the steps:

Create a stat_table and export the partition statistics.. begin DBMS_STATS.create_stat_table ( ownname=>'scott', stattab =>'STATS_on_apr07'); end; / BEGIN DBMS_STATS.export_table_stats ( ownname=>'scott', tabname =>'your_part_table', STATTAB =>'STATS_on_apr07', statid =>'STATS_on_apr07', STATOWN=>'scott' ); end; /
Then insert new rows by copying stats from a partition. For example, in this case, I am populating stats to partitions 251-400 from 250th partition stats. Partition names are of the format PART_01_251 through PART_01_400 etc for us, so, you might have to change this based upon your naming convention.

begin
FOR i in 251 .. 400 loop
INSERT INTO stats_on_apr07 (
STATID, TYPE , VERSION , FLAGS , C1 , C2 , C3, C4 , C5 ,
N1 , N2 , N3, N4 , N5 , N6, N7, N8, N9, n10,N11,N12, D1 , R1 , R2 , CH1 )

select STATID, TYPE     ,VERSION ,  FLAGS    ,
  C1      ,  'PART_'||substr(c2,6,2)||'_'||lpad(i,3,'0')  ,  C3,  C4 ,  C5 ,
  N1       ,  N2       ,  N3       ,  N4      ,N5, N6, N7, N8, N9,

n10,N11,N12,
D1 , R1 , R2 , CH1
FROM STATS_on_apr07 WHERE C2 like 'PART_%_250'; end loop;
end;
/

3. Import statistics.

Only problem here is that this not update the table level statistics, meaning if 1 million rows added to 251th partition, table level statistics does not reflect that. But, that is fine for us due to table size.

4. verify.

PS: Version:10gR1. Might need adjustments for different versions.

Syed Jaffar Hussain wrote:
> Hello List,
>
> Is there any way that we can set fake statistics to a particular
> partiton of a partitioned table?
> As I had asked a question sometime back that insert and other stuff
> are very slow on the first day of a new partition(monthly partitions).
> I would like to set fake statitics to a partition of a partition table.
>
> Thanks for your time.
>
> Jaffar

The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If the reader of this message is not the intended recipient, you are hereby notified that your access is unauthorized, and any review, dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you are not the intended recipient, please contact the sender and delete the material from any computer.

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

Received on Thu Aug 10 2006 - 13:08:56 CDT