MOVE和CTAS比較

發表于  2012 年 01 月 10 日  由  惜分飛

聯系：手機[phone]) QQ(107644445)

标題：MOVE和CAST比較

作者：惜分飛©版權所有[未經本人同意,請不得以任何形式轉載,否則有進一步追究法律責任的權利.]

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内容

[[email protected] ~]$ 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内容

[[email protected] ~]$ 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