ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Import parameter: STATISTICS

Import parameter: STATISTICS

原创 Linux操作系统 作者:zhanglei_itput 时间:2009-06-24 15:13:22 0 删除 编辑

 What is the new value SAFE of the STATISTICS parameter at IMPORT ?
------------------------------------------------------------------

The 4 possible values for this parameter are :
    
  -> ALWAYS : Always imports database optimizer statistics regardless of whether or not they are questionable.
                         无论是否有可疑信息,都会导入优化器的统计信息

  -> NONE : Does not import or recalculate the database optimizer statistics.
                     不导入或重新计算优化器的统计信息

  -> SAFE : Imports database optimizer statistics back only if they are not questionable.
                    If they are questionable, recalculates the optimizer statistics.
                    如果无可疑信息,则导入原优化器统计信息
                    如果有可疑信息,则重新计算优化器的统计信息

  -> RECALCULATE : Does not import the database optimizer statistics. Instead, recalculates them on import.
                                       不导入优化器的统计信息,在imp的时候重新收集优化器的统计信息
---------------------------------------------------------------------

When are statistics questionable ?
(什么是统计信息可疑)
----------------------------------

The precalculated optimizer statistics are flagged as questionable at export
time if:
在exp时,如果出现以下几种情况,那么预先计算好的优化器的统计信息被标记为可疑
   -> There are row errors while exporting
       exp时有行的错误
   -> The client character set or NCHAR character set does not match the server character set or NCHAR character set
      客户端和server端的字符集不一致
   -> A QUERY clause is specified
       有查询语句
   -> Only certain partitions or subpartitions are exported
        exp只导出某一个分区或者子分区

e.g.
 1. 导出用户 ecc_view2 的dmp文件 (制造统计信息不是最新的情况)

 a. ECC_VIEW2 检查统计信息
 select  table_name, tablespace_name, status, num_rows, blocks,last_analyzed
 from user_tables
 table_name   tablespace_name   status   num_rows  blocks  last_analyzed
 -----------------   -------------------------  ---------   ---------------  ----------  -----------------------
       TEST                             USERS     VALID                  2              5  2009-6-23 13:47:05

 
 b. 重新收集统计信息
 EXEC DBMS_STATS.gather_table_stats(ownname => 'ECC_VIEW2',tabname => 'TEST',cascade => TRUE);
 
 select  table_name, tablespace_name, status, num_rows, blocks,last_analyzed
 from user_tables
 table_name   tablespace_name   status   num_rows  blocks  last_analyzed
 -----------------   -------------------------  ---------   --------------  ---------  -----------------------
       TEST                             USERS   VALID                   5           5    2009-6-24 14:35:40

      
 c. 插入5条记录,exp
 insert into test   
 select * from TEST
      
select count(*) from test  -- 10 rows

 d. exp ecc_view2
 [oracle@rac1 ~]$ exp ecc_view2/ecc@devdb1 file=./ecc_view2.dmp ;
 . about to export ECC_VIEW2's tables via Conventional Path ...
. . exporting table                           TEST         10 rows exported
Export terminated successfully without warnings.

 2. 创建用户 ecc_view3

create user ecc_view3
identified by ecc
default tablespace DATA03

grant connect, resource to ecc_view3

 3. 导入数据到ecc_view3
 a. statistics = ALWAYS (default)
[oracle@rac1 ~]$ imp ecc_view3/ecc@devdb1 file=./ecc_view2.dmp fromuser=ecc_view2 touser=ecc_view3
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. . importing table                         "TEST"         10 rows imported
Import terminated successfully without warnings.
 select  table_name, tablespace_name, status, num_rows, blocks,last_analyzed
 from user_tables
 table_name   tablespace_name   status   num_rows  blocks  last_analyzed
 ------------------   -----------------------     ------             --------     ------    -----------------------
      TEST                            USERS       VALID                5           5      2009-6-24 14:42:44

 
 b. statistics = none      
 drop table test
 [oracle@rac1 ~]$ imp ecc_view3/ecc@devdb1 file=./ecc_view2.dmp fromuser=ecc_view2 touser=ecc_view3 statistics=none
 select  table_name, tablespace_name, status, num_rows, blocks,last_analyzed
 from user_tables
 table_name   tablespace_name   status   num_rows  blocks  last_analyzed
 ----------   ------------------------------       ------       -------------    ------  -----------------------
 TEST                           USERS           VALID
   
 
 c. statistics = safe      
 drop table test
 [oracle@rac1 ~]$ imp ecc_view3/ecc@devdb1 file=./ecc_view2.dmp fromuser=ecc_view2 touser=ecc_view3 statistics=safe
 select  table_name, tablespace_name, status, num_rows, blocks,last_analyzed
 from user_tables
 table_name   tablespace_name   status   num_rows  blocks               last_analyzed
 ---------------   ---------------------------  ------               --------       ------          -----------------------
      TEST                         USERS     VALID                    10          5        2009-6-24 14:54:51

 d. statistics = RECALCULATE      
 drop table test
 [oracle@rac1 ~]$ imp ecc_view3/ecc@devdb1 file=./ecc_view2.dmp fromuser=ecc_view2 touser=ecc_view3 statistics=RECALCULATE;
 select  table_name, tablespace_name, status, num_rows, blocks,last_analyzed
 from user_tables
 table_name   tablespace_name   status   num_rows  blocks  last_analyzed
 -----------------               ----------------  --------        ------------      ------  ---------------------------
     TEST                                USERS  VALID                    10         5   2009-6-24 14:56:03

 

参考文献:
Subject:  Import parameter: STATISTICS=SAFE
  Doc ID:  159787.1 Type:  BULLETIN
  Modified Date :  11-NOV-2008 Status:  PUBLISHED

    
    

来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/9252210/viewspace-607446/ ,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-02-10

  • 博文量
    400
  • 访问量
    1224685