Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Krishnaprasad.

Asked: November 09, 2018 - 4:03 pm UTC

Last updated: November 13, 2018 - 11:43 am UTC

Version: 12.1.2.0

Viewed 1000+ times

You Asked

Hi Team ,

as per the requirement from application team , we need to copy table stats from one table to other table .

Both source and destination table are partition tables .

here we tested out in local system below steps :

1. created dummy table 

create table t1(c1, c2)
  partition by range (c1) (
    partition p1 values less than (1000),
    partition p2 values less than (2000),
    partition p3 values less than (3000)
  )
  as
  select level-1, level
  from dual
  connect by level <= 3000
  ;


2. gather stats 
 exec dbms_stats.gather_table_stats(sys, 't1', granularity=>'all');


3.exported stats in table "STATISTICS"
 exec dbms_stats.create_stat_table('SYS','STATISTICS');
 
 exec dbms_stats.export_table_stats('SYS','T1',NULL,'STATISTICS');


4. crete target table 

create table t3(c1, c2)
  partition by range (c1) (
    partition p1 values less than (1000),
    partition p2 values less than (2000),
    partition p3 values less than (3000)
  )
  as
  select level-1, level
  from dual
  connect by level <= 3000  ;


5.updated table name of exported stats 

   update statistics set c1='T2';

   12 rows updated.

SQL> commit;

Commit complete.


6. Imported table in target partition table 

   exec dbms_stats.import_table_stats('SYS','T3', null, 'STATISTICS');

    SQL> select partition_name,last_analyzed from dba_tab_partitions where table_name='T3' and 
    table_owner='SYS';

     PARTITION_NAME                 LAST_ANAL
    ------------------------------ ---------
      P1                             09-NOV-18
      P2                             09-NOV-18
      P3                             09-NOV-18


    SQL> select table_name, num_rows from dba_tables where table_name in ('T1','T3');

     TABLE_NAME                       NUM_ROWS
      ------------------------------ ----------
      T1                                   3000
      T3                                   3000



Please confirm is this step is correct? As per my understanding their should be global stats and partition stats both we need to import, but here i am just taking export of table stats as whole and importing it.

Does this make sense or some other steps needs to be carried out here?

additionally in syntax exec dbms_stats.import_table_stats('SYS','T3', null, 'STATISTICS'); what does 'null' indicate ?

regards,
Krishna

and Chris said...

Eeeeeek! Creating tables in SYS! No no no no no no no no no no no NO!

Always create your own user and make new tables there.

Anyway, the third parameter of export/import table stats is the partition name. Use this to manage stats for that partition only:

create table t1(c1, c2)
partition by range (c1) (
partition p1 values less than (1000),
partition p2 values less than (2000),
partition p3 values less than (3000)
)
as
select level-1, level
from dual
connect by level <= 3000;

create table t3(c1, c2)
partition by range (c1) (
partition p1 values less than (1000),
partition p2 values less than (2000),
partition p3 values less than (3000)
)
as
select level-1, level
from dual
connect by level <= 3000;

exec dbms_stats.gather_table_stats(user, 't1', granularity=>'all');
exec dbms_stats.create_stat_table(user,'STATISTICS');
exec dbms_stats.export_table_stats(user,'T1',NULL,'STATISTICS');

update statistics set c1='T3';

commit;

exec dbms_stats.import_table_stats(user,'T3', 'P1', 'STATISTICS');

select table_name, partition_name, num_rows, last_analyzed 
from   user_tab_statistics
where  table_name like 'T_'
order  by 1, 2;

TABLE_NAME   PARTITION_NAME   NUM_ROWS   LAST_ANALYZED          
T1           P1                     1000 12-NOV-2018 02:44:28   
T1           P2                     1000 12-NOV-2018 02:44:28   
T1           P3                     1000 12-NOV-2018 02:44:28   
T1           <null>                 3000 12-NOV-2018 02:44:28   
T3           P1                     1000 12-NOV-2018 02:44:28   
T3           P2                   <null> <null>                 
T3           P3                   <null> <null>                 
T3           <null>               <null> <null> 

exec dbms_stats.import_table_stats(user,'T3', null, 'STATISTICS');

select table_name, partition_name, num_rows, last_analyzed
from   user_tab_statistics
where  table_name like 'T_'
order  by 1, 2;

TABLE_NAME   PARTITION_NAME   NUM_ROWS   LAST_ANALYZED          
T1           P1                     1000 12-NOV-2018 02:44:28   
T1           P2                     1000 12-NOV-2018 02:44:28   
T1           P3                     1000 12-NOV-2018 02:44:28   
T1           <null>                 3000 12-NOV-2018 02:44:28   
T3           P1                     1000 12-NOV-2018 02:44:28   
T3           P2                     1000 12-NOV-2018 02:44:28   
T3           P3                     1000 12-NOV-2018 02:44:28   
T3           <null>                 3000 12-NOV-2018 02:44:28


As to whether you should import all the source table stats or just for one partition: it depends.

Why are you doing this vs. using normal stats gathering on the target table?

Do all the partitions store the same values?

If so a wholesale copy is probably right. But if you want to refresh only one partition, the answer's probably no. It comes down to what your goal is here.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Krishnaprasad Yadav, November 12, 2018 - 5:42 pm UTC

thank you for your response.

we have table and we are planning to purge it , by copying xxx rows from it .

so , copying stats was taken into consideration rather than collecting it . benefit is to save time .


i am not able to understand this " If so a wholesale copy is probably right. But if you want to refresh only one partition, the answer's probably no. It comes down to what your goal is here. "

is refreshing partition stats is can't be done ? or need of do it ?
Chris Saxon
November 13, 2018 - 11:43 am UTC

You can refresh (or collect) stats at the partition level.

we have table and we are planning to purge it, by copying xxx rows from it .

If you're copying a subset of the table's rows, you only want stats about what you've transferred. NOT the whole original table.

Though your table is partitioned: can't you drop partitions to purge it?

Also, from 12c if you do direct path inserts then the database gathers stats for you anyway:

create table t ( x, y )as 
  select level, level from dual
  connect by level <= 1000;
  
create table t_copy (
  c1 int, c2 int
);

insert /*+ append */into t_copy
  select * from t
  where  x <= 500;
commit;

select num_rows from user_tables
where  table_name = 'T_COPY';

NUM_ROWS   
       500 

select * from user_tab_statistics
where  table_name = 'T_COPY';

NUM_ROWS   BLOCKS   
       500        4


So there's no need for you to gather/copy stats on the new table.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.