不同库之间互导表的统计信息

最新推荐文章于 2020-09-29 14:37:17 发布

转载最新推荐文章于 2020-09-29 14:37:17 发布 · 139 阅读

本文介绍了一种在Oracle数据库中迁移统计信息的方法，包括创建统计信息表、导出表统计信息、导入并应用到目标表的过程。

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

生产库：

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

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

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

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>

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

转载于:http://blog.itpub.net/15779287/viewspace-690233/