月归档：一月 2012

本篇文章是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比较(续)