Purpose ------- This Bulletin Explains The Behavior of the New Import Parameter Value of Statistics = SAFE. Scope & Application ------------------- This allows to understandtand Which Statistics Are: 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 ............................................................................................................................................................................................................................. Mizer statistics. IMPORT. WHEN Are Statistics Questions ---------------------------------- 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 Questions STATISTISTINTICS *** ****************************************** ******************* 1. The Statistics Are Recalculate at Import Since Theiere Defined As Questions 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_la Ng = 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 Editio n 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 Genereates Safe Statistics *** ******************************* ********************* 1. The Statistics Are Not Recalculated At Import Since They Are Not De De De DEFINED AS Questionable At The End of The Export (this Means Safe Implicitly 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 . Connected to: Oracle9i Enterprise Edition Release 9.0.1.1.0 - 64bit produted with the partitioning OPT ion 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';
