1.展示列属性

[oracle@saas-xunzhi-db1 ~]$ sqlplus testga/testga
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jan 12 16:26:56 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> desc t_xifenfei;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROCESS_INSTANCE_ID                       NOT NULL NUMBER(10)
 STATUS_NEW                                         NUMBER
 PACKAGE_NAME                                       VARCHAR2(50)
SQL> desc wf_proc_inst@oldmoa
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(10)
 NAME                                               VARCHAR2(500)
 PROC_ID                                   NOT NULL NUMBER(10)
 PARE_INST_ID                              NOT NULL NUMBER(10)
 PARE_ACTI_ID                              NOT NULL NUMBER(10)
 INST_CREATOR                                       VARCHAR2(40)
 CREAT_DATE                                         DATE
 STATUS                                             CHAR(1)
 ATT1                                               NUMBER(10)
 ATT2                                               VARCHAR2(255)
 SEQVALUE                                           VARCHAR2(50)

从这里可以看出，原表的status列是char，而目标表的STATUS_NEW列是number类型

2.通过dblink查询列值

SQL> select t.inst_id as ID,
  2         case w.status
  3           when '0' then
  4            4
  5           when '2' then
  6            3
  7           else
  8            1
  9         end as  status_new,
 10         p.name as PACKAGE_NAME
 11    from wf_proc_inst@oldmoa i
 12    left join wf_proc_info_inst@oldmoa t on t.inst_id = i.id
 13    left join wf_proc_type@oldmoa p on t.type_id = p.id
 14    left join wf_proc_inst@oldmoa w on t.inst_id = w.id
 15   where t.inst_id = i.id
 16     and t.proc_id <> 53
 17     and t.is_ok = 1
 18     AND t.inst_id <= 4837
 19     AND t.inst_id>=4735;
        ID STATUS_NEW PACKAGE_NAME
---------- ---------- --------------------------
      4755          3 呈批处理
      4836          3 公文处理

从这里可以看出来，case工作正常，传入的char类型，通过case转换为number类型

3.使用dblink插入数据并查询

SQL> INSERT INTO t_xifenfei
  2  select t.inst_id as ID,
  3         case w.status
  4           when '0' then
  5            4
  6           when '2' then
  7            3
  8           else
  9            1
 10         end as  status_new,
 11         p.name as PACKAGE_NAME
 12    from wf_proc_inst@oldmoa i
 13    left join wf_proc_info_inst@oldmoa t on t.inst_id = i.id
 14    left join wf_proc_type@oldmoa p on t.type_id = p.id
 15    left join wf_proc_inst@oldmoa w on t.inst_id = w.id
 16   where t.inst_id = i.id
 17     and t.proc_id <> 53
 18     and t.is_ok = 1
 19     AND t.inst_id <= 4837
 20     AND t.inst_id>=4735;
2 rows created.
SQL> commit;
Commit complete.
SQL> col package_name for a10
SQL> select * from t_xifenfei;
PROCESS_INSTANCE_ID STATUS_NEW PACKAGE_NA
------------------- ---------- ----------
               4755          1 呈批处理
               4836          1 公文处理

这里出现异常，怀疑case中传入的值，都走到else那边了，怀疑是在使用dblink 的时候，char的类型发生的改变，转为了number，导致case匹配失败，所以走到else。

4.在dblink端创建视图，插入数据

SQL> CREATE VIEW GZZJ.v_xifenfei
  2  AS
  3  select t.inst_id as ID,
  4         case to_char(w.status)
  5           when '0' then
  6            4
  7           when '2' then
  8            3
  9           else
 10            1
 11         end as  status_new,
 12         p.name as PACKAGE_NAME
 13    from GZZJ.wf_proc_inst i
 14    left join GZZJ.wf_proc_info_inst t on t.inst_id = i.id
 15    left join GZZJ.wf_proc_type p on t.type_id = p.id
 16    left join GZZJ.wf_proc_inst w on t.inst_id = w.id
 17   where t.inst_id = i.id
 18     and t.proc_id <> 53
 19     and t.is_ok = 1
 20     AND t.inst_id <= 4837
 21     AND t.inst_id>=4735;
View created.
SQL> insert into t_xifenfei
  2  select * from v_xifenfei@oldmoa;
2 rows created.
SQL> commit;
Commit complete.
SQL> select * from t_xifenfei;
PROCESS_INSTANCE_ID STATUS_NEW PACKAGE_NA
------------------- ---------- ----------
               4755          3 呈批处理
               4836          3 公文处理

通过远程视图，接触数据类型发生改变异常(怀疑是dblink 在insert时候，char数据类型变成了number，但是未得到官方或者权威的确认)

1.试验前提

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> select FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,
   2 SUPPLEMENTAL_LOG_DATA_MIN from v$database;
FOR SUP SUP SUPPLEME
--- --- --- --------
NO  NO  NO  NO
SQL> conn / as sysdba
Connected.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /opt/oracle/oradata/archivelog/chf
Oldest online log sequence     1879
Next log sequence to archive   1881
Current log sequence           1881
SQL> select count(*) from t_xifenfei_move;
  COUNT(*)
----------
   7432085
SQL> select bytes/1024/1024 from user_segments where segment_name='T_XIFENFEI_MOVE';
BYTES/1024/1024
---------------
            832

从上面信息可以看到数据库处于归档模式，强制日志和辅助日志为开启，试验测试表t_xifenfei_move有7432085条记录，占用硬盘空间832M

2.常规CAST

SQL>  alter system flush buffer_cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> SET TIMING ON
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                            100028
undo change vector size                                               16172
Elapsed: 00:00:00.06
SQL> create table chf.t_xifenfei_move_CAST tablespace users
  2  as
  3  select * from chf.t_xifenfei_move;
Table created.
Elapsed: 00:01:58.10
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         873150548
undo change vector size                                              131384
Elapsed: 00:00:00.01
SQL> select 873150548-100028 "redo size" from dual;
 redo size
----------
 873050520
SQL> select 131384-16172 "undo size" from dual;
 undo size
----------
    115212

通过这个可以得出结论，产生redo为873050520，undo为115212

3.普通INSERT

SQL>  alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.06
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.01
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               732
undo change vector size                                                 136
Elapsed: 00:00:00.18
SQL> CREATE TABLE t_xifenfei_move_INSERT
  2  AS
  3  SELECT * FROM T_XIFENFEI_MOVE WHERE 1=0;
Table created.
Elapsed: 00:00:00.32
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                             22712
undo change vector size                                                6512
Elapsed: 00:00:00.02
SQL> INSERT INTO t_xifenfei_move_INSERT
  2  SELECT * FROM T_XIFENFEI_MOVE;
7432085 rows created.
Elapsed: 00:01:59.47
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         862261580
undo change vector size                                            27980508
--redo
SQL> select 22712-732 "create redo size" from dual;
create redo size
----------------
           21980
SQL> select 862261580-22712 "insert redo size" from dual;
insert redo size
----------------
       862238868
--undo
SQL> select 6512-136 "create undo size" from dual;
create undo size
----------------
            6376
SQL> select 27980508-6512 "insert undo size" from dual;
insert undo size
----------------
        27973996

通过这个可以得出CREATE TABLE 过程中产生redo:21980,undo:6376,而INSERT 过程中产生的redo:862238868,undo:27973996,整个过程总的产生redo:862260848(862238868+21980),undo:27980372(27973996+6376)

4.INSERT+APPEND

SQL>  alter system flush buffer_cache;
System altered.
Elapsed: 00:00:25.19
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.04
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               732
undo change vector size                                                 136
Elapsed: 00:00:00.05
SQL> create table chf.t_xifenfei_move_INSERT_A tablespace users
  2  as
  3  select * from chf.t_xifenfei_move where 1=0;
Table created.
Elapsed: 00:00:00.18
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                             21892
undo change vector size                                                6308
Elapsed: 00:00:00.00
SQL> INSERT /*+ append */INTO t_xifenfei_move_INSERT_A
  2  SELECT * FROM T_XIFENFEI_MOVE;
7432085 rows created.
Elapsed: 00:02:26.37
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         872792032
undo change vector size                                               87764
--redo
SQL> select 21892-732 "create redo size" from dual;
create redo size
----------------
           21160
SQL> select 872792032-21892 "insert redo size" from dual;
insert redo size
----------------
       872770140
--undo
SQL> select 6308-136 "create undo size" from dual;
create undo size
----------------
            6172
SQL> select 87764-6308 "insert undo size" from dual;
insert undo size
----------------
           81456

这个过程可以得到结论,create 表的过程产生的redo:21160,undo:6172;insert 表的过程redo:872770140,undo:81456;整个过程产生的redo:872791300(21160+872770140),undo:87628(6172+81456)

5.INSERT+NOLOGGING

SQL>  alter system flush buffer_cache;
System altered.
Elapsed: 00:00:02.21
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.06
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               780
undo change vector size                                                 136
Elapsed: 00:00:00.06
SQL> create table chf.t_xifenfei_move_INSERT_N tablespace users
  2  as
  3  select * from chf.t_xifenfei_move where 1=0;
Table created.
Elapsed: 00:00:00.22
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                             22056
undo change vector size                                                6308
Elapsed: 00:00:00.00
SQL> INSERT /*+ NOLOGGING */INTO t_xifenfei_move_INSERT_N
  2  SELECT * FROM T_XIFENFEI_MOVE;
7432085 rows created.
Elapsed: 00:02:30.33
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         862123984
undo change vector size                                            27982876
--redo
SQL> select 22056-780 "create redo size" from dual;
create redo size
----------------
           21276
SQL> select 862123984-22056 "insert redo size" from dual;
insert redo size
----------------
       862101928
--undo
SQL> select 6308-136 "create undo size" from dual;
create undo size
----------------
            6172
SQL> select 27982876-6308 "insert undo size" from dual;
insert undo size
----------------
        27976568

这个试验过程得出,create table得到redo:21276,undo:6172,insert table 得到redo:862101928,undo:27976568,整个过程redo:862123204,undo:27982740(27976568+6172)

6.INSERT+NOLOGGING(TABLE)

SQL>  alter system flush buffer_cache;
System altered.
Elapsed: 00:00:23.68
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.04
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               800
undo change vector size                                                 136
Elapsed: 00:00:00.18
SQL> create table chf.t_xifenfei_move_INSERT_N_new nologging tablespace users
  2  as
  3  select * from chf.t_xifenfei_move where 1=0;
Table created.
Elapsed: 00:00:00.71
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                             23296
undo change vector size                                                6756
Elapsed: 00:00:00.00
SQL> INSERT INTO t_xifenfei_move_INSERT_N_new
  2  SELECT * FROM T_XIFENFEI_MOVE;
7432085 rows created.
Elapsed: 00:02:37.51
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         862031304
undo change vector size                                            27982776
SQL> select 23296-800 "create redo size" from dual;
create redo size
----------------
           22496
SQL> select 862031304-23296 "insert redo size" from dual;
insert redo size
----------------
       862008008
SQL> select 6756-136 "create undo size" from dual;
create undo size
----------------
            6620
SQL> select 27982776-6756 "insert undo size" from dual;
insert undo size
----------------
        27976020

通过该试验得出,create table 产生redo:22496,undo:6620;insert into 产生redo:862008008,undo:27976020;整个过程产生redo:862030504(22496+862008008),undo:27982640(6620+27976020)

7.APPEND+NOLOGGING(TABLE)

SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:23.59
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.05
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               780
undo change vector size                                                 136
Elapsed: 00:00:00.04
SQL> create table chf.t_xifenfei_move_INSERT_NA nologging tablespace users
  2  as
  3  select * from chf.t_xifenfei_move where 1=0;
Table created.
Elapsed: 00:00:00.42
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                             22000
undo change vector size                                                6308
Elapsed: 00:00:00.00
SQL> INSERT /*+APPEND */ INTO t_xifenfei_move_INSERT_NA
  2  SELECT * FROM T_XIFENFEI_MOVE;
7432085 rows created.
Elapsed: 00:01:08.92
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                            602352
undo change vector size                                               82756
--redo
SQL> select 22000-780 "create redo size" from dual;
create redo size
----------------
           21220
SQL> select 602352-22000 "insert redo size" from dual;
insert redo size
----------------
          580352
--undo
SQL> select 6308-136 "create undo size" from dual;
create undo size
----------------
            6172
SQL> select 82756-6308 "insert undo size" from dual;
insert undo size
----------------
           76448

在这个试验中，create table产生redo:21220,undo:6172,insert into产生redo:580352,undo:76448;整个过程产生的redo:601572(
21220+580352),undo:82620(6172+76448)

8.CAST+NOLOGGING(TABLE)

SQL>  alter system flush buffer_cache;
System altered.
Elapsed: 00:00:03.35
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.04
SQL>
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               732
undo change vector size                                                 136
Elapsed: 00:00:00.05
SQL> create table chf.t_xifenfei_move_cast_N nologging tablespace users
  2  as
  3  select * from chf.t_xifenfei_move;
Table created.
Elapsed: 00:00:56.41
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                            769848
undo change vector size                                              124944
SQL> select 769848-732 "redo size" from dual;
 redo size
----------
    769116
SQL> select 124944-136 "undo  size" from dual;
undo  size
----------
    124808

这个试验产生的redo:769116,undo:124808

9.通过试验得出结论
1)sql hint中的nologgging无效
2)普通的cast(不含hint)，其本质是append，无nologgging
3)nologgging(表级别)可以使得cast效率较高
4)nologgging(表级别)+append(hint)可以使得insert效率较高

本篇文章是MOVE和CAST比较的续篇，主要是对于第一篇中没有涉及到的redo和undo的情况加以叙述
1.查询move产生redo和undo量

SQL> alter system flush buffer_cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                          VALUE
---------------------------------------- ----------
redo size                                       844
undo change vector size                         136
SQL> ALTER TABLE CHF.T_XIFENFEI_MOVE MOVE TABLESPACE TEST_OCP;
Table altered.
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                          VALUE
---------------------------------------- ----------
redo size                                 873074928
undo change vector size                      110748
--产生redo
SQL> select 873074928-844 "redo size" from dual;
 redo size
----------
 873074084
--产生undo
SQL> select 110748-136 "undo size" from dual;
 undo size
----------
    110612

2.查询cast产生redo和undo 大小

SQL> alter system flush buffer_cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
and lower(a.name) in
  4    5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               776
undo change vector size                                                 136
SQL> create table chf.t_xifenfei_move_new tablespace users
  2  as
  3  select * from chf.t_xifenfei_move;
Table created.
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         873017580
undo change vector size                                              115340
--产生redo
SQL> select 873017580-776 "redo size" from dual;
 redo size
----------
 873016804
--产生undo
SQL> select 115340-136 "undo size" from dual;
 undo size
----------
    115204

3.两次实验比较

--redo(分母使用cast操作产生redo)
SQL> select 873074084-873016804 "redo" from dual;
      redo
----------
     57280
SQL> select 57280/873074084 from dual;
57280/873074084
---------------
     .000065607
--undo(分母使用cast操作产生undo)
SQL> select 110612-115204 undo from dual;
         undo
-------------
        -4592
SQL> select 4592/115204 from dual;
4592/115204
-----------
 .039859727

通过这两个的比较可以知道move操作产生的redo多了万分之七不到，undo少了百分之四，这些也是在实验允许的误差范围内，再说move操作还包括了cast的一些后续步骤在其中，所以通过这个验证和上一篇试验(MOVE和CAST比较)，基本上可以大胆操作move操作的本质就是全表扫描+append插入数据，操作过程中产生的redo大小几乎和表本身大小(872415232)相等,这个证明,move和cast都是以logging模式运行(数据库本身是非force logging模式)

1.创建模拟表

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> create table chf.t_xifenfei_move
  2  as
  3  select * from dba_objects;
Table created.
SQL> select count(*) from chf.t_xifenfei_move;
  COUNT(*)
----------
     73585
SQL> create table chf.t_xifenfei_move
  2  as
  3  select * from dba_objects;
Table created.
SQL> select count(*) from chf.t_xifenfei_move;
  COUNT(*)
----------
     73585
SQL> DECLARE
  2     i NUMBER;
  3      BEGIN
  4        FOR i IN 1..100 LOOP
  5          INSERT INTO chf.t_xifenfei_move
  6          select * from dba_objects;
  7          END LOOP;
  8          COMMIT;
  9      END;
 10      /
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('CHF','T_XIFENFEI_MOVE');
PL/SQL procedure successfully completed.
SQL> select bytes from dba_segments where segment_name='T_XIFENFEI_MOVE';
     BYTES
----------
 872415232

2.测试move
2.1)执行move操作，记录时间

SQL> alter system flush buffer_cache;
System altered.
SQL> SET TIMING ON;
SQL> alter session set events
  2  '10046 trace name context forever,level 1';
Session altered.
Elapsed: 00:00:00.00
SQL> ALTER TABLE CHF.T_XIFENFEI_MOVE MOVE TABLESPACE USERS;
Table altered.
Elapsed: 00:02:11.77
SQL> alter session set events
  2  '10046 trace name context off';
Session altered.
Elapsed: 00:00:00.04
SQL> select d.value||'/'||lower(rtrim(i.instance,chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name from
  2  (select p.spid from v$mystat m, v$session s,v$process p where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr ) p,
  3  (select t.instance from v$thread t,v$parameter v where v.name = 'thread' and(v.value = 0 or t.thread# = to_number(v.value))) i,
  4  (select value from v$parameter where name = 'user_dump_dest') d;
TRACE_FILE_NAME
--------------------------------------------------------------------------------
/opt/oracle/diag/rdbms/chf/chf/trace/chf_ora_4765.trc

从这里可以看出，move操作执行了00:02:11.77

2.2)查看trace内容

[oracle@node1 ~]$ tkprof  /opt/oracle/diag/rdbms/chf/chf/trace/chf_ora_4765.trc /tmp/xifenfei_move.txt
TKPROF: Release 11.2.0.3.0 - Development on Tue Jan 10 10:57:59 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
********************************************************************************
SQL ID: c1yk5pv0v1wg1 Plan Hash: 2931676921
ALTER TABLE CHF.T_XIFENFEI_MOVE MOVE TABLESPACE USERS
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.05          7          8          0           0
Execute      1     11.29     131.23     105584     106275     115654     7432085
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     11.30     131.29     105591     106283     115654     7432085
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  LOAD AS SELECT  (cr=117799 pr=105602 pw=105585 time=131351005 us)
   7432085    7432085    7432085   TABLE ACCESS FULL T_XIFENFEI_MOVE (cr=105591 pr=105586 pw=0 time=4735560 us cost=23453 size=720912245 card=7432085)
********************************************************************************

从这里可以看出执行move，其实本质是全表扫描表，然后append方式插入数据，而不是真的数据块拷贝

3.测试CAST
3.1).CAST插入数据过程

SQL> alter system flush buffer_cache;
System altered.
SQL> SET TIMING ON;
SQL> alter session set events
2 ‘10046 trace name context forever,level 1’;
Session altered.
Elapsed: 00:00:00.01
SQL> create table chf.t_xifenfei_move_new tablespace test_ocp
2 as
3 select * from chf.t_xifenfei_move;
Table created.
Elapsed: 00:01:59.22
SQL> alter session set events
2 ‘10046 trace name context off’;
Session altered.
Elapsed: 00:00:00.00
SQL> select d.value||’/’||lower(rtrim(i.instance,chr(0)))||’_ora_’||p.spid||’.trc’ trace_file_name from
2 (select p.spid from v$mystat m, v$session s,v$process p where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr ) p,
3 (select t.instance from v$thread t,v$parameter v where v.name = ‘thread’ and(v.value = 0 or t.thread# = to_number(v.value))) i,
4 (select value from v$parameter where name = ‘user_dump_dest’) d;
TRACE_FILE_NAME
——————————————————————————–
/opt/oracle/diag/rdbms/chf/chf/trace/chf_ora_5121.trc

从这里看出cast操作用时:00:01:59.22，比move稍微少，但是cast要实现move完全的功能，还需要表重命名，表授权，编译无效对象等。

3.2)查看trace内容

[oracle@node1 ~]$ tkprof  /opt/oracle/diag/rdbms/chf/chf/trace/chf_ora_5121.trc /tmp/xifenfei_create.txt
TKPROF: Release 11.2.0.3.0 - Development on Tue Jan 10 11:08:19 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
********************************************************************************
create table chf.t_xifenfei_move_new tablespace test_ocp
as
select * from chf.t_xifenfei_move
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.02          1          4          0           0
Execute      1      9.85     118.37     105587     106097     112387     7432085
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      9.85     118.40     105588     106101     112387     7432085
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  LOAD AS SELECT  (cr=106631 pr=105592 pw=105585 time=118338607 us)
   7432085    7432085    7432085   TABLE ACCESS FULL T_XIFENFEI_MOVE (cr=105591 pr=105586 pw=0 time=2935008 us cost=23453 size=720912245 card=7432085)
********************************************************************************

通过这个可以看出，CAST其实本质也是全表扫描，然后append方式插入数据

4.比较move和cast
4.1)通过比较执行时间，cast稍微少，但是还有后续操作需要时间
4.2)通过比较执行计划，两者是一样的
4.3)move操作在整个过程中都会锁表,而cast不会锁住原表(select+where可以减少停业务时间)
4.4)move操作会一次性处理好权限,plsql/view等有效,而cast在rename之后，相关对象可能需要重新编译，重新授权等操作
4.5)cast操作index需要新建(create),而move操作index需要重建(rebuild)
4.6)cast完成后，需要对表重命名，删除原表操作操作，而这个操作move不用

5.选择使用谁
5.1)如果停业务时间够长，建议使用move操作
5.2)如果停业务时间不能太长，可以使用cast+where实现
5.3)如果数据库版本>=10g,且表空间使用local管理，那么可以考虑在不停业务的情况下使用shrink实现类此功能

至于MOVE和CAST在执行过程中，关于产生的redo和undo的比较，请见下篇:MOVE和CAST比较(续)

1.数据库版本

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

2.查询DBA_DATA_FILES视图

SQL> col tablespace_name for a15
SQL>  SELECT TABLESPACE_NAME,
  2          SUM(MAXBYTES) / 1024
  5     FROM DBA_DATA_FILES
  6    GROUP BY TABLESPACE_NAME
  7    UNION
  8     SELECT TABLESPACE_NAME,
  9          SUM(MAXBYTES) / 1024
 12     FROM DBA_TEMP_FILES
 13    GROUP BY TABLESPACE_NAME;
TABLESPACE_NAME SUM(MAXBYTES)/1024
--------------- ------------------
EXAMPLE                   33554416
OGG                        5242880
SYSAUX                    33554416
SYSTEM                    33554416
TEMP                      33554416
TS_INDEX_BASE             15728640
TS_PUB_BASE               15728640
UNDOTBS1                  33554416
USERS                     33554416
9 rows selected.

3.查询DBA_HIST_TBSPC_SPACE_USAGE视图

SQL> col name for a15
SQL> SELECT NAME, TABLESPACE_MAXSIZE
  2    FROM DBA_HIST_TBSPC_SPACE_USAGE A, V$TABLESPACE B
  3   WHERE A.TABLESPACE_ID = B.TS#
  4     AND SNAP_ID = (select MAX(snap_id) FROM DBA_HIST_TBSPC_SPACE_USAGE)
  5   ORDER BY NAME ;
NAME            TABLESPACE_MAXSIZE
--------------- ------------------
EXAMPLE                    4194302
OGG                         655360
SYSAUX                     4194302
SYSTEM                     4194302
TEMP                       4194302
TS_INDEX_BASE              1966080
TS_PUB_BASE                1966080
UNDOTBS1                   8388604
USERS                      4194302
9 rows selected.

观察者两个视图的运行结果，DBA_HIST_TBSPC_SPACE_USAGE视图收集到的统计大小和实际大小都存在一定的误差，但是UNDO表空间出入太明显(UNDOTBS1),特别是最大值和当前值，几乎是真实大小的两倍

4.排除原因
4.1)收集信息是否是最新

SQL> select MAX(rtime) FROM DBA_HIST_TBSPC_SPACE_USAGE;
MAX(RTIME)
-------------------------
01/09/2012 15:00:50

4.2)statistics_level是否被设置为basic

SQL> show parameter statistics_level;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL

通过这两个查询证明，收集信息和statistics_level都是符合要求，那么为什么undo空间的空间信息还是正常的两倍呢？

5.怀疑bug，查询mos

6.查询11.2.0.3中DBA_HIST_TBSPC_SPACE_USAGE是否正常

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> col name for a15
SQL> SELECT NAME, TABLESPACE_MAXSIZE
  2    FROM DBA_HIST_TBSPC_SPACE_USAGE A, V$TABLESPACE B
  3   WHERE A.TABLESPACE_ID = B.TS#
  4     AND SNAP_ID = (select MAX(snap_id) FROM DBA_HIST_TBSPC_SPACE_USAGE)
  5   ORDER BY NAME ;
NAME            TABLESPACE_MAXSIZE
--------------- ------------------
DRSYS_1                    4194302
EXAMPLE                    4194302
ODU                        8139262
SYSAUX                     4194302
SYSTEM                     4194302
TEMP                       4194302
TEST_OCP                   4194302
UNDOTBS01                  3938560
USERS                      4194302
9 rows selected.
SQL> col tablespace_name for a15
SQL>  SELECT TABLESPACE_NAME,
  2          SUM(MAXBYTES) / 1024  3
  5     FROM DBA_DATA_FILES
  6    GROUP BY TABLESPACE_NAME
  7    UNION
  8     SELECT TABLESPACE_NAME,
  9          SUM(MAXBYTES) / 1024
 12     FROM DBA_TEMP_FILES
 13    GROUP BY TABLESPACE_NAME;
TABLESPACE_NAME SUM(MAXBYTES)/1024
--------------- ------------------
DRSYS_1                   33554416
EXAMPLE                   33554416
ODU                       65114096
SYSAUX                    33554416
SYSTEM                    33554416
TEMP                      33554416
TEST_OCP                  33554416
UNDOTBS01                 31457280
USERS                     33554416
9 rows selected.

通过对比，发现基本误差不大，确定在该版本，bug7578292已经被修复