Wednesday, July 31, 2013

STATS Export/Import


STATS Export/Import



In some cases it is really helpful to import schema statistics from one database to another.
This can help in diagnosing query plan differences as these statistics are the primary information used by CBO.

User Schema : SOE

SELECT owner,
  table_name,
  TO_CHAR(last_analyzed,'dd-mon-yyyy hh24:mi') last_analyzed,
  column_name,
  data_type,
  num_distinct,
  density,
  num_nulls,
  sample_size,
  avg_col_len
FROM dba_tab_columns
WHERE owner='SOE'


 exec dbms_stats.drop_stat_table(ownname => 'SOE', stattab => 'old_stats');

exec dbms_stats.create_stat_table(ownname => 'SOE', stattab => 'old_stats',tblspace => 'USERS');
SQL>  desc OLD_STATS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 STATID                                             VARCHAR2(30 CHAR)
 TYPE                                               CHAR(1 CHAR)
 VERSION                                            NUMBER
 FLAGS                                              NUMBER
 C1                                                 VARCHAR2(30 CHAR)
 C2                                                 VARCHAR2(30 CHAR)
 C3                                                 VARCHAR2(30 CHAR)
 C4                                                 VARCHAR2(30 CHAR)
 C5                                                 VARCHAR2(30 CHAR)
 N1                                                 NUMBER
 N2                                                 NUMBER
 N3                                                 NUMBER
 N4                                                 NUMBER
 N5                                                 NUMBER
 N6                                                 NUMBER
 N7                                                 NUMBER
 N8                                                 NUMBER
 N9                                                 NUMBER
 N10                                                NUMBER
 N11                                                NUMBER
 N12                                                NUMBER
 D1                                                 DATE
 R1                                                 RAW(32)
 R2                                                 RAW(32)
 CH1                                                VARCHAR2(1000 CHAR)
 CL1                                                CLOB

--Export the Table, Index, and Column Statistics
exec dbms_stats.export_schema_stats(ownname => 'SOE', stattab => 'old_stats');
--Export system statistics (sys.aux_stats$)
exec dbms_stats.export_system_stats('old_stats');


***********************************************
Import:

  dbms_stats.import_schema_stats( ownname=>user,stattab=>'old_stats');
   
  dbms_stats.import_system_stats( stattab=>'old_stats');
 


************************************************

For more https://blogs.oracle.com/priminout/entry/exporting_schema_statistics

No comments:

Post a Comment