EXPORT not GATHER with DBMS_STATS

connor_mc_d's picture

Just a short post today on something that came in as a question for the upcoming Office Hours session which I thought could be covered quickly in a blog post without needing a lot of additional discussion for which Office Hours is more suited to.

The question was:

“When I gather statistics using DBMS_STATS, can I just create a statistic table and pass that as a parameter to get the results of the gather”

And the answer simply is “No” Smile but let me clear up the confusion.

Many DBMS_STATS routines allow you to pass the name of the “statistics table” into which statistical information for the table, schema, database etc will be stored. For example, you can see the STAT-prefixed parameter to GATHER_TABLE_STATS


PROCEDURE GATHER_TABLE_STATS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNNAME                        VARCHAR2                IN
 TABNAME                        VARCHAR2                IN
 PARTNAME                       VARCHAR2                IN     DEFAULT
 ESTIMATE_PERCENT               NUMBER                  IN     DEFAULT
 BLOCK_SAMPLE                   BOOLEAN                 IN     DEFAULT
 METHOD_OPT                     VARCHAR2                IN     DEFAULT
 DEGREE                         NUMBER                  IN     DEFAULT
 GRANULARITY                    VARCHAR2                IN     DEFAULT
 CASCADE                        BOOLEAN                 IN     DEFAULT
 STATTAB                        VARCHAR2                IN     DEFAULT
 STATID                         VARCHAR2                IN     DEFAULT
 STATOWN                        VARCHAR2                IN     DEFAULT
 NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT
 STATTYPE                       VARCHAR2                IN     DEFAULT
 FORCE                          BOOLEAN                 IN     DEFAULT
 CONTEXT                        CCONTEXT                IN     DEFAULT
 OPTIONS                        VARCHAR2                IN     DEFAULT

The statistics table must be of a certain structure, which is managed via the API as well using the CREATE_STAT_TABLE routine.


SQL> exec dbms_stats.create_stat_table('','st')

PL/SQL procedure successfully completed.

SQL> desc ST
 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 TI
                                        ME ZONE
 R1                                     RAW(1000)
 R2                                     RAW(1000)
 R3                                     RAW(1000)
 CH1                                    VARCHAR2(1000)
 CL1                                    CLOB
 BL1                                    BLOB

But when calling the GATHER_xxx routines, if you pass the name of the statistic table, please note that this is for getting a copy of the relevant statistics before the fresh gathering of statistics is done. We can see this with a simple demo.


SQL> create table t1 as select * from dba_objects;

Table created.

SQL>
SQL> select num_rows
  2  from   user_tables
  3  where  table_name = 'T1';

  NUM_ROWS
----------
     83608

1 row selected.

So we can see that the table T1 has ~83,000 rows in it. Since I’m running this on 12c, there was no need to gather statistics after this initial load, because they were collected automatically as part of the loading process. (Very nifty!).

I now add another ~250,000 rows and perform a new GATHER_TABLE_STATS call, this time passing in the name of the statistics table (ST) that I just created.


SQL>
SQL> insert into t1
  2  select * from t1;

83608 rows created.

SQL>
SQL> insert into t1
  2  select * from t1;

167216 rows created.

SQL>
SQL> begin
  2  dbms_stats.gather_table_stats
  3   ( ownname=>'MCDONAC',
  4     tabname=>'T1',
  5     stattab=>'ST',
  6     statid=>'STATS'
  7  );
  8  end;
  9  /

PL/SQL procedure successfully completed.

Digging into the statistic table data, you can see that the number of rows recorded for T1 is 83597, which is the before image of the optimizer stat, not the after image.


SQL> select * from st where c1 = 'T1' and type = 'T'
  2  @pr
==============================
STATID                        : STATS
TYPE                          : T
VERSION                       : 8
FLAGS                         : 2
C1                            : T1
C2                            :
C3                            :
C4                            :
C5                            : MCDONAC
C6                            :
N1                            : 83597
N2                            : 1607
N3                            : 129
N4                            : 83597
N5                            :
N6                            :
N7                            :
N8                            :
N9                            : 0
N10                           :
N11                           :
N12                           :
N13                           :
D1                            : 16-JAN-19
T1                            :
R1                            :
R2                            :
R3                            :
CH1                           :
CL1                           :
BL1                           :

PL/SQL procedure successfully completed.

So just remember that if you want to get the DBMS_STATS information that is the result of a GATHER_xxx call, then you can follow it up with the appropriate EXPORT_xxx call to dump the data.

To prevent automated spam submissions leave this field empty.