Create ==> Gather ==> Export ==> Delete ==> Import Schema Statistics

==================================================================================
create a table to hold the statistics
----------------------------------------

SQL> exec dbms_stats.create_stat_table(ownname => 'SYS', stattab => 'stats');

PL/SQL procedure successfully completed.

SQL>
==================================================================================
SQL> DESC SYS.stats
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 STATID                                             VARCHAR2(128)
 TYPE                                               CHAR(1)
 VERSION                                            NUMBER
 FLAGS                                              NUMBER
 C1                                                 VARCHAR2(128)
 C2                                                 VARCHAR2(128)
 C3                                                 VARCHAR2(128)
 C4                                                 VARCHAR2(128)
 C5                                                 VARCHAR2(128)
 C6                                                 VARCHAR2(128)
 N1                                                 NUMBER
 N2                                                 NUMBER
 N3                                                 NUMBER
 N4                                                 NUMBER
 N5                                                 NUMBER
 N6                                                 NUMBER
 N7                                                 NUMBER
 N8                                                 NUMBER
 N9                                                 NUMBER
 N10                                                NUMBER
 N11                                                NUMBER
 N12                                                NUMBER
 N13                                                NUMBER
 D1                                                 DATE
 T1                                                 TIMESTAMP(6) WITH TIME ZONE
 R1                                                 RAW(1000)
 R2                                                 RAW(1000)
 R3                                                 RAW(1000)
 CH1                                                VARCHAR2(1000)
 CL1                                                CLOB

SQL>
==================================================================================
gather schema statistics from a sample schema
-----------------------------------------------
SQL> execute dbms_stats.gather_schema_stats(ownname=>'SPM',estimate_percent=>10,degree=>2,cascade=>true);

PL/SQL procedure successfully completed.

==================================================================================
export the statistics into the stats table owned by "SYS" user
----------------------------------------------------------------

SQL> exec DBMS_STATS.export_schema_stats(ownname=>'SPM',stattab=>'stats',statown=>'SYS');

PL/SQL procedure successfully completed.

==================================================================================

After having lot of SQL executions & PLAN changes & when latest stats gathered, we may face performance issues,

So we may need to delete the existing stats & import the backed up stats.

==================================================================================
Delete the existing stats:
===========================

SQL> exec DBMS_STATS.delete_schema_stats (ownname=>'SPM');

PL/SQL procedure successfully completed.

==================================================================================
SQL> SELECT TABLE_NAME,NUM_ROWS,LAST_ANALYZED,BLOCKS FROM DBA_TABLES WHERE OWNER='SPM';

TABLE_NAME             NUM_ROWS LAST_ANALYZED          BLOCKS
-------------------- ---------- ------------------ ----------
EMP3
==================================================================================

Now apply the statistics from the stored table to the new schema
---------------------------------------------------------------------
SQL> exec DBMS_STATS.import_schema_stats(ownname=>'SPM',stattab=>'stats',statown=>'SYS');

PL/SQL procedure successfully completed.

==================================================================================
SQL> SELECT TABLE_NAME,NUM_ROWS,LAST_ANALYZED,BLOCKS FROM DBA_TABLES WHERE OWNER='SPM';

TABLE_NAME             NUM_ROWS LAST_ANALYZED          BLOCKS
-------------------- ---------- ------------------ ----------
EMP3                       9837 28-MAR-17                1756

SQL>

==================================================================================