Oracle provides a PL/SQL package for handling statistics called DBMS_STATS. With this package you’re able to gather, delete, export and import statistics and some more stuff. So today a colleague came to me and told me that he just wanted to import the stats of a table from one schema to another one on a different database but the stats doesn’t get populated. I said him that he should provide the commands he executed and checked them – they were ok. The import routine of the DBMS_STATS package always gets successfully executed. After a short look into the stats table and on Metalink I was surprised that the package seems to be designed for saving and restoring statistics of a schema in case of crash or upgrades but not for importing statistics into another schema. And Metalinks only workaround (look at section “Different schema” in Doc 117203.1) is to modify the stats table before re-importing although the documentation explicit says not to modify anything in the stats table: (:D)
CREATE_STAT_TABLE Procedure
This procedure creates a table with name stattab in ownname‘s schema which is capable of holding statistics. The columns and types that compose this table are not relevant as it should be accessed solely through the procedures in this package.)
So let’s see what the problem actually is:
First of all we need two users where we want to transfer the statistics, so we create user test and test1:
SQL> create user test identified by test;
User created.
SQL> grant connect, resource to test;
Grant succeeded.
SQL> create user test1 identified by test1;
User created.
SQL> grant connect, resource to test1;
Grant succeeded.
Next thing is to create a table, populate it with data and gather table stats:
SQL> conn test/test@TEST1
Connected.
SQL> create table datatable (line number);
Table created.
SQL> insert into datatable select rownum from all_objects;
40779 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats (user, ‘DATATABLE’);
PL/SQL procedure successfully completed.
SQL> select num_rows from user_tables;
NUM_ROWS
———-
40779
As you can see stats showing 40,779 rows in the table.
Next thing now is to export the statistics into a stats table. Therefore we need to create the stats table and export the stats into it:
SQL> exec dbms_stats.create_stat_table(user, ‘STATS’);
PL/SQL procedure successfully completed.
SQL> select * from stats;
no rows selected
SQL> exec dbms_stats.export_table_stats(user, ‘DATATABLE’,null,’STATS’);
PL/SQL procedure successfully completed.
As you can see the table was empty before the export, now it’s already populated:
SQL> set heading off;
SQL> select * from stats;
T 4 2
DATATABLE
TEST 40779 65 4 40779
16-JUL-08
C 4 2
DATATABLE
LINE
TEST 40779 .000024522 40779 40779
0 4 40774 5
16-JUL-08
C105
C305084B
SQL> set heading on;
Looking at the C5 column which contains the owner of the object we can see that these stats belong to owner TEST:
SQL> select c5 from stats;
C5
——————————
TEST
TEST
Now let’s export the stats table and import it in the other schema (using create table as select statement for this, but this would be also the same with exp/imp and datapump:
SQL> conn sys@TEST1 as sysdba
Connected.
SQL> create table test1.stats as select * from test.stats;
Table created.
So now let’s create the same data table and import the statistics:
SQL> conn test1/test1@TEST1
Connected.
SQL> create table datatable (line number);
Table created.
SQL> exec dbms_stats.import_table_stats(user, ‘DATATABLE’, null, ‘STATS’);
PL/SQL procedure successfully completed.
Stats are successfully imported the procedure completed successfully. Looking at the statistics we should see now stat but instead of that we see following:
SQL> select num_rows from user_tables;
NUM_ROWS
———-
SQL> select table_name, num_rows from user_tables;
TABLE_NAME NUM_ROWS
—————————— ———-
STATS
DATATABLE
No stats there, looking at the column C5 we still see the old user of course:
SQL> select c5 from stats;
C5
——————————
TEST
TEST
So the workaround is: Update the C5 column with the correct owner (the new schema) and import it:
SQL> update stats set c5 = user;
2 rows updated.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.import_table_stats(user, ‘DATATABLE’, null, ‘STATS’);
PL/SQL procedure successfully completed.
And there you go:
SQL> select table_name, num_rows from user_tables;
TABLE_NAME NUM_ROWS
—————————— ———-
STATS
DATATABLE 40779
I’m a little bit confused that the package doesn’t support export and re-import into another schema. For me that’s a design issue. But I read on Metalink that an enhancement request has been opened by the Oracle support guy. Let’s see if it will get implemented.
Gerald on IT 