学习笔记:分析Oracle表move或cast两个操作的时长/锁/数据影响对比

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的一些后续步骤在其中，所以通过这个验证和上一篇试验(案例:Oracle数据库table表移动操作MOVE和CAST的比较redo和undo数据量)，基本上可以大胆操作move操作的本质就是全表扫描+append插入数据，操作过程中产生的redo大小几乎和表本身大小(872415232)相等,这个证明,move和cast都是以logging模式运行(数据库本身是非force logging模式)
 

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

最权威、专业的Oracle案例资源汇总之学习笔记:分析Oracle表move或cast两个操作的时长/锁/数据影响对比

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

Oracle研究中心

关键词：

Oracle表move或cast两个操作的时长/锁/数据影响对比

table进行move与cast操作步骤比较