Import and export statistics|V4.3.1| docs|Distributed Database

Import and export statistics

Last Updated：2024-06-28 05:30:31 Updated

The statistics export feature allows you to export table statistics to a specified user table. The import feature allows you to import statistics stored in a specified user table to the current internal table, so that you do not have to re-collect the statistics.

For example, during database migration, you can export the statistics of a large table to a specified user table. After the data is migrated successfully, you can import the statistics previously exported back to the internal statistics table of the new database.

Statistics user tables

A statistics user table stores statistics collected from internal tables. The following table describes the structure of a user 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 for future use	Reserved for future use
FLAGS	NUMBER	Reserved for future use	Reserved for future use
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 for future use	Column Name
C5	VARCHAR2(30)	Owner	Owner
C6	VARCHAR2(30)	Reserved for future use	Reserved for future use
N1	NUMBER	Num Rows	Num Distinct
N2	NUMBER	Blocks	Density
N3	NUMBER	Average Row Length	Reserved for future use
N4	NUMBER	Sample Size	Sample Size
N5	NUMBER	Reserved for future use	Num Nulls
N6	NUMBER	Reserved for future use	Lower Value
N7	NUMBER	Reserved for future use	High Value
N8	NUMBER	Reserved for future use	Average Column Length
N9	NUMBER	Reserved for future use	Reserved for future use
N10	NUMBER	Reserved for future use	Endpoint Number
N11	NUMBER	Reserved for future use	Endpoint Value
N12	NUMBER	Reserved for future use	ENDPOINT_REPEAT_COUNT
N13	NUMBER	Reserved for future use	Reserved for future use
D1	DATE	Last Analyzed	Last Analyzed
T1	TIMESTAMP(6) WITH TIME ZONE	Reserved for future use	Reserved for future use
R1	RAW(32)	Reserved for future use	Lower RAW Value
R2	RAW(32)	Reserved for future use	High RAW Value
R3	RAW(32)	Reserved for future use	ENDPOINT_ACTUAL_VALUE_RAW
CH1	VARCHAR2(1000)	Reserved for future use	Reserved for future use
CH1	CLOB	Reserved for future use	Reserved for future use
BH1	BLOB	Reserved for future use	Reserved for future use
OB_SPEC1	NUMBER	OB Macro Block CNT	OB LLC Bitmap Size
OB_SPEC2	NUMBER	OB Micro Block CNT	Reserved for future use
OB_SPEC3	VARCHAR2(4096)	Reserved for future use	OB LLC Bitmap(ND Synopsis)

The DBMS_STATS package provides stored procedures create_stat_table and drop_stat_table for creating and deleting statistics user tables. The stored procedures are defined as follows:

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	Oracle mode: the username. If the username is set to `NULL`, the current logon username is used by default. MySQL mode: the name of the database to which the table belongs.
stattab	The name of the statistics user table to be created.
tblspace	The name of the table group.
global_temporary	This parameter is not available.

Export

You can export statistics by using the following methods:

Use the stored procedure export_table_stats to export table-level statistics.
Use the stored procedure export_column_stats to export column-level statistics.
Use the stored procedure export_schema_stats to export statistics on all tables in a schema.
Use the stored procedure export_index_stats to 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	Oracle mode: the username. If the username is set to `NULL`, the current logon username is used by default. MySQL mode: the name of the database to which the table belongs.
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. Default value: `TRUE`.
stattab	The name of the statistics user table for storing exported statistics.
statid	This parameter is not available.
statown	The owner of the statistics user table for storing exported statistics.
stat_category	This parameter is not available.

Import

You can import statistics by using the following methods:

Use the stored procedure import_table_stats to import table-level statistics.
Use the stored procedure import_column_stats to import column-level statistics.
Use the stored procedure import_schema_stats to import statistics of all tables in a schema.
Use the stored procedure import_index_stats to 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	Oracle mode: the username. If the username is set to `NULL`, the current logon username is used by default. MySQL mode: the name of the database to which the table belongs.
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. Default value: `TRUE`.
stattab	The name of the statistics user table from which the statistics are retrieved.
statid	This parameter is not available.
statown	The owner of the statistics user table from which the statistics are retrieved.
stat_category	This parameter is not available.
no_invalidate	This parameter is not available.
force	Specifies whether to perform forced deletion and ignore the lock status. Default value: `FALSE`.

Examples

Create the statistics user table test_stat for the test user.

CALL dbms_stats.create_stat_table('test', 'test_stat');

Export the statistics on the tbl1 table of the test user to test_stat.

CALL dbms_stats.export_table_stats('test', 'tbl1', stattab=>'test_stat', statown=>'test');

Import the statistics on the tbl1 table of the test user from the test_stat table owned by the test user.
```
CALL dbms_stats.import_table_stats('test', 'tbl1', stattab=>'test_stat', statown=>'test');
```

Drop the statistics user table test_stat for the test user.

CALL dbms_stats.drop_stat_table('test', 'test_stat');