10.2.0.4.12中的DBMS_STATS.COPY_TABLE_STATS 存过中的scale_factor参数

aimos

仿照：metalink note：
How To Copy Statistics From One Partition To Another using DBMS_STATS.COPY_TABLE_STATS (文档 ID 839111.1)
做了个测试，测试数据库版本是10.2.0.4.12，但是发现其中的scale_factor参数不起作用，无论设置成1.5还是2，都只是按原比例进行拷贝，并不会放大。
测试过程如下：

SQL> CREATE TABLE sales_range
  2  (salesman_id NUMBER(5),
  3  salesman_name VARCHAR2(30),
  4  sales_amount NUMBER(10),
  5  sales_date DATE)
  6  PARTITION BY RANGE(sales_date)
  7  (
  8  PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')),
  9  PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')),
10  PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')),
11  PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY'))
12  );

Table created

SQL>
SQL>
SQL> create index salesman_id_ix on sales_range (salesman_id) local;

Index created

SQL>
SQL>
SQL>
SQL> insert into sales_range values(10,'SCOTT',1000,'01-JAN-2000');

1 row inserted
SQL> insert into sales_range values(20,'SMITH',1200,'01-JAN-2000');

1 row inserted
SQL> insert into sales_range values(30,'ALLEN',1300,'01-JAN-2000');

1 row inserted
SQL> commit;

Commit complete

SQL>
SQL>
SQL>
SQL>
SQL> exec dbms_stats.gather_table_stats(ownname=>'', tabname=> 'sales_range', granularity=>'ALL');

PL/SQL procedure successfully completed

SQL>
SQL>
SQL> select count(*) from sales_range partition (SALES_JAN2000);

  COUNT(*)
----------
         3

SQL>
SQL>
SQL> select count(*) from sales_range partition (SALES_FEB2000);

  COUNT(*)
----------
         0

SQL>
SQL>
SQL> select num_rows,AVG_ROW_LEN,BLOCKS,PARTITION_NAME from user_tab_partitions where table_name='SALES_RANGE';

  NUM_ROWS AVG_ROW_LEN     BLOCKS PARTITION_NAME
---------- ----------- ---------- ------------------------------
         3          20          1 SALES_JAN2000
         0           0          0 SALES_FEB2000
         0           0          0 SALES_MAR2000
         0           0          0 SALES_APR2000

SQL>
SQL>
SQL> select partition_name, num_rows,leaf_blocks,avg_leaf_blocks_per_key from  user_ind_partitions where index_name='SALESMAN_ID_IX';

PARTITION_NAME                   NUM_ROWS LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY
------------------------------ ---------- ----------- -----------------------
SALES_JAN2000                           3           1                       1
SALES_FEB2000                           0           0                       0
SALES_MAR2000                           0           0                       0
SALES_APR2000                           0           0                       0

SQL>
SQL>
SQL> EXEC DBMS_STATS.COPY_TABLE_STATS ('','SALES_RANGE','SALES_JAN2000','SALES_FEB2000',2);

PL/SQL procedure successfully completed

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> select num_rows,AVG_ROW_LEN,BLOCKS,PARTITION_NAME from user_tab_partitions where table_name='SALES_RANGE';

  NUM_ROWS AVG_ROW_LEN     BLOCKS PARTITION_NAME
---------- ----------- ---------- ------------------------------
         3          20          1 SALES_JAN2000
         3          20          1 SALES_FEB2000
         0           0          0 SALES_MAR2000
         0           0          0 SALES_APR2000

SQL>
SQL>
SQL>  select partition_name, num_rows,leaf_blocks,avg_leaf_blocks_per_key from  user_ind_partitions where index_name='SALESMAN_ID_IX';

PARTITION_NAME                   NUM_ROWS LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY
------------------------------ ---------- ----------- -----------------------
SALES_JAN2000                           3           1                       1
SALES_FEB2000                           3           1                       1  ---可以看到，并没有缩放。
SALES_MAR2000                           0           0                       0
SALES_APR2000                           0           0                       0

大家有没有试过？

aimos

刚才又找了个10.2.0.5.6的环境，在这个环境下起作用:
SQL> REM CREATE TABLE
SQL> REM ============
SQL> CREATE TABLE sales_range
  2  (salesman_id NUMBER(5),
  3  salesman_name VARCHAR2(30),
  4  sales_amount NUMBER(10),
  5  sales_date DATE)
  6  PARTITION BY RANGE(sales_date)
  7  (
  8  PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')),
  9  PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')),
10  PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')),
11  PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY'))
12  );

REM CREATE LOCAL INDEX
REM ==================
create index salesman_id_ix on sales_range (salesman_id) local;

REM POPULATE TABLE

Table created.

SQL> SQL> SQL> SQL> REM ===============
insert into sales_range values(10,'SCOTT',1000,'01-JAN-2000');
insert into sales_range values(20,'SMITH',1200,'01-JAN-2000');
insert into sales_range values(30,'ALLEN',1300,'01-JAN-2000');

Index created.

SQL> SQL> SQL> SQL>
1 row created.

SQL>
1 row created.

SQL> commit;

1 row created.

SQL>
Commit complete.

SQL>
SQL> REM GATHER STATISTICS
SQL> REM ==================
SQL> exec dbms_stats.gather_table_stats(ownname=>'', tabname=> 'sales_range', granularity=>'ALL');



PL/SQL procedure successfully completed.

SQL> SQL> SQL>
SQL>
SQL> select count(*) from sales_range partition (SALES_JAN2000);

  COUNT(*)
----------
         3

SQL> select count(*) from sales_range partition (SALES_FEB2000);

  COUNT(*)
----------
         0

SQL>
SQL>
SQL> select num_rows,AVG_ROW_LEN,BLOCKS,PARTITION_NAME from user_tab_partitions where table_name='SALES_RANGE';

  NUM_ROWS AVG_ROW_LEN     BLOCKS PARTITION_NAME
---------- ----------- ---------- ------------------------------
         3          20          1 SALES_JAN2000
         0           0          0 SALES_FEB2000
         0           0          0 SALES_MAR2000
         0           0          0 SALES_APR2000

SQL>
SQL>
SQL>  select partition_name, num_rows,leaf_blocks,avg_leaf_blocks_per_key from  user_ind_partitions where index_name='SALESMAN_ID_IX';

PARTITION_NAME                   NUM_ROWS LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY
------------------------------ ---------- ----------- -----------------------
SALES_JAN2000                           3           1                       1
SALES_FEB2000                           0           0                       0
SALES_MAR2000                           0           0                       0
SALES_APR2000                           0           0                       0

SQL> EXEC DBMS_STATS.COPY_TABLE_STATS ('','SALES_RANGE','SALES_JAN2000','SALES_FEB2000',2);

PL/SQL procedure successfully completed.

SQL>  select num_rows,AVG_ROW_LEN,BLOCKS,PARTITION_NAME from user_tab_partitions where table_name='SALES_RANGE';

  NUM_ROWS AVG_ROW_LEN     BLOCKS PARTITION_NAME
---------- ----------- ---------- ------------------------------
         3          20          1 SALES_JAN2000
         6          20          2 SALES_FEB2000
         0           0          0 SALES_MAR2000
         0           0          0 SALES_APR2000

SQL>
SQL>
SQL> select partition_name, num_rows,leaf_blocks,avg_leaf_blocks_per_key from  user_ind_partitions where index_name='SALESMAN_ID_IX';

PARTITION_NAME                   NUM_ROWS LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY
------------------------------ ---------- ----------- -----------------------
SALES_JAN2000                           3           1                       1
SALES_FEB2000                           6           2                       2
SALES_MAR2000                           0           0                       0
SALES_APR2000                           0           0                       0

Maclean Liu(刘相兵

scale_factor
Scale factor to scale nblks, nrows etc. in dstpartname

ODM FINDING:


This note explains how to copy statistics from one partition to a different partition in the same table.

FIX

DBMS_STATS.COPY_TABLE_STATS

Statistics can be copied from one partition of a table to another partition using DBMS_STATS.COPY_TABLE_STATS.
Statistics of all dependent object such as columns and local indexes will also be copied.Statistics can optionally be scaled based on the given scale_factor.


The definition of the procedure is as follows:


DBMS_STATS.COPY_TABLE_STATS (
   ownname          VARCHAR2,
   tabname          VARCHAR2,
   srcpartname      VARCHAR2,
   dstpartname      VARCHAR2,
   scale_factor     VARCHAR2 DEFAULT 1,
   flags            NUMBER DEFAULT,
   force            BOOLEAN DEFAULT FALSE);
Where:

Ownname:       Name of the schema
Tabname:        Table name of source and destination [sub] partition
Srcpartname: Source [sub] partition
Dtspartname: Destination [sub] partition
Scale factor:   To scale nblks, nrows etc. in destination partition
Flags:              For internal Oracle use (should be left as NULL)
Force:              When TRUE, copy statistics will be copied even when statisitcs are locked by DBMS_STATS.LOCK_*_STATISTICS
Note: If there are no statistics present for the source partition then nothing is copied.

Note that in 10g, this procedure is FOR INTERNAL USE ONLY.  Use of the procedure in 10g may work but is not supported due to this.
In 11g the procedure has been modified is now externalised.


Example

Setup

REM CREATE TABLE
REM ============
CREATE TABLE sales_range
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY RANGE(sales_date)
(
PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')),
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')),
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')),
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY'))
);

REM CREATE LOCAL INDEX
REM ==================
create index salesman_id_ix on sales_range (salesman_id) local;

REM POPULATE TABLE
REM ===============
insert into sales_range values(10,'SCOTT',1000,'01-JAN-2000');
insert into sales_range values(20,'SMITH',1200,'01-JAN-2000');
insert into sales_range values(30,'ALLEN',1300,'01-JAN-2000');
commit;

REM GATHER STATISTICS
REM ==================
exec dbms_stats.gather_table_stats(ownname=>'', tabname=> 'sales_range', granularity=>'ALL');
Table SALES_RANGE has been created with 3 partitions containing a local index.
Data has been inserted into partition SALES_JAN2000 and statistics gathered on all partitions
SQL> select count(*) from sales_range partition (SALES_JAN2000);

  COUNT(*)
----------
         3

SQL> select count(*) from sales_range partition (SALES_FEB2000);

  COUNT(*)
----------
         0

SQL>  select num_rows,AVG_ROW_LEN,BLOCKS,PARTITION_NAME from user_tab_partitions where table_name='SALES_RANGE';

  NUM_ROWS AVG_ROW_LEN     BLOCKS PARTITION_NAME
---------- ----------- ---------- ------------------------------
         3          20         46 SALES_JAN2000
         0           0          0 SALES_FEB2000
         0           0          0 SALES_MAR2000
         0           0          0 SALES_APR2000


SQL> select partition_name, num_rows,leaf_blocks,avg_leaf_blocks_per_key from  user_ind_partitions where index_name='SALESMAN_ID_IX';

PARTITION_NAME                   NUM_ROWS LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY
------------------------------ ---------- ----------- -----------------------
SALES_JAN2000                           3           1                       1
SALES_FEB2000                           0           0                       0
SALES_MAR2000                           0           0                       0
SALES_APR2000                           0           0                       0
SALES_JAN2000 shows a count of  3 rows whereas SALES_FEB2000 shows 0 rows
USER_TAB_PARTITIONS and USER_TAB_INDEXES show non-zero information for partition SALES_JAN2000 that has been populated with data
The other partitions that do not contain data show all statisitc information as 0.
Copying Statistics

SQL> EXEC DBMS_STATS.COPY_TABLE_STATS ('','SALES_RANGE','SALES_JAN2000','SALES_FEB2000',2);

PL/SQL procedure successfully completed.

Statistics are copied from SALES_JAN2000 partition to SALES_FEB2000 partition using a scale-factor of 2.


SQL> select num_rows,AVG_ROW_LEN,BLOCKS,PARTITION_NAME from user_tab_partitions where table_name='SALES_RANGE';

  NUM_ROWS AVG_ROW_LEN     BLOCKS PARTITION_NAME
---------- ----------- ---------- ------------------------------
         3          20         46 SALES_JAN2000
         6          20         92 SALES_FEB2000
         0           0          0 SALES_MAR2000
         0           0          0 SALES_APR2000

SQL> select partition_name, num_rows,leaf_blocks,avg_leaf_blocks_per_key from  user_ind_partitions where index_name='SALESMAN_ID_IX';

PARTITION_NAME                   NUM_ROWS LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY
------------------------------ ---------- ----------- -----------------------
SALES_JAN2000                           3           1                       1
SALES_FEB2000                           6           2                       2
SALES_MAR2000                           0           0                       0
SALES_APR2000                           0           0                       0
USER_TAB_PARTITIONS and USER_TAB_INDEXES  now show non-zero information for partition SALES_FEB2000.
As we have used a scale-factor of 2, the copied statistics have been mutliplied by a factor of 2.

Maclean Liu(刘相兵

怀疑是 10.2.0.4的BUG，但是由于该特性使用的人员过少所以没有找到相关的BUG NOTE

aimos

这个note我看了，只是发现在10.2.0.4.12这个版本里，这个参数不生效。

aimos

ok，多谢ML。