imp STATISTICS=SAFE

PURPOSE 
------- 
 
This bulletin explains the behavior of the new IMPORT parameter value of 
STATISTICS=SAFE. 
  
 
SCOPE & APPLICATION 
------------------- 
 
This allows to understand which statistics are imported back for a table: 
statistics of the export or statistics recalculated at import ? 
 
 
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.  
 
 
When are statistics questionable ? 
---------------------------------- 
 
The precalculated optimizer statistics are flagged as questionable at export 
time if: 
 
   -> There are row errors while exporting 
   -> The client character set or NCHAR character set does not match the server 
      character set or NCHAR character set 
   -> A QUERY clause is specified 
   -> Only certain partitions or subpartitions are exported 
 
 
*** ************************************************ 
*** Case 1: Export generates questionable statistics 
*** ************************************************ 
 
1. The statistics are recalculated at import since they are defined as  
   questionable at the end of the export, due to a client character set 
   different from the server character set. 
 
     SQL> analyze table t_exp compute statistics; 
     Table analyzed. 
 
     SQL> select TABLE_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, 
       2     AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN 
       3  from user_tables where table_name='T_EXP';     
 
     TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE 
     ------------------------------ ---------- ---------- ------------ ---------- 
      CHAIN_CNT AVG_ROW_LEN 
     ---------- ----------- 
     T_EXP                                1024         61            3       1170 
           1008         113 
 
     SQL> ! 
 
     $ NLS_LANG=american_america.WE8ISO8859P1 
     $ exp scott/tiger tables=t_exp statistics=compute 
 
     Export: Release 9.0.1.1.0 - Production on Thu Oct 4 14:18:21 2001 
     (c) Copyright 2001 Oracle Corporation.  All rights reserved. 
 
     Connected to: Oracle9i Enterprise Edition Release 9.0.1.1.0 - 64bit Production 
     With the Partitioning option 
     JServer Release 9.0.1.0.0 - Production 
     Export done in WE8ISO8859P1 character set and UTF8 NCHAR character set 
     server uses WE8ISO8859P15 character set (possible charset conversion) 
 
     About to export specified tables via Conventional Path ... 
     . . exporting table                          T_EXP       1024 rows exported 
     EXP-00091: Exporting questionable statistics. 
     Export terminated successfully with warnings. 
     $ exit 
 
     SQL> drop table t_exp; 
     Table dropped. 
 
     $ imp scott/tiger full=y statistics=safe 
 
     Import: Release 9.0.1.1.0 - Production on Thu Oct 4 14:18:44 2001 
     (c) Copyright 2001 Oracle Corporation.  All rights reserved. 
 
     Connected to: Oracle9i Enterprise Edition Release 9.0.1.1.0 - 64bit Production 
     With the Partitioning option 
     JServer Release 9.0.1.0.0 - Production 
 
     Export file created by EXPORT:V09.00.01 via conventional path 
     import done in WE8ISO8859P1 character set and UTF8 NCHAR character set 
     import server uses WE8ISO8859P15 character set (possible charset conversion) 
     . importing SCOTT's objects into SCOTT 
     . . importing table                        "T_EXP"       1024 rows imported 
     Import terminated successfully without warnings. 
 
     SQL> select TABLE_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, 
       2         AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN 
       3  from user_tables where table_name='T_EXP'; 
 
     TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE 
     ------------------------------ ---------- ---------- ------------ ---------- 
      CHAIN_CNT AVG_ROW_LEN 
     ---------- ----------- 
     T_EXP                                1024         45            3       1379 
              0         107 
 
2. The statistics are recalculated at import since they are defined as  
   questionable at the end of the export, due to a query used at export. 
 
     SQL> analyze table t_exp compute statistics; 
     Table analyzed. 
 
     SQL> select TABLE_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, 
       2     AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN 
       3  from user_tables where table_name='T_EXP';     
 
     TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE 
     ------------------------------ ---------- ---------- ------------ ---------- 
      CHAIN_CNT AVG_ROW_LEN 
     ---------- ----------- 
     T_EXP                                1024         61            3       1170 
           1008         113 
 
     $ exp scott/tiger tables=t_exp statistics=compute query=/"where rownum /< 100/" 
 
     Export: Release 9.0.1.1.0 - Production on Thu Oct 4 15:32:19 2001 
     (c) Copyright 2001 Oracle Corporation.  All rights reserved. 
 
     Connected to: Oracle9i Enterprise Edition Release 9.0.1.1.0 - 64bit Production 
     With the Partitioning option 
     JServer Release 9.0.1.0.0 - Production 
     Export done in WE8ISO8859P15 character set and UTF8 NCHAR character set 
 
     About to export specified tables via Conventional Path ... 
     . . exporting table                          T_EXP         99 rows exported 
     EXP-00091: Exporting questionable statistics. 
     Export terminated successfully with warnings. 
 
     SQL> drop table t_exp; 
     Table dropped. 
 
     $ imp scott/tiger full=y statistics=safe 
 
     Import: Release 9.0.1.1.0 - Production on Thu Oct 4 14:18:44 2001 
     (c) Copyright 2001 Oracle Corporation.  All rights reserved. 
 
     Connected to: Oracle9i Enterprise Edition Release 9.0.1.1.0 - 64bit Production 
     With the Partitioning option 
     JServer Release 9.0.1.0.0 - Production 
 
     Export file created by EXPORT:V09.00.01 via conventional path 
     import done in WE8ISO8859P15 character set and UTF8 NCHAR character set 
     . importing SCOTT's objects into SCOTT 
     . . importing table                        "T_EXP"       99 rows imported 
     Import terminated successfully without warnings. 
 
 
     SQL> select TABLE_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, 
       2         AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN 
       3  from user_tables where table_name='T_EXP'; 
 
     TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE 
     ------------------------------ ---------- ---------- ------------ ---------- 
      CHAIN_CNT AVG_ROW_LEN 
     ---------- ----------- 
     T_EXP                                  99         13            3       3147 
              0         107 
 
In both cases, the behavior would be similar to STATISTICS set to ALWAYS or 
RECALCULATE. 
 
 
*** **************************************** 
*** Case 2: Export generates SAFE statistics 
*** **************************************** 
1. The statistics are not recalculated at import since they are not defined as  
   questionable at the end of the export (this means safe implicitely) due to  
   the fact that there are none of the above conditions which would lead to  
   questionable statistics. 
 
     SQL> select TABLE_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, 
       2         AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN 
       3  from user_tables where table_name='T_EXP'; 
 
     TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE 
     ------------------------------ ---------- ---------- ------------ ---------- 
      CHAIN_CNT AVG_ROW_LEN 
     ---------- ----------- 
     T_EXP                                1024         61            3       1170 
           1008         113 
 
     $ NLS_LANG=american_america.WE8ISO8859P15         
     $ exp scott/tiger tables=t_exp statistics=compute 
 
     Export: Release 9.0.1.1.0 - Production on Thu Oct 4 14:26:04 2001 
     (c) Copyright 2001 Oracle Corporation.  All rights reserved. 
 
     Connected to: Oracle9i Enterprise Edition Release 9.0.1.1.0 - 64bit Production 
     With the Partitioning option 
     JServer Release 9.0.1.0.0 - Production 
     Export done in WE8ISO8859P15 character set and UTF8 NCHAR character set 
 
     About to export specified tables via Conventional Path ... 
     . . exporting table                          T_EXP       1024 rows exported 
     Export terminated successfully without warnings. 
  
     SQL> drop table t_exp; 
     Table dropped. 
 
     $ NLS_LANG=american_america.WE8ISO8859P15         
     $ imp scott/tiger full=y statistics=safe          
 
     Import: Release 9.0.1.1.0 - Production on Thu Oct 4 14:26:46 2001 
     (c) Copyright 2001 Oracle Corporation.  All rights reserved. 
 
     Connected to: Oracle9i Enterprise Edition Release 9.0.1.1.0 - 64bit Production 
     With the Partitioning option 
     JServer Release 9.0.1.0.0 - Production 
 
     Export file created by EXPORT:V09.00.01 via conventional path 
     import done in WE8ISO8859P15 character set and UTF8 NCHAR character set 
     . importing SCOTT's objects into SCOTT 
     . . importing table                        "T_EXP"       1024 rows imported 
     Import terminated successfully without warnings. 
 
     SQL> select TABLE_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, 
       2         AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN 
       3  from user_tables where table_name='T_EXP'; 
 
     TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE 
     ------------------------------ ---------- ---------- ------------ ---------- 
      CHAIN_CNT AVG_ROW_LEN 
     ---------- ----------- 
     T_EXP                                1024         61            0          0 
              0         113 
 
     If you reanalyze the table, you get different statistics from those  
     imported back. 
 
     SQL> analyze table t_exp compute statistics; 
     Table analyzed. 
 
     SQL> select TABLE_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, 
       2         AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN 
       3  from user_tables where table_name='T_EXP'; 
 
     TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE 
     ------------------------------ ---------- ---------- ------------ ---------- 
      CHAIN_CNT AVG_ROW_LEN 
     ---------- ----------- 
     T_EXP                                1024         45            3       1379 
              0         107 
 
 
2. If the import STATISTICS is set to RECALCULATE, the statistics would be  
   equivalent to the last previous ones: 
 
     SQL> select TABLE_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, 
       2         AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN 
       3  from user_tables where table_name='T_EXP'; 
 
     TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE 
     ------------------------------ ---------- ---------- ------------ ---------- 
      CHAIN_CNT AVG_ROW_LEN 
     ---------- ----------- 
     T_EXP                                1024         61            3       1170 
           1008         113 
 
     $ exp scott/tiger tables=t_exp statistics=compute 
 
     Export: Release 9.0.1.1.0 - Production on Thu Oct 4 15:49:36 2001 
     (c) Copyright 2001 Oracle Corporation.  All rights reserved. 
 
     Connected to: Oracle9i Enterprise Edition Release 9.0.1.1.0 - 64bit Production 
     With the Partitioning option 
     JServer Release 9.0.1.0.0 - Production 
     Export done in WE8ISO8859P15 character set and UTF8 NCHAR character set 
 
     About to export specified tables via Conventional Path ... 
     . . exporting table                          T_EXP       1024 rows exported 
     Export terminated successfully without warnings. 
 
     SQL> drop table t_exp; 
     Table dropped. 
 
     $imp scott/tiger full=y statistics=recalculate 
      
     Import: Release 9.0.1.1.0 - Production on Thu Oct 4 15:49:54 2001 
     (c) Copyright 2001 Oracle Corporation.  All rights reserved. 
 
     Connected to: Oracle9i Enterprise Edition Release 9.0.1.1.0 - 64bit Production 
     With the Partitioning option 
     JServer Release 9.0.1.0.0 - Production 
 
     Export file created by EXPORT:V09.00.01 via conventional path 
     import done in WE8ISO8859P15 character set and UTF8 NCHAR character set 
     . importing SCOTT's objects into SCOTT 
     . . importing table                        "T_EXP"       1024 rows imported 
     Import terminated successfully without warnings. 
 
     SQL> select TABLE_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, 
       2         AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN 
       3  from user_tables where table_name='T_EXP'; 
 
     TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE 
     ------------------------------ ---------- ---------- ------------ ---------- 
      CHAIN_CNT AVG_ROW_LEN 
     ---------- ----------- 
     T_EXP                                1024         45            3       1379 
              0         107