Use DBMS_STATS to import and export Oracle statistics

Today, the customer reported that the query was slow. Because the query was in a production environment, the test was not allowed. Therefore, refer to the following blog to export the statistics to the test server for SQL statement optimization. 1. Create a storage

Today, the customer reported that the query was slow. Because the query was in a production environment, the test was not allowed. Therefore, refer to the following blog to export the statistics to the test server for SQL statement optimization. 1. Create a storage

Today, the customer reported that the query was slow. Because the query was in a production environment, the test was not allowed. Therefore, refer to the following blog to export the statistics to the test server for SQL statement optimization.

1. Create a storage statistics table
EXECUTE DBMS_STATS.CREATE_STAT_TABLE ('Scott ', 'stattab', 'sysaux ');

Create stattab on the sysaux tablespace to store statistics. The owner is SCOTT.

2. Export schema statistics
EXEC dbms_stats.EXPORT_SCHEMA_STATS
(Ownname => 'Scott ', stattab => 'stattab', STATID => 'foo _ 100 ');

We recommend that you set the STATID. STATID naming rules manually. We recommend that you use the Object Name (SCHEMA name) + time (granularity ).

Export table statistics
EXEC dbms_stats.export_table_stats
(OWNNAME => 'Scott ', TABNAME => 'foo', STATTAB => 'stattab', STATID => 'foo _ 100 ');

3. Use expdp to export the statistics table
Expdp system/Oracle DIRECTORY = expdimp DUMPFILE = scottexp201102.16.dmp SCHEMAS = 'Scott 'logfile = scottexp20120427.log EXCLUDE = TABLE: \ "IN \ (\ 'stattab '\'\)\"

4. Upload the expdp exported file scottexp201102.16.dmp to the test server. There are many methods, such as scp.

5. Import schema statistics

Restore the statistics of the table (the current statistics should be exported before ):
Exec DBMS_STATS.IMPORT_SCHEMA_STATS (ownname => 'Scott ', stattab => 'stattab', STATID => 'foo _ 20120427 ');

Statistics of the imported table
EXEC dbms_stats.import_table_stats
(OWNNAME => 'Scott ', TABNAME => 'foo', STATTAB => 'stattab', STATID => 'foo _ 100 ');

Ownname can specify a new

6. Delete the statistics table

Exec DBMS_STATS.DROP_STAT_TABLE ('Scott ', 'stattab ');