Performance – Transfering Stats 11i
Source: Oracle Documentations
It is possible to transfer statistics between servers allowing consistent execution plans between servers with varying amounts of data. First the statistics must be collected into a statistics table. In the following examples the statistics for the APPSCHEMA user are collected into a new table, STATS_TABLE, which is owned by DBASCHEMA:
SQL> EXEC DBMS_STATS.create_stat_table(‘DBASCHEMA’,’STATS_TABLE’);
SQL> EXEC DBMS_STATS.export_schema_stats(‘APPSCHEMA’,’STATS_TABLE’,NULL,’DBASCHEMA’);
This table can then be transfered to another server using your preferred method (Export/Import, SQLPlus Copy etc.) and the stats imported into the data dictionary as follows:
SQL> EXEC DBMS_STATS.import_schema_stats(‘APPSCHEMA’,’STATS_TABLE’,NULL,’DBASCHEMA’);
SQL> EXEC DBMS_STATS.drop_stat_table(‘DBASCHEMA’,’STATS_TABLE’);
Issues
Exclude dataload tables from your regular stats gathering, unless you know they will be full at the time that stats are gathered.
It is found gathering stats for the SYS schema can make the system run slower, not faster.
Gathering statistics can be very resource intensive for the server so avoid peak workload times or gather stale stats only. яндекс
Even if scheduled, it may be necessary to gather fresh statistics after database maintenance or large data loads