一张表的统计信息的导入导出-测试

--创建演示表，并插入所有sys用户的表记录  
test@USBO> create table t1 nologging tablespace tbs1 as select * from dba_objects where owner='SYS' and object_type='TABLE';  
  
--添加所有非sys的记录  
test@USBO> insert into t1 select * from dba_objects where owner <>'SYS';   
43172 rows created.  
  
test@USBO> commit;  
test@USBO> create index i_t1_owner on t1(owner);       
  
--收集统计信息  
test@USBO> exec dbms_stats.gather_table_stats('TEST','T1',cascade=>true);   
PL/SQL procedure successfully completed.  


--查看统计信息时间
SQL> select to_char(last_analyzed ,'yyyy-mm-dd hh24:mi:ss') from dba_tables where table_name='T1' AND owner='TEST';
TO_CHAR(LAST_ANALYZ
-------------------
2016-10-09 10:26:42

--此时表上sys用户的表位1001个  
test@USBO> select owner,count(*) from t1 where owner='SYS' group by owner;  
  
OWNER                            COUNT(*)  
------------------------------ ----------  
SYS                                  1001  
  
--下面是其执行计划  
test@USBO> set autot trace exp;  
test@USBO> select owner,count(*) from t1 where owner='SYS' group by owner;  
  
Execution Plan  
----------------------------------------------------------  
Plan hash value: 832695366   
-----------------------------------------------------------------------------------  
| Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time     |  
-----------------------------------------------------------------------------------  
|   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 |  
-----------------------------------------------------------------------------------  
  
test@USBO>set autot off;  

2、导出统计信息
--首先创建用于存放统计信息的舞台表  
test@USBO> exec dbms_stats.create_stat_table (ownname => 'TEST', stattab => 'ST_T1', tblspace => 'USERS');   
PL/SQL procedure successfully completed.  


在test用户下会生成一个ST_T1表
SQL> select * from tab ;


TNAME       TABTYPE CLUSTERID
------------------ --------- ----------
EMP       TABLE
EMP_TEMP       TABLE
ST_T1       TABLE   ---用于存放统计信息的表ST_T1

3 rows selected.
  
--下面使用export_table_stats过程导出统计信息，此时statid为A  
test@USBO> exec dbms_stats.export_table_stats(ownname =>'TEST',tabname=>'T1',stattab=>'ST_T1',statid => 'A');    
PL/SQL procedure successfully completed.  
  
--插入新的记录，此时为SYS非表类型的所有对象，有30043条  
test@USBO> insert into t1 nologging select * from dba_objects where owner='SYS' and object_type<>'TABLE';   
30043 rows created.  
  
test@USBO> commit;  
  
--收集统计信息  
test@USBO> exec dbms_stats.gather_table_stats('TEST','T1',cascade=>true);  

--查看统计信息时间
SQL> select to_char(last_analyzed ,'yyyy-mm-dd hh24:mi:ss') from dba_tables where table_name='T1' AND owner='TEST';
TO_CHAR(LAST_ANALYZ
-------------------
2016-10-09 10:29:32
  
--再次导出统计信息，注意，此时的statid为B  
test@USBO> exec dbms_stats.export_table_stats(ownname =>'TEST',tabname=>'T1',stattab=>'ST_T1',statid => 'B');    
PL/SQL procedure successfully completed.  

--下面我们分析原SQL的执行计划  
test@USBO> set autot trace exp;  
test@USBO> select owner,count(*) from t1 where owner='SYS' group by owner;  
  
Execution Plan  
----------------------------------------------------------  
Plan hash value: 453826725    
------------------------------------------------------------------------------------  
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time     |  
------------------------------------------------------------------------------------  
|   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 |  
------------------------------------------------------------------------------------  
  
--上面的执行计划中使用了最新的统计信息，而且预估的行数31349接近于表上的行数  


3、导入过旧的统计信息并对比执行计划
 
--下面使用import_table_stats导入之前过旧的统计信息  
test@USBO> exec dbms_stats.import_table_stats(ownname => 'TEST', tabname => 'T1', stattab => 'ST_T1', statid => 'A', no_invalidate => true);    
PL/SQL procedure successfully completed.  

--查看统计信息时间
SQL> select to_char(last_analyzed ,'yyyy-mm-dd hh24:mi:ss') from dba_tables where table_name='T1' AND owner='TEST';
TO_CHAR(LAST_ANALYZ
-------------------
2016-10-09 10:26:42
  
--再次查看原SQL的执行计划  
test@USBO> select owner,count(*) from t1 where owner='SYS' group by owner;   
Execution Plan  
----------------------------------------------------------  
Plan hash value: 832695366   
-----------------------------------------------------------------------------------  
| Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time     |  
-----------------------------------------------------------------------------------  
|   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 |  
-----------------------------------------------------------------------------------  

--从上面的执行计划中来看，尽管执行计划与先前的两个执行计划相同，但预估的行数是之前的行数，只有1425条记录  
--也即是由于过时的统计信息造成的  

--删除统计信息之后重新导入统计信息
begin
dbms_stats.delete_table_stats
(
ownname => 'TEST',
tabname => 'T1' 
);
end;
/

--查看统计信息时间
SQL> select to_char(last_analyzed ,'yyyy-mm-dd hh24:mi:ss') from dba_tables where table_name='T1' AND owner='TEST';
TO_CHAR(LAST_ANALYZ
-------------------

--导入statid ='A'的统计信息
exec dbms_stats.import_table_stats(ownname => 'TEST', tabname => 'T1', stattab => 'ST_T1', statid => 'A', no_invalidate => true);

--查看统计信息时间
SQL> select to_char(last_analyzed ,'yyyy-mm-dd hh24:mi:ss') from dba_tables where table_name='T1' AND owner='TEST';
TO_CHAR(LAST_ANALYZ
-------------------
2016-10-09 10:26:42

--再次删除统计信息
begin
dbms_stats.delete_table_stats
(
ownname => 'TEST',
tabname => 'T1' 
);
end;
/

--查看统计信息时间
SQL> select to_char(last_analyzed ,'yyyy-mm-dd hh24:mi:ss') from dba_tables where table_name='T1' AND owner='TEST';
TO_CHAR(LAST_ANALYZ
-------------------

导入statid ='B'的统计信息
exec dbms_stats.import_table_stats(ownname => 'TEST', tabname => 'T1', stattab => 'ST_T1', statid => 'B', no_invalidate => true);

--查看统计信息时间
SQL> select to_char(last_analyzed ,'yyyy-mm-dd hh24:mi:ss') from dba_tables where table_name='T1' AND owner='TEST';
TO_CHAR(LAST_ANALYZ
-------------------
2016-10-09 10:29:32

统计信息导入成功！！！！

--可以使用expdp和impdp导出ST_T1表，在迁移的时候进行统计信息的迁移。
SQL> create directory d1 as '/home/oracle/';
SQL> grant all on directory d1 to public ;

--导出ST_T1
[oracle@test01 ~]$ expdp test/oracle directory=d1 dumpfile=st_t1.dmp logfile=st_t1.log tables=st_t1

SQL> drop table st_t1 purge ;

--导入ST_T1
[oracle@test01 ~]$ impdp test/oracle directory=d1 dumpfile=st_t1.dmp logfile=st_t1_impdp.log

--再直接使用
exec dbms_stats.import_table_stats(ownname => 'TEST', tabname => 'T1', stattab => 'ST_T1', statid => 'A', no_invalidate => true);
导入统计信息。

--删除用于存放统计信息的表
exec DBMS_STATS.DROP_STAT_TABLE('TEST','ST_T1');
此时test用户下的ST_T1表将会被删除。