统计信息备份

下面例子中，都是使用SEC用户作为测试用户

例子中函数只使用常用参数，部分参数未使用，可以查看oracle 在线帮助手册

位置：

步骤一：创建储统备份计信息的表

SQL> conn sec/sec                 Connected.                 SQL>                   SQL> exec dbms_stats.create_stat_table(ownname => 'sec',stattab => 'stat_backup_table') ;                  PL/SQL procedure successfully completed.                 SQL> select * from stat_backup_table;                 no rows selected                                 SQL>

这个时候只是在SEC用户下创建一个没有记录的表stat_backup_table。

步骤二：保存统计信息

SQL> exec dbms_stats.export_schema_stats(ownname => 'sec',stattab => 'stat_backup_table') ;                  PL/SQL procedure successfully completed.                 SQL>                  SQL> select count(*) from stat_backup_table;                   COUNT(*)                 ----------                         18                 SQL>

这个时候，就会把SEC下面所有统计信息备份到表stat_backup_table。

步骤三：查看原来的表和索引的统计信息

列的统计信息                 SQL> select  t.table_name,t.column_name,t.last_analyzed from dba_tab_col_statistics t                    2   where t.owner='SEC' AND t.table_name in ('COLOCATED','DISORGANIZED','TESTT');                                 TABLE_NAME                     COLUMN_NAME                    LAST_ANAL                 ------------------------------ ------------------------------ ---------                 TESTT                          FLAG                           30-DEC-13                 TESTT                          ID                             30-DEC-13                 DISORGANIZED                   Y                              23-DEC-13                 DISORGANIZED                   X                              23-DEC-13                 COLOCATED                      Y                              23-DEC-13                 COLOCATED                      X                              23-DEC-13                                 6 rows selected.

索引的统计信息                 SQL> set linesize 300                  SQL> select t.index_name,table_owner,table_name,last_analyzed                   2    from dba_indexes  t where t.OWNER='SEC'                   3     and t.table_name in ('COLOCATED','DISORGANIZED','TESTT');                                 INDEX_NAME                     TABLE_OWNER                    TABLE_NAME                     LAST_ANAL                 ------------------------------ ------------------------------ ------------------------------ ---------                 COLOCATED_PK                   SEC                            COLOCATED                      23-DEC-13                 DISORGANIZED_PK                SEC                            DISORGANIZED                   23-DEC-13                 INX_TESTT                      SEC                            TESTT                          30-DEC-13                 SQL>

表的统计信息                 SQL>                  SQL> select t.TABLE_NAME, t.LAST_ANALYZED                   2   from dba_tables t where t.OWNER = 'SEC'                   3    and t.table_name in ('COLOCATED', 'DISORGANIZED', 'TESTT');                                 TABLE_NAME                     LAST_ANAL                 ------------------------------ ---------                 TESTT                          30-DEC-13                 DISORGANIZED                   23-DEC-13                 COLOCATED                      23-DEC-13                 SQL>

上面3个结果主要看LAST_ANAL这列，在下面步骤要重新生成统计信息，这样LAST_ANAL的时候会有有变化

步骤四：重新生成统计信息，查看LAST_ANAL的变化

SQL> exec dbms_stats.gather_table_stats(ownname => 'SEC',tabname => 'COLOCATED',estimate_percent => 10,method_opt=> 'for all columns') ;                                  PL/SQL procedure successfully completed.                 SQL>                  SQL> exec dbms_stats.gather_table_stats(ownname => 'SEC',tabname => 'DISORGANIZED',estimate_percent => 10, method_opt=> 'for all  columns') ;                                   PL/SQL procedure successfully completed.                 SQL>                  SQL> exec dbms_stats.gather_table_stats(ownname => 'SEC',tabname => 'TESTT',estimate_percent => 10,method_opt=> 'for all columns') ;                                  PL/SQL procedure successfully completed.                                 SQL>                   SQL>

重新收集3个表的统计信息，查询相关统计信息视图

列的统计信息                 SQL> select  t.table_name,t.column_name,t.last_analyzed from dba_tab_col_statistics t                   2   where t.owner='SEC' AND t.table_name in ('COLOCATED','DISORGANIZED','TESTT');                                 TABLE_NAME                     COLUMN_NAME                    LAST_ANAL                 ------------------------------ ------------------------------ ---------                 TESTT                          FLAG                           17-DEC-14                 TESTT                          ID                             17-DEC-14                 DISORGANIZED                   Y                              17-DEC-14                 DISORGANIZED                   X                              17-DEC-14                 COLOCATED                      Y                              17-DEC-14                 COLOCATED                      X                              17-DEC-14                                 6 rows selected.                                 SQL>

索引的统计信息                 SQL> select t.index_name,table_owner,table_name,last_analyzed                   2   from dba_indexes  t where t.OWNER='SEC'                   3    and t.table_name in ('COLOCATED','DISORGANIZED','TESTT');                                 INDEX_NAME                     TABLE_OWNER                    TABLE_NAME                     LAST_ANAL                 ------------------------------ ------------------------------ ------------------------------ ---------                 COLOCATED_PK                   SEC                            COLOCATED                      17-DEC-14                 DISORGANIZED_PK                SEC                            DISORGANIZED                   17-DEC-14                 INX_TESTT                      SEC                            TESTT                          17-DEC-14                                 SQL>

表的统计信息                                 SQL>                  SQL> select t.TABLE_NAME, t.LAST_ANALYZED                   2   from dba_tables t where t.OWNER = 'SEC'                   3    and t.table_name in ('COLOCATED', 'DISORGANIZED', 'TESTT');                                 TABLE_NAME                     LAST_ANAL                 ------------------------------ ---------                 TESTT                          17-DEC-14                 DISORGANIZED                   17-DEC-14                 COLOCATED                      17-DEC-14                                 SQL>

可以看到相关统计信息的LAST_ANAL字段时间已经变化

步骤五：还原备份的统计信息

导入单个索引的信息                 SQL> exec dbms_stats.import_index_stats(ownname => 'SEC',indname => 'COLOCATED_PK',stattab => 'stat_backup_table') ;                                 PL/SQL procedure successfully completed.                                 SQL>                  SQL> select t.index_name,table_owner,table_name,last_analyzed                   2    from dba_indexes  t where t.OWNER='SEC'                   3     and t.table_name in ('COLOCATED','DISORGANIZED','TESTT');                                 INDEX_NAME                     TABLE_OWNER                    TABLE_NAME                     LAST_ANAL                 ------------------------------ ------------------------------ ------------------------------ ---------                 COLOCATED_PK                   SEC                            COLOCATED                      23-DEC-13                 DISORGANIZED_PK                SEC                            DISORGANIZED                   17-DEC-14                 INX_TESTT                      SEC                            TESTT                          17-DEC-14                                 SQL>

导入单个表的信息                                 SQL>                  SQL> exec dbms_stats.import_table_stats(ownname => 'SEC',tabname => 'COLOCATED',stattab => 'stat_backup_table') ;                                  PL/SQL procedure successfully completed.                                 SQL>                  SQL> select t.TABLE_NAME, t.LAST_ANALYZED                   2    from dba_tables t where t.OWNER = 'SEC'                   3     and t.table_name in ('COLOCATED', 'DISORGANIZED', 'TESTT');                                 TABLE_NAME                     LAST_ANAL                 ------------------------------ ---------                 TESTT                          17-DEC-14                 DISORGANIZED                   17-DEC-14                 COLOCATED                      23-DEC-13                 SQL>                                  SQL> select  t.table_name,t.column_name,t.last_analyzed from dba_tab_col_statistics t                   2   where t.owner='SEC' AND t.table_name in ('COLOCATED','DISORGANIZED','TESTT');                                 TABLE_NAME                     COLUMN_NAME                    LAST_ANAL                 ------------------------------ ------------------------------ ---------                 TESTT                          FLAG                           17-DEC-14                 TESTT                          ID                             17-DEC-14                 DISORGANIZED                   Y                              17-DEC-14                 DISORGANIZED                   X                              17-DEC-14                 COLOCATED                      Y                              23-DEC-13                 COLOCATED                      X                              23-DEC-13                                 6 rows selected.

如果一个一个的表比较麻烦，可以导入刚才所有的备份信息

SQL> exec dbms_stats.import_schema_stats(ownname => 'SEC',stattab => 'stat_backup_table');                                 PL/SQL procedure successfully completed.                                 SQL> select  t.table_name,t.column_name,t.last_analyzed from dba_tab_col_statistics t                   2   where t.owner='SEC' AND t.table_name in ('COLOCATED','DISORGANIZED','TESTT');                                 TABLE_NAME                     COLUMN_NAME                    LAST_ANAL                 ------------------------------ ------------------------------ ---------                 TESTT                          FLAG                           30-DEC-13                 TESTT                          ID                             30-DEC-13                 DISORGANIZED                   Y                              23-DEC-13                 DISORGANIZED                   X                              23-DEC-13                 COLOCATED                      Y                              23-DEC-13                 COLOCATED                      X                              23-DEC-13                                 6 rows selected.                                 SQL>                  SQL> select t.TABLE_NAME, t.LAST_ANALYZED                   2   from dba_tables t where t.OWNER = 'SEC'                   3    and t.table_name in ('COLOCATED', 'DISORGANIZED', 'TESTT');                                 TABLE_NAME                     LAST_ANAL                 ------------------------------ ---------                 TESTT                          30-DEC-13                 DISORGANIZED                   23-DEC-13                 COLOCATED                      23-DEC-13                                 SQL>                  SQL> select t.index_name,table_owner,table_name,last_analyzed                   2    from dba_indexes  t where t.OWNER='SEC'                   3     and t.table_name in ('COLOCATED','DISORGANIZED','TESTT');                                 INDEX_NAME                     TABLE_OWNER                    TABLE_NAME                     LAST_ANAL                 ------------------------------ ------------------------------ ------------------------------ ---------                 COLOCATED_PK                   SEC                            COLOCATED                      23-DEC-13                 DISORGANIZED_PK                SEC                            DISORGANIZED                   23-DEC-13                 INX_TESTT                      SEC                            TESTT                          30-DEC-13                                 SQL>