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比较(续)
原文地址:http://www.xifenfei.com/2012/01/move%E5%92%8Ccast%E6%AF%94%E8%BE%83.html