在实际数据库性能遇到问题的时候，有时我们直接收集统计信息并不能带来好的执行计划，我们需要某一个其它环境能产生好的执行计划的统计信息，下面是处理过程

生产库：

SQL> exec dbms_stats.create_stat_table(ownname => 'LIZHONGYUAN002',stattab => 'CBO_STATS_BAK');

 

PL/SQL procedure successfully completed.

 

SQL> BEGIN

   DBMS_STATS.export_table_stats (ownname      => 'ICSSDATA',

                                  tabname      => 'TEL_ACCOUNT',

                                  stattab      => 'CBO_STATS_BAK',

                                  statown      => 'LIZHONGYUAN002',

                                  CASCADE      => TRUE,

                                  statid       => 'TEL_ACCOUNT_0323'

                                 );

END;

/  2    3    4    5    6    7    8    9   10 

 

PL/SQL procedure successfully completed.

 

SQL> BEGIN

   DBMS_STATS.export_table_stats (ownname      => 'ICSSDATA',

                                  tabname      => 'TEL_ACCOUNT_PRIVILEGE',

                                  stattab      => 'CBO_STATS_BAK',

                                  statown      => 'LIZHONGYUAN002',

                                  CASCADE      => TRUE,

                                  statid       => 'TEL_ACCOUNT_PRIVILEGE_0323'

                                 );

END;

/

  2    3    4    5    6    7    8    9   10 

PL/SQL procedure successfully completed.

 

 

 

g4as7061:dr_icss > vi exp.par

"exp.par" [New file]

userid='/  as sysdba '

file=exp.dmp

tables=lizhongyuan002.CBO_STATS_BAK

log=exp.log

 

~

~

 

~

"exp.par" [New file] 5 lines, 85 characters

g4as7061:dr_icss > exp parfile=exp.par

 

Export: Release 9.2.0.8.0 - Production on Wed Mar 23 09:01:55 2011

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

 

Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options

JServer Release 9.2.0.8.0 - Production

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

 

About to export specified tables via Conventional Path ...

Current user changed to LIZHONGYUAN002

. . exporting table                  CBO_STATS_BAK         29 rows exported

Export terminated successfully without warnings.

 

 

测试库：

 

导入之前表的统计信息为空

 

SQL> @tbinfo TEL_ACCOUNT

 

OWNER                          TABLE_NAME                       NUM_ROWS AVG_ROW_LEN LAST_ANALYZED       TABLESPACE_NAME                  PCT_FREE   PCT_USED

------------------------------ ------------------------------ ---------- ----------- ------------------- ------------------------------ ---------- ----------

ICSSDATA                       TEL_ACCOUNT                                                               ICSSDATA                               10

 

SQL> @tbinfo TEL_ACCOUNT_PRIVILEGE

 

OWNER                          TABLE_NAME                       NUM_ROWS AVG_ROW_LEN LAST_ANALYZED       TABLESPACE_NAME                  PCT_FREE   PCT_USED

------------------------------ ------------------------------ ---------- ----------- ------------------- ------------------------------ ---------- ----------

ICSSDATA                       TEL_ACCOUNT_PRIVILEGE                                                     ICSSDATA                               10

 

g2bh8060:cmsdev > vi imp.par       

"imp.par" [New file]

userid= '/ as sysdba '

file=exp.dmp

full=y

log=imp.log

 

~

"imp.par" [New file] 5 lines, 56 characters

g2bh8060:cmsdev > imp parfile=imp.par

 

Import: Release 9.2.0.8.0 - Production on Wed Mar 23 09:19:11 2011

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

 

Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

With the Partitioning, Oracle Label Security and OLAP options

JServer Release 9.2.0.8.0 - Production

 

Export file created by EXPORT:V09.02.00 via conventional path

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

. importing SYS's objects into SYS

. importing LIZHONGYUAN002's objects into LIZHONGYUAN002

. . importing table                "CBO_STATS_BAK"         29 rows imported

Import terminated successfully without warnings.

 

g2bh8060:cmsdev > sqlplus '/as sysdba'

 

SQL*Plus: Release 9.2.0.8.0 - Production on Wed Mar 23 09:29:53 2011

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

 

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

With the Partitioning, Oracle Label Security and OLAP options

JServer Release 9.2.0.8.0 - Production

 

SQL> exec dbms_stats.import_table_stats(ownname => 'ICSSDATA',tabname => 'TEL_ACCOUNT',stattab => 'CBO_STATS_BAK',statid => 'TEL_ACCOUNT_0323',statown => 'LIZHONGYUAN002',no_invalidate =>false,cascade => TRUE);

 

PL/SQL procedure successfully completed.

 

SQL> exec dbms_stats.import_table_stats(ownname => 'ICSSDATA',tabname => 'TEL_ACCOUNT_PRIVILEGE',stattab => 'CBO_STATS_BAK',statid => 'TEL_ACCOUNT_PRIVILEGE_0323',statown => 'LIZHONGYUAN002',no_invalidate =>false,cascade => TRUE);

 

PL/SQL procedure successfully completed.

 

导入之后表的统计信息：

SQL> @tbinfo TEL_ACCOUNT

 

OWNER                          TABLE_NAME                       NUM_ROWS AVG_ROW_LEN LAST_ANALYZED       TABLESPACE_NAME                  PCT_FREE   PCT_USED

------------------------------ ------------------------------ ---------- ----------- ------------------- ------------------------------ ---------- ----------

ICSSDATA                       TEL_ACCOUNT                       4060983         117 2011-03-19 02:39:09 ICSSDATA                               10

 

SQL> @tbinfo TEL_ACCOUNT_PRIVILEGE

 

OWNER                          TABLE_NAME                       NUM_ROWS AVG_ROW_LEN LAST_ANALYZED       TABLESPACE_NAME                  PCT_FREE   PCT_USED

------------------------------ ------------------------------ ---------- ----------- ------------------- ------------------------------ ---------- ----------

ICSSDATA                       TEL_ACCOUNT_PRIVILEGE             4143456          46 2011-03-19 02:32:24 ICSSDATA                               10

 

SQL>