sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle迁移表、列、索引的统计信息到其它表的案例

时间:2016-10-20 19:17   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 运维DBA反映需求Oracle数据表中数据在分存储的时候会遇到统计信息不一致问题导致运行缓慢.通过案例来实例迁移表、列、索引的统计信息到其它表。
这边环境有很多月表.都是之前很早就创建好的.每到出帐的时候.就会因为没有统计信息导致业务运行慢.重新进行表分析后.正常.为了预防再次出现.考虑将上个月的月表的统计信息手动迁移到这个月。环境中是按地市来创建的schema.所以同一个用户下面会存在相同的表。下面是在自己的测试环境中测试:

os:win7 db:11.2.0.3 schema:htz,scott table_name:dbms_test,dbms_test1;

htz.scott.下面分别存在dbms_test,dbms_text1两张表.dbms_text表上面有统计信息.需要把dbms_text上面的统计信息迁移到dbms_text1上面。

1.创建测试表


1.1 创建测试表
CREATE TABLE SCOTT.DBMS_TEST AS SELECT * FROM DBA_OBJECTS;
CREATE TABLE SCOTT.DBMS_TEST1 AS SELECT * FROM DBA_OBJECTS;
CREATE TABLE HTZ.DBMS_TEST AS SELECT * FROM DBA_OBJECTS;
CREATE TABLE HTZ.DBMS_TEST1 AS SELECT * FROM DBA_OBJECTS;
1.2 创建索引
CREATE INDEX SCOTT.IND_DBMS_TEST_OWNER ON SCOTT.DBMS_TEST(OWNER);
CREATE INDEX HTZ.IND_DBMS_TEST_OWNER ON SCOTT.DBMS_TEST(OWNER);
CREATE INDEX SCOTT.IND_DBMS_TEST1_OWNER ON SCOTT.DBMS_TEST1(OWNER);
CREATE INDEX HTZ.IND_DBMS_TEST1_OWNER ON SCOTT.DBMS_TEST1(OWNER);
1.3 收集dbms_text表的统计信息
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'DBMS_TEST',cascade=>true);
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'DBMS_TEST',cascade=>true);

2.创建STAT_TABLE_MAPPING表


用于存放对象mapping关系的

CREATE TABLE SYSTEM.STAT_TABLE_MAPPING
(
  OWNER   VARCHAR2(30 BYTE),
  TYPE    VARCHAR2(20 BYTE),
  SOURCE  VARCHAR2(100 BYTE),
  TARGET  VARCHAR2(100 BYTE)
)

alter table system.stat_table_mapping add constraint pk_stat_table_mapping primary key(owner,source);

3.插入测试表的对应关系


insert into SYSTEM.stat_table_mapping values('scott','t','dbms_test','dbms_test1');
insert into SYSTEM.stat_table_mapping values('htz','t','dbms_test','dbms_test1');
insert into SYSTEM.stat_table_mapping values('scott','i','ind_dbms_test_owner','ind_dbms_test1_owner');
insert into SYSTEM.stat_table_mapping values('htz','i','ind_dbms_test_owner','ind_dbms_test1_owner');

4.创建stat_table用于存放统计信息


exec DBMS_STATS.CREATE_STAT_TABLE(ownname=>'SYSTEM', STATTAB=>'DBMS_STAT_TABLE');

5.创建存储过程.用于迁移统计信息


/* Formatted on 2013/9/26 22:05:50 (QP5 v5.240.12305.39476) */
CREATE OR REPLACE PROCEDURE import_dbms_stats
AS
   v_source     VARCHAR2 (30);
   v_target     VARCHAR2 (30);
   v_username   VARCHAR2 (30);
BEGIN
   FOR cuser IN (SELECT DISTINCT owner
                   FROM SYSTEM.stat_table_mapping
                  WHERE UPPER (TYPE) = 'T')
   LOOP
      v_username := UPPER (cuser.owner);
      DBMS_OUTPUT.put_line ('v_username:' || v_username);

      FOR ctable
         IN (SELECT source, target
               FROM SYSTEM.stat_table_mapping
              WHERE UPPER (TYPE) = 'T' AND UPPER (owner) = v_username)
      LOOP
         v_source := UPPER (ctable.source);
         v_target := UPPER (ctable.target);

         DBMS_OUTPUT.put_line ('v_source ' || v_source);
         DBMS_OUTPUT.put_line ('v_target ' || v_target);

         EXECUTE IMMEDIATE 'truncate table system.dbms_stat_table';

         DBMS_STATS.export_table_stats (ownname   => v_username,
                                        tabname   => v_source,
                                        stattab   => 'DBMS_STAT_TABLE',
                                        cascade   => TRUE,
                                        statown   => 'system');
         DBMS_OUTPUT.put_line ('EXPORT_TABLE_STATS ' || v_source);

         UPDATE SYSTEM.DBMS_STAT_TABLE a
            SET a.c1 =
                   (SELECT UPPER (b.target)
                      FROM SYSTEM.stat_table_mapping b
                     WHERE     a.c1 = UPPER (b.source)
                           AND a.c5 = UPPER (b.owner)
                           AND a.c5 = v_username);

         COMMIT;
         DBMS_OUTPUT.put_line (
            'update dbms_stat_table :' || v_source || ' success');
         DBMS_STATS.import_table_stats (ownname   => v_username,
                                        tabname   => v_target,
                                        stattab   => 'DBMS_STAT_TABLE',
                                        statown   => 'system');
         DBMS_OUTPUT.put_line (
            'import dbms_stat_table :' || v_target || ' success');
      END LOOP;
   END LOOP;
END;

6.测试迁移过程


6.1 手动删除表.列.索引相关的统计信息
SQL> EXEC DBMS_STATS.DELETE_TABLE_STATS(OWNNAME=>'SCOTT', TABNAME=>'DBMS_TEST1',  CASCADE_PARTS=>true, CASCADE_COLUMNS=>true, CASCADE_INDEXES=>true);
SQL> EXEC DBMS_STATS.DELETE_TABLE_STATS(OWNNAME=>'HTZ', TABNAME=>'DBMS_TEST1',  CASCADE_PARTS=>true, CASCADE_COLUMNS=>true, CASCADE_INDEXES=>true);
确认相当的统计信息已经删除
SQL> SELECT owner,
  2         table_name,
  3         TO_CHAR (a.last_analyzed, 'yyyy-mm-dd hh24:mi:ss') last_analyzed
  4    FROM dba_tables a
  5   WHERE table_name = 'DBMS_TEST1' OR table_name = 'DBMS_TEST';

SCOTT                          DBMS_TEST                      2013-09-26 21:28:36
SCOTT                          DBMS_TEST1
HTZ                            DBMS_TEST                      2013-09-26 21:22:00
HTZ                            DBMS_TEST1
SQL> SELECT owner,
  2         index_name,
  3         TO_CHAR (a.last_analyzed, 'yyyy-mm-dd hh24:mi:ss') last_analyzed
  4    FROM dba_indexes a
  5   WHERE table_name = 'DBMS_TEST1' OR table_name = 'DBMS_TEST';

HTZ                            IND_DBMS_TEST_OWNER            2013-09-26 21:22:00
HTZ                            IND_DBMS_TEST1_OWNER
SCOTT                          IND_DBMS_TEST_OWNER            2013-09-26 21:28:36
SCOTT                          IND_DBMS_TEST1_OWNER
SQL>           

6.2 执行过程迁移
SQL> exec import_dbms_stats;
v_username:HTZ
v_source DBMS_TEST
v_target DBMS_TEST1
EXPORT_TABLE_STATS DBMS_TEST
update dbms_stat_table :DBMS_TEST success
import dbms_stat_table :DBMS_TEST1 success
v_username:SCOTT
v_source DBMS_TEST
v_target DBMS_TEST1
EXPORT_TABLE_STATS DBMS_TEST
update dbms_stat_table :DBMS_TEST success
import dbms_stat_table :DBMS_TEST1 success

6.3 查看迁移是否成功
SQL> set lines 200
SQL> SELECT owner,
  2         table_name,
  3         TO_CHAR (a.last_analyzed, 'yyyy-mm-dd hh24:mi:ss') last_analyzed
  4    FROM dba_tables a
  5   WHERE table_name = 'DBMS_TEST1' OR table_name = 'DBMS_TEST';

OWNER                          TABLE_NAME                     LAST_ANALYZED
------------------------------ ------------------------------ -------------------
SCOTT                          DBMS_TEST                      2013-09-26 21:28:36
SCOTT                          DBMS_TEST1                     2013-09-26 21:28:36
HTZ                            DBMS_TEST                      2013-09-26 21:22:00
HTZ                            DBMS_TEST1                     2013-09-26 21:22:00

SQL> SELECT owner,
  2         index_name,
  3         TO_CHAR (a.last_analyzed, 'yyyy-mm-dd hh24:mi:ss') last_analyzed
  4    FROM dba_indexes a
  5   WHERE table_name = 'DBMS_TEST1' OR table_name = 'DBMS_TEST';

OWNER                          INDEX_NAME                     LAST_ANALYZED
------------------------------ ------------------------------ -------------------
HTZ                            IND_DBMS_TEST_OWNER            2013-09-26 21:22:00
HTZ                            IND_DBMS_TEST1_OWNER           2013-09-26 21:22:00
SCOTT                          IND_DBMS_TEST_OWNER            2013-09-26 21:28:36   
SCOTT                          IND_DBMS_TEST1_OWNER           2013-09-26 21:28:36

这里我们可以发现相当的统计信息已经有了.说明迁移成功。

本文固定链接: http://www.htz.pw/2013/09/26/oracle-%e8%bf%81%e7%a7%bb%e8%a1%a8%e3%80%81%e5%88%97%e3%80%81%e7%b4%a2%e5%bc%95%e7%9a%84%e7%bb%9f%e8%ae%a1%e4%bf%a1%e6%81%af%e5%88%b0%e5%8f%a6%e4%b8%80%e5%bc%a0%e7%bb%93%e6%9e%84%e5%ae%8c%e5%85%a8.html | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle迁移表、列、索引的统计信息到其它表的案例

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1015.html

Oracle研究中心

关键词:

Oracle数据库统计信息的迁移方法

ORACLE迁移表、列、索引的统计信息到另一张结构完全相同的表