how to compare statistics

One of the key reasons an execution plan can differ from one system to another is because the Optimizer statistics on each system are different, for example when data on a test system is not 100% in sync with real production system. To identify differences in statistics, the DBMS_STATS.DIFF_TABLE_STATS_* functions can be used to compare statistics for a table from two different sources.

In the example below, we compare the current dictionary statistics for the EMP table with the statistics for EMP in the statistics table TAB1; the SQL statement will generate a report .

SELECT report, maxdiffpct
FROM table(DBMS_STATS.DIFF_TABLE_STATS_IN_STATTAB(‘SCOTT’,’EMP’,’TAB1’ ));