Dbms_stats import and export table statistics

When dbms_stats Imports and Exports table statistics in SQL tuning, incorrect or outdated statistics result in incorrect execution plans being used. Of course, in this case, we can collect the latest statistics to achieve the goal of optimization. In addition, the statistical information before Oracle is automatically retained. In addition, we can also import and export statistics through backup. Even if this method is described, this article compares the execution plans of different statistics, and finally provides a code to export statistics in batches. 1. Create a demo Environment [SQL] scott @ USBO> select * from v $ version where rownum <2; BANNER orders Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production -- create a demo table, insert the table record scott @ USBO> create TABLE t1 nologging tablespace tbs1 as select * from dba_objects where owner = 'sys 'and object_type = 'table '; -- add all non-sys records scott @ USBO> insert into t1 select * from dba_objects where owner <> 'sys '; 43172 rows created. scott @ USBO> commit; scott @ USBO> create index I _t1_owner on t1 (owner); ---> Add index -- collect statistics scott @ USBO> exec dbms_stats.gather_table_stats ('Scott ', 't1', cascade => true); PL/SQL procedure successfully completed. -- in this case, the table sys has 1001 scott @ USBO> select owner, count (*) from t1 where owner = 'sys 'group by owner; owner count (*) ---------------------------- ---------- SYS 1001 -- The following is the execution plan scott @ USBO> set autot trace exp; scott @ USBO> select owner, count (*) from t1 where owner = 'sys' group by owner; Execution Plan ------------------------------------------------------------ Plan hash value: 832695366 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | minute | 0 | select statement | 1 | 8 | 4 (0) | 00:00:01 | 1 | sort group by nosort | 1 | 8 | 4 (0) | 00:00:01 | * 2 | index range scan | I _T1_OWNER | 1425 | 11400 | 4 (0) | 00:00:01 | scott ----------------------------------------------------------------------------------- @ USBO> set autot off; 2. Export statistical information [SQL] -- first create a stage table scott @ USBO> exec dbms_stats.create_stat_table (ownname => 'Scott ', stattab => 'st _ t1', tblspace => 'tbs1'); PL/SQL procedure successfully completed. -- the following uses the export_table_stats process to export statistics. statid is A scott @ USBO> exec dbms_stats.export_table_stats (ownname => 'Scott ', tabname => 't1 ', stattab => 'st _ t1', statid => 'A'); PL/SQL procedure successfully completed. -- Insert a new record. All objects of the SYS non-Table type are inserted, there are 30043 pieces of scott @ USBO> insert into t1 nologging select * from dba_objects where owner = 'sys 'and object_type <> 'table'; 30043 rows created. scott @ USBO> commit; -- collect statistics scott @ USBO> exec dbms_stats.gather_table_stats ('Scott ', 'T1', cascade => true); -- export statistics again. Note that, in this case, statid is B scott @ USBO> exec dbms_stats.export_table_stats (ownname => 'Scott ', tabname => 't1', stattab => 'st _ t1 ', statid => 'B'); PL/SQL procedure successfully completed. -- Next we will analyze the execution plan of the original SQL statement scott @ USBO> set autot trace exp; scott @ USBO> select owner, count (*) from t1 where owner = 'sys' group by owner; Execution Plan ------------------------------------------------------------ Plan hash value: 453826725 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | minute | 0 | select statement | 1 | 6 | 58 (0) | 00:00:01 | 1 | sort group by nosort | 1 | 6 | 58 (0) | 00:00:01 | * 2 | index fast full scan | I _T1_OWNER | 31349 | 183K | 58 (0) | 00:00:01 | latest -- the latest statistics are used in the preceding execution plan, in addition, the estimated number of rows is 31349 close to the number of rows in the Table. 3. The old statistics have been imported and compared to the execution plan. [SQL] -- the following uses import_table_stats to import the old statistics. scott @ USBO> exec dbms_stats.import_table_stats (ownname => 'Scott ', tabname => 't1', stattab => 'st _ t1',-> statid => 'A', no_invalidate => true); PL/SQL procedure successfully completed. -- view the Execution Plan of the original SQL statement scott @ USBO> select owner, count (*) from t1 where owner = 'sys 'group by owner; Execution Plan -------------------------------------------------- Plan hash value: 832695366 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 1 | 8 | 4 (0) | 00:00:01 | 1 | sort group by nosort | 1 | 8 | 4 (0) | 00:00:01 | * 2 | index range scan | I _T1_OWNER | 1425 | 11400 | 4 (0) | 00:00:01 | Author -- Author: Leshami Blog: http://blog.csdn.net/leshami -- From the above execution plan, although the execution plan is the same as the previous two execution plans, the estimated number of rows is the previous number of rows, there are only 1425 records-that is, due to outdated statistics 4. Batch export of table statistics [SQL]-The following anonymous pl/SQL blocks can be used to export statistics in batches, it can be used when an SQL statement involves multiple tables. You can export all related table statistics. Note that the table name cannot exceed 28, because the statistical backup table I defined here occupies two characters starting with "S _"-you can modify its code as needed, such as adding table space parameters. -- For the script for the statistics of the volume import table, you can refer to the following script to modify the process: import_table_stats DECLARE v_table_name VARCHAR2 (30); v_stat_name VARCHAR2 (35 ); v_ SQL _stat VARCHAR2 (200); v_schema VARCHAR2 (30): = 'Scott '; -- Define your table you want to export stat CURSOR cur_tab is select table_name FROM dba_tables WHERE table_name IN ('emp', 'dept', 'bonus '); begin for cur_rec IN cur_tab LOOP v_stat_name: ='s _ '| cur_rec.table_name; v_ SQL _stat: = 'in IN DBMS_STATS.create_stat_table (''' | v_schema | ''', ''' | v_stat_name | '''); END; '; -- DBMS_OUTPUT.put_line (v_ SQL _stat); execute immediate v_ SQL _stat; v_ SQL _stat: = 'in in DBMS_STATS.export_table_stats (''' | v_schema | ''', tabname => ''' | cur_rec.table_name | ''', stattab => ''' | v_stat_name | '''); END; '; -- DBMS_OUTPUT.put_line (v_ SQL _stat); execute immediate v_ SQL _stat; END LOOP; END;