The statistics export feature is mainly used to export table statistics to a specified user table for storage. The import feature is mainly used to import statistics from a specified user table into the current internal statistics table, eliminating the need to collect statistics again.
For example, during database migration, you can export the statistics of large tables to a specified user table for data migration. After the data migration succeeds, you can directly import the data from the user table that stores the statistics into the corresponding internal statistics table of the new database.
Statistics user table
The statistics user table is mainly used to store the statistics collected in the internal tables. The table structure and related explanations are shown in the following table.
Field |
Data type |
Table-level statistics TYPE='T' |
Column-level statistics TYPE='C' |
|---|---|---|---|
| STATID | VARCHAR2(30) | User-settable | User-settable |
| TYPE | CHAR(1) | Table | Column |
| VERSION | NUMBER | Reserved field | Reserved field |
| FLAGS | NUMBER | Reserved field | Reserved field |
| C1 | VARCHAR2(30) | Table name | Table name |
| C2 | VARCHAR2(30) | Partition name | Partition name |
| C3 | VARCHAR2(30) | Subpartition name | Subpartition name |
| C4 | VARCHAR2(30) | Reserved field | Column name |
| C5 | VARCHAR2(30) | Owner | Owner |
| C6 | VARCHAR2(30) | Reserved field | Reserved field |
| N1 | NUMBER | Num rows | Num distinct |
| N2 | NUMBER | Blocks | Density |
| N3 | NUMBER | Average row length | Reserved field |
| N4 | NUMBER | Sample size | Sample size |
| N5 | NUMBER | Reserved field | Num nulls |
| N6 | NUMBER | Reserved field | Lower value |
| N7 | NUMBER | Reserved field | High value |
| N8 | NUMBER | Reserved field | Average column length |
| N9 | NUMBER | Reserved field | Reserved field |
| N10 | NUMBER | Reserved field | Endpoint number |
| N11 | NUMBER | Reserved field | Endpoint value |
| N12 | NUMBER | Reserved field | Endpoint repeat count |
| N13 | NUMBER | Reserved field | Reserved field |
| D1 | DATE | Last analyzed | Last analyzed |
| T1 | TIMESTAMP(6) WITH TIME ZONE | Reserved field | Reserved field |
| R1 | RAW(32) | Reserved field | Lower raw value |
| R2 | RAW(32) | Reserved field | High raw value |
| R3 | RAW(32) | Reserved field | Endpoint actual value raw |
| CH1 | VARCHAR2(1000) | Reserved field | Reserved field |
| CL1 | CLOB | Reserved field | Reserved field |
| BL1 | BLOB | Reserved field | Reserved field |
| OB_SPEC1 | NUMBER | OB macro block count | OB LLC bitmap size |
| OB_SPEC2 | NUMBER | OB micro block count | Reserved field |
| OB_SPEC3 | VARCHAR2(4096) | Reserved field | OB LLC bitmap (ND synopsis) |
The DBMS_STATS package provides the stored procedures create_stat_table and drop_stat_table to create and drop the statistics user table, as defined below:
PROCEDURE create_stat_table(
ownname VARCHAR2,
stattab VARCHAR2,
tblspace VARCHAR2 DEFAULT NULL,
global_temporary BOOLEAN DEFAULT FALSE
);
PROCEDURE drop_stat_table(
ownname VARCHAR2,
stattab VARCHAR2
);
The following table describes the parameters.
Parameter |
Description |
|---|---|
| ownname | The name of the database where the table is located. |
| stattab | Specifies the name of the statistics user table to be created. |
| tblspace | Table group name. |
| global_temporary | This parameter is not implemented and is unavailable. |
Export functionality
Currently, the following methods are supported for exporting statistics:
Use the stored procedure
export_table_statsto export table-level statistics.Use the stored procedure
export_column_statsto export column-level statistics.Use the stored procedure
export_schema_statsto export statistics of all tables in a schema.Use the stored procedure
export_index_statsto export index statistics.
The stored procedures are defined as follows:
PROCEDURE export_table_stats (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
stattab VARCHAR2,
statid VARCHAR2 DEFAULT NULL,
cascade BOOLEAN DEFAULT TRUE,
statown VARCHAR2 DEFAULT NULL,
stat_category VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY
);
PROCEDURE export_column_stats (
ownname VARCHAR2,
tabname VARCHAR2,
colname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
stattab VARCHAR2,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL
);
PROCEDURE export_schema_stats (
ownname VARCHAR2,
stattab VARCHAR2,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL
);
PROCEDURE export_index_stats (
ownname VARCHAR2,
indname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
stattab VARCHAR2,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
tabname VARCHAR2 DEFAULT NULL
);
The following table describes the parameters.
Parameter |
Description |
|---|---|
| ownname | The name of the database where the table is located. |
| tabname | The name of the table. |
| indname | The name of the index. |
| partname | The name of the partition. |
| colname | The name of the column. |
| cascade | Specifies whether to export column-level statistics at the same time. Default value: TRUE. |
| stattab | Specifies the name of the statistics user table to which statistics are exported. |
| statid | This parameter is not implemented and is unavailable. |
| statown | Specifies the username of the statistics user table to which statistics are exported. |
| stat_category | This parameter is not implemented and is unavailable. |
Import functionality
Currently, the following methods are supported for importing statistics:
Use the stored procedure
import_table_statsto import table-level statistics.Use the stored procedure
import_column_statsto import column-level statistics.Use the stored procedure
import_schema_statsto import statistics of all tables in a schema.Use the stored procedure
import_index_statsto import index statistics.
The stored procedures are defined as follows:
PROCEDURE import_table_stats (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
stattab VARCHAR2,
statid VARCHAR2 DEFAULT NULL,
cascade BOOLEAN DEFAULT TRUE,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT FALSE,
force BOOLEAN DEFAULT FALSE,
stat_category VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY
);
PROCEDURE import_column_stats (
ownname VARCHAR2,
tabname VARCHAR2,
colname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
stattab VARCHAR2,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT FALSE,
force BOOLEAN DEFAULT FALSE
);
PROCEDURE import_schema_stats (
ownname VARCHAR2,
stattab VARCHAR2,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT FALSE,
force BOOLEAN DEFAULT FALSE
);
PROCEDURE import_index_stats (
ownname VARCHAR2,
indname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
stattab VARCHAR2,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT FALSE,
force BOOLEAN DEFAULT FALSE,
tabname VARCHAR2 DEFAULT NULL
);
The following table describes the parameters.
Parameter |
Description |
|---|---|
| ownname | The name of the database where the table is located. |
| tabname | The name of the table. |
| indname | The name of the index. |
| partname | The name of the partition. |
| colname | The name of the column. |
| cascade | Specifies whether to import column-level statistics at the same time. Default value: TRUE. |
| stattab | Specifies the name of the statistics user table from which statistics are imported. |
| statid | This parameter is not implemented and is unavailable. |
| statown | Specifies the username of the statistics user table from which statistics are imported. |
| stat_category | This parameter is not implemented and is unavailable. |
| no_invalidate | This parameter is not implemented and is unavailable. |
| force | Specifies whether to perform forced deletion and ignore the lock status. Default value: FALSE. |
Examples
Create the statistics user table
test_statfor usertest.CALL dbms_stats.create_stat_table('test', 'test_stat');Export the statistics of the
tbl1table of usertesttotest_stat.CALL dbms_stats.export_table_stats('test', 'tbl1', stattab=>'test_stat', statown=>'test');Import the statistics of the
tbl1table under usertestfrom thetest_stattable of usertest.CALL dbms_stats.import_table_stats('test', 'tbl1', stattab=>'test_stat', statown=>'test');Drop the statistics user table
test_statof usertest.CALL dbms_stats.drop_stat_table('test', 'test_stat');