While most of us are familiar with the schema statistics used by the Cost-Based Optimizer (CBO), something not so well know is the ability to export/import these statistics using the DBMS_STATS package. This can be an invaluable aid in diagnosing query plan differences as these statistics are the primary information used by CBO. It is also a way to save and restore statistics in your own Primavera database.
Exporting statistics is a three step process. The result is a single table containing schema statistics (table, index and column) and system statistics (workload and non-workload). The first step creates a physical version of a StatTable. The StatTable is a consolidated table to hold all types of statistics, so the format is very generic. First, create an instance of the table using DBMS_STATS.CREATE_STAT_TABLE:
begin
-- exec DBMS_STATS.CREATE_STAT_TABLE('<table owner>','<enter a
name for the stats ----table>','<tablespace to store the stats table');
dbms_stats.CREATE_STAT_TABLE( ownname=>user
, stattab=>'MY_STATS_TABLE'
);
end;
/
The result is a physical table called MY_STATS_TABLE
SQL> desc MY_STATS_TABLE 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
The next two steps are to export data from the current schema. The column STATID identifies a particular set of statistics within this table. It is possible to do multiple exports into a single StatTable by using a different STATID. In this case I am using "CURRENT_STATS" as the STATID.
--Export the Table, Index, and Column Statistics
begin
dbms_stats.export_schema_stats( ownname=>user
, stattab=>'MY_STATS_TABLE'
, statid=>'CURRENT_STATS'
);
end;
/
--Export system statistics (sys.aux_stats$)
begin
dbms_stats.export_system_stats( stattab=>'MY_STATS_TABLE'
, statid=>'CURRENT_STATS'
);
end;
/
If we look at the contents of MY_STATS_TABLE, we will see rows for each different statistic type (T=Table, I=Index, C=Column, S=System).
select statid, type, count(*) from my_stats_table group by statid, type / STATID T COUNT(*) ------------------------------ - ---------- CURRENT_STATS S 2 CURRENT_STATS C 4216 CURRENT_STATS I 884 CURRENT_STATS T 277
This table can be exported (Export or Datapump) and imported into another database. If the schema is the same, then the statistics can be imported. (Remember to clear the shared pool anytime statistics are updated.)
begin
dbms_stats.import_schema_stats( ownname=>user
, stattab=>'MY_STATS_TABLE'
, statid=>'CURRENT_STATS'
);
end;
/
begin
dbms_stats.import_system_stats( stattab=>'MY_STATS_TABLE'
, statid=>'CURRENT_STATS'
);
--database stats ;
--exec dbms_stats.EXPORT_DATABASE_STATS('<enter the name of the stats table>','<enter an identifier>','<enter the owner of the stats table>');dbms_stats.import_system_stats( stattab=>'MY_STATS_TABLE',null,schema);
end; /
