当前位置:Oracle研究中心 > 运维DBA >
时间:2016-06-05 21:59 来源:Oracle研究中心 作者:惜分飞 点击: 次
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
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
--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
Powered by Oracle研究中心 OraclePlus Inc.
BY DedeCMS 京ICP备14022373号-5
声明:Oracle研究中心部分文章源自互联网,转载均按原文作者要求转载,如有疑问联系QQ:1026002015,协商未果24小时删除。