How to compare statistics using stattab for a table in Oracle

We might have a requirement to compare stats for a table between two different systems, two different statid in the user statistics table, user statistics table and dictionary. All these requirements are met by DBMS_STATS new procedure DIFF_TABLE_STATS_IN_STATTAB. In this post, we will see how to use this procedure to compare statistics

How to compare statistics using stattab for a table

Oracle has provided a function DIFF_TABLE_STATS_IN_STATTAB in DBMS_STATS which can be used to compare statistics for a table from two different sources

The two different sources could be

• Two user statistics tables
• A single-user statistics table containing two sets of statistics that can be identified using statids
• A user statistics table and dictionary

Let’s see the syntax for various sources

Two user statistics tables

set long 99999
set lines 200
set pages 120
select * from dbms_stats.diff_table_stats_in_stattab(ownname=> 'SCOTT', tabname=> 'TEST', stattab1=> 'STATTAB1', stattab2=> 'STATTAB2', pctthreshold=> 0, statid1 => 'Stats1',statid2 => 'Stats2');

A single-user statistics table containing two sets of statistics that can be identified using statids

set long 99999
set lines 200
set pages 120
SQL> select * from dbms_stats.diff_table_stats_in_stattab(ownname=> 'SCOTT', tabname=> 'TEST', stattab1=> 'STATTAB1', stattab2=> 'STATTAB1', pctthreshold=> 0, statid1 => 'Stats1',statid2 => 'Stats2');

A user statistics table and dictionary

set long 99999
set lines 200
set pages 120
select * from dbms_stats.diff_table_stats_in_stattab(ownname=> 'SCOTT', tabname=> 'TEST', stattab1=> 'STATTAB1', stattab2=> NULL, pctthreshold=> 0, statid1 => 'Stats1');

How to compare statistics between different databases

Step 1
On System A: Create the STAT table and export the statistics of the concerned table to that.

SQL> exec dbms_stats.create_stat_table('SCOTT','STATTAB1');
SQL> exec dbms_stats.export_table_stats('SCOTT','TEST',stattab=>'STATTAB1',statid=>'Stats1');

Step 2
Export the STAT table and transfer the file to another database in a different environment

$ exp scott/tiger file=stat.dmp tables=STATTAB1

Step 3
On System B, Import the STAT table using the export dump file from another database for comparison.

$ imp scott/tiger file=stat.dmp full=y

Step 4
Now, run the following commands in System 2 to get the statistics difference report.

set long 99999
set lines 200
set pages 120
select * from dbms_stats.diff_table_stats_in_stattab(ownname=> 'SCOTT', tabname=> 'TEST', stattab1=> 'STATTAB1', stattab2=> NULL, pctthreshold=> 0, statid1 => 'Stats1');