Exporting Schema Statistics

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
  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'
                                );
end;
/

In this way we can guarantee that queries are using the same statistics for optimization. This is the case even if the underlying data is different. Oracle only uses the stored statistics to perform query optimization (except in the case where there are no statistics; then dynamic sampling is used). This technique is an invaluable way to share optimizer statistics and diagnose query plan problems.

Other Links:

Oracle 11g Statistics Management

Comments:

Thanks .. you are too late to post this. most of DBAs knew this 5/6 year ago

Posted by guest on May 08, 2013 at 02:35 AM PDT #

this was very helpful for me as a fresher DBA thanks

Posted by guest on August 07, 2013 at 05:20 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

An insider view of the technology behind the Primavera product suite.

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today