Dbms_stats Import and Export schema-level statistics

Dbms_stats Import and Export schema-level statistics are used in Oracle databases using the CBO optimizer mode. statistics are an important basis for CBO to generate the best execution plan. These statistics usually include column-level, table-level, index, and system-level statistics. All such statistics can be backed up, and the import/export operations can also be locked and unlocked. Therefore, we can export statistics at the column level, table level, index level, and system level. By exporting import statistics, you can simulate the production environment in the test environment for database performance optimization and SQL optimization. This article mainly describes how to export import statistics to different databases based on the schema level. 1. Import and export the statistical information. a. Import the statistical information of the production environment to the test environment so that the execution plan can be generated to a great extent equivalent to the production environment B. Import and Export you can freeze the execution plan, control and compare Execution Plan c in different environments. Statistical information can be backed up before re-analyze schema, prevent performance degradation after analyze. d. System-level statistics can be transplanted to small servers to simulate Oracle running environment on large servers. e. System-level statistics can also be used for migration to new servers. ensure consistent execution plans until new server f is used and different statistical information is used for different workloads, appropriate statistics can be provided before these loads are run (such as OLTP during the day and the batch job mode at night) 2. Export schema-level statistics a. Collect statistics (source schema or system level) B. Create a table for storing statistics (such as stats_table) c. Use dbms_stats.export_schema _ Stats: Export schema statistics to the stats_table d table and use datapump expdp to export the stats_table table. (SQL * Plus copy can be used to avoid import and export) e. Use ftp or scp/cp dump to import the dmp file to the target schema server f, and use datapump impdp to import the dmp file to the target schema. If you need to back up the file, back up the original statistical information g, use dbms_stats.import_system_stats to import the statistical information to the required schema 3, and create an example of statistical information. The following is an example of collecting statistics at the schema scott level. [SQL] BEGIN DBMS_STATS.gather_schema_stats (ownname => 'Scott ', options => 'gather auto', estimate_percent => DBMS_STATS.auto_sample_size, method_opt => 'for all columns size repeat ', Degree => 8); END; /4. Demonstrate export import statistics export different DB [SQL] -- Next we will import scott's statistics on the database mmbo5 to scott under the other database mmbo4 a. Collect statistics scott @ MMBO5> delete from emp where deptno = 20; scott @ MMBO5> commit; scott @ MMBO5> exec dbms_stats.gather_schema_stats ('Scott ', cascade => true); SCOTT @ MMBO5> select table_name, num_rows, last_analyzed from user_tables; TABLE_NAME NUM_ROWS LAST_ANALYZED -------------------------------------------------- ------- DEPT 4 20130513 22:19:10 EMP 9 20130513 22:19:10 ---> the emp table has five records deleted and nine BONUS 0 20130513 22:19:10 SALGRADE 5 20130513 22:19:10 TT 4 20130513 22:19:10 B is created for storage. statistical information table -- you can specify the tablespace in which the table is stored, if it is set to default, it is stored in the user's default tablespace scott @ MMBO5> exec dbms_stats.create_stat_table ('Scott ', 'stats _ table'); PL/SQL procedure successfully completed. c. Export scott's statistics to stats_table scott @ MMBO5> exec dbms_stats.export_schema_stats ('Scott', 'Stats _ table', 'Scott '); PL/SQL procedure successfully completed. SCOTT @ MMBO5> exit d, export statistics to the dump file oracle @ Dev-DB-04: ~> Expdp scott/tiger directory = db_dump_dir dumpfile = st. dmp logfile = st. log tables = STATS_TABLE Export: Release 10.2.0.4.0-64bit Production on Monday, 13 May, 2013 22:20:53 Estimate in progress using BLOCKS method... processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type TABLE_EXPORT/TABLE/ INDEX/INDEX .. exported "SCOTT ". "STATS_TABLE" 17.82 KB 74 rows Master table "SCOTT ". "SYS_EXPORT_TABLE_01" successfully loaded/unloaded ********************************* **************************************** * *** Dump file set for SCOTT. SYS_EXPORT_TABLE_01 is:/u02/database/MMBO5/BNR/dump/st. dmp Job "SCOTT ". "SYS_EXPORT_TABLE_01" successfully completed at 22:21:11 e. ftp statistics are sent to the target server and the dump file is imported. In the same server, so directly use the cp command to copy the dump file to a specific directory oracle @ Dev-DB-04: ~> Cp/u02/database/MMBO5/BNR/dump/st. dmp/u02/database/MMBO4/BNR/dump f, use datapump impdp to import the dmp file to the destination schema oracle @ Dev-DB-04: ~> Export ORACLE_SID = MMBO4 oracle @ Dev-DB-04: ~> Impdp scott/tiger directory = db_dump_dir dumpfile = st. dmp logfile = imp_st.log tables = STATS_TABLE Import: Release 10.2.0.4.0-64bit Production on Monday, 13 May, 2013 22:22:57 Processing object type TABLE_EXPORT/TABLE/TABLE_DATA .. imported "SCOTT ". "STATS_TABLE" 17.82 KB 74 rows Processing object type TABLE_EXPORT/TABLE/INDEX Job "SCOTT ". "SYS_IMPORT_TABLE_01" successfully completed at 22:23:03g. Import the statistical information to the schema of the target database. Check the scott statistics of the database before importing. scott @ MMBO4> select table_name, num_rows, last_analyzed from user_tables; TABLE_NAME NUM_ROWS LAST_ANALYZED created ---------- ----------------- STATS_TABLE DEPT 4 20130513 22:16:05 EMP 14 20130513 22:16:05 BONUS 0 20130513 22:16:05 SALGRADE 5 20130513 22:16:05 T 49991 22:16:20 6 rows selected. -- import statistics scott @ MMBO4> exec dbms_stats.import_schema_stats ('Scott ', 'stats _ table', 'Scott'); PL/SQL procedure successfully completed. -- you can obtain the statistical information log alter session set nls_timestamp_tz_format = 'yyyy-mm-dd hh24: mi: ss' in the target database through the query view dba_optstat_operations '; COL start_time FORMAT a14 COL end_time FORMAT a14 COL operation FORMAT a30 COL target FORMAT a30 set linesize 120 SELECT operation, target, start_time, end_time FROM dba_optstat_operations WHERE operation LIKE 'import % '; operation target START_TIME END_TIME ------------------------------ -------------------------- import_schema_stats SCOTT 2013-05-13 22: 2013-05-13 22: 24:23 24:23 -- The following query also shows that LAST_ANALYZED under scott has been updated to the same statistical information as that on the source server. scott @ MMBO4> select table_name, num_rows, last_analyzed from user_tables; TABLE_NAME NUM_ROWS LAST_ANALYZED ------------------------------ --------------------- STATS_TABLE DEPT 4 20130513 22:19:10 EMP 9 20130513 22:19:10 BONUS 0 20130513 22:19:10 SALGRADE 5 20130513 22:19:10 T 49991 22:16:20 6 rows selected. scott @ MMBO4> select count (*) from emp; COUNT (*) ---------- 14 -- in fact, the emp data in the mmbo4 table is still 14, but the statistics are 9, is the statistical information from the source database-for objects that exist in the source database schema but do not exist in the target database schema, such as the tt table on mmbo5, it will not be imported on mmbo4 -- objects that do not exist in the source database schema but exist in the target database schema, such as table t on mmbo4, the statistics are not updated. h. You can determine whether to clear the TABLE scott @ MMBO4> exec dbms_stats.drop_stat_table ('Scott ', 'stats _ table') that stores the statistics '); PL/SQL procedure successfully completed. -- the system-level statistical information import and export is not demonstrated here. Note that the corresponding import and export procedure in dbms_stats should be used. -- dbms_stats.gather_system_stats -- dbms_stats.import_system_stats -- dbms_stats.export_system_stats