How to Export and Import Statistics.

How to Export and Import Statistics. 


A critical application suddenly seems to hang, wait events show long table scans running on the OLTP environment.
It comes out that the DBA in charge of this system did run statistics on the tables of the user that owns the application.
The gather statistics got stuck and the process was killed. Since this moment the application started to perform extremely slowly.


The production database has several clones; we decide to export back statistics from one of these clones, to the production database.


This document will descripe the steps to perform the export and import statistics. 


1. Create the stat table 


SQL> exec DBMS_STATS.CREATE_STAT_TABLE('SCOTT','OLD_STATS');


PL/SQL procedure successfully completed.


SQL> GRANT SELECT,INSERT,UPDATE,DELETE ON SCOTT.OLD_STATS TO PUBLIC;


Grant succeeded.


SQL>


SQL> select 'exec dbms_stats.export_table_stats('||chr(39)||owner||chr(39)||','||chr(39)||table_name||chr(39)||',nu
  2  ||chr(39)||',null,true,'||chr(39)||'SCOTT'||chr(39)||')'
  3  from dba_tables where owner ='&usrname'
  4
SQL> /
Enter value for usrname: SCOTT
old   3: from dba_tables where owner ='&usrname'
new   3: from dba_tables where owner ='SCOTT'
exec dbms_stats.export_table_stats('SCOTT','DEPT',null,'DEPT',null,true,'INV')
exec dbms_stats.export_table_stats('SCOTT','EMP',null,'EMP',null,true,'INV')
exec dbms_stats.export_table_stats('SCOTT','BONUS',null,'BONUS',null,true,'INV')
exec dbms_stats.export_table_stats('SCOTT','SALGRADE',null,'SALGRADE',null,true,'INV')
exec dbms_stats.export_table_stats('SCOTT','OLD_STATS',null,'OLD_STATS',null,true,'INV')


SQL>
SQL> select num_rows,last_analyzed from dba_tables where owner='SCOTT';


  NUM_ROWS LAST_ANAL
---------- ---------
         4 25-DEC-10
        14 25-DEC-10
         0 25-DEC-10
         5 25-DEC-10




SQL> exec DBMS_STATS.EXPORT_SCHEMA_STATS('&username','&stattablename');
Enter value for username: SCOTT
Enter value for stattablename: OLD_STATS


PL/SQL procedure successfully completed.


SQL> COMMIT;


Commit complete.


SQL>


SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.


E:\>exp system/sys file=soctt_stat.dmp tables=scott.old_stats


Export: Release 10.2.0.1.0 - Production on Sat Dec 25 14:58:20 2010


Copyright (c) 1982, 2005, Oracle.  All rights reserved.




Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Product
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set


About to export specified tables via Conventional Path ...
Current user changed to SCOTT
. . exporting table                      OLD_STATS         24 rows exported
Export terminated successfully without warnings.


E:\>




I'm deleting statistics by using the below method.


SQL> exec DBMS_STATS.delete_SCHEMA_STATS('&username');
Enter value for username: SCOTT


PL/SQL procedure successfully completed.


SQL> commit;


Commit complete.


SQL>






SQL> seLEct stale_stats ,num_rows from dba_tab_statistics where owner='SCOTT' and stale_stats is not null;


no rows selected




Here, Im not importing existing statistics by using export dump. Im trying to generate the new stats


SQL> exec dbms_stats.gather_schema_stats  -
>     (ownname=> 'SCOTT', -
>       cascade=>TRUE,   -
>       degree => 4, -
>     estimate_percent=>dbms_stats.auto_sample_size,-
>       options =>'GATHER');


PL/SQL procedure successfully completed.


SQL> commit;


Commit complete.


SQL> seLEct stale_stats ,num_rows from dba_tab_statistics where owner='SCOTT' and stale_stats is not null;
NO           4
NO          14
NO           0
NO           5
NO          24


SQL>


Thanks