ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 不同库之间互导表的统计信息

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

原创 Linux操作系统 作者:linger_52102 时间:2011-03-23 10:17:00 0 删除 编辑

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

生产库:

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-10-03

  • 博文量
    50
  • 访问量
    208591