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');
I hope you like this post on How to compare statistics using stattab for a table in Oracle, How to compare statistics between different databases
Related Articles
How to import statistics in Oracle
How to export statistics in Oracle
How to transfer statistics between databases in Oracle
How to create stat table in Oracle
How to set statistics for table in oracle