Oracle Database - Statistics - stattab (Statistics Table)

> Database > Oracle Database > Oracle Database - Optimizer Statistics

1 - About

The stattab is a table that permits to:

  • store statistics outside the data dictionary
  • and therefore make comparison

This table is used within the DBMS_STATS package.

Only statistics stored in the dictionary have an impact on the cost-based optimizer.

3 - How to

3.1 - Create

You can create the stattab table with the CREATE_STAT_TABLE Procedure

-- will create the stattab table in the current schema
BEGIN
   DBMS_STATS.CREATE_STAT_TABLE(NULL,'stattab');
END;
/

Stattab structure. The columns are generic then the structure gives no so much information.

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 TIME ZONE
R1                               RAW(1000)
R2                               RAW(1000)
R3                               RAW(1000)
CH1                              VARCHAR2(1000)
CL1                              CLOB
Advertising

3.2 - Drop

DROP_STAT_TABLE procedure

BEGIN
   DBMS_STATS.DROP_STAT_TABLE(NULL, 'STATTAB')
END;
/
db/oracle/statistics/stattab.txt · Last modified: 2017/09/06 19:27 by gerardnico