首页 > Linux操作系统 > Linux操作系统 > 不同库之间互导表的统计信息
在实际数据库性能遇到问题的时候,有时我们直接收集统计信息并不能带来好的执行计划,我们需要某一个其它环境能产生好的执行计划的统计信息,下面是处理过程
生产库:
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>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15779287/viewspace-690233/,如需转载,请注明出处,否则将追究法律责任。