Oracle sqlplus的Copy比对CTAS以及普通insert测试
版本：11.2.0.4.0
随意创建一张测试表，信息如下：
11:16:45 ZKM@xxxxxxx1(479)> select segment_name,segment_type,bytes/1024/1024 mb from user_segments where segment_name='TEST'; SEGMENT_NAME SEGMENT_TYPE MB --------------- --------------- ---------- TEST TABLE 4160 Elapsed: 00:00:00.01
首先从10046看copy本质上是做了什么。
使用用户zkm登录后，会话sid=321开启10046后，使用copy拷贝ZKM.TEST的数据为另外一张新表ZKM.COPY，查看10046的trc文件。
11:20:20 ZKM@xxxxxxx1(321)> select value from v$diag_info where name like '%De%'; VALUE -------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/xxxxxxx/xxxxxxx1/trace/xxxxxxx1_ora_26860.trc Elapsed: 00:00:00.01 11:20:27 ZKM@xxxxxxx1(321)> alter session set events '10046 trace name context forever,level 12'; Session altered. Elapsed: 00:00:00.00 11:20:41 ZKM@xxxxxxx1(321)> !echo '' > /u01/app/oracle/diag/rdbms/xxxxxxx/xxxxxxx1/trace/xxxxxxx1_ora_26860.trc 11:20:51 ZKM@xxxxxxx1(321)> copy from zkm/oracle@192.168.1.224/xxxxxxx create copy using select * from test; Array fetch/bind size is 15. (arraysize is 15) Will commit when done. (copycommit is 0) Maximum long size is 9999. (long is 9999) Table COPY created. 1048576 rows selected from zkm@192.168.1.224/xxxxxxx. 1048576 rows inserted into COPY. 1048576 rows committed into COPY at DEFAULT HOST connection. 11:25:31 ZKM@xxxxxxx1(321)> alter session set events '10046 trace name context off'; Session altered. Elapsed: 00:00:00.00
查看/u01/app/oracle/diag/rdbms/xxxxxxx/xxxxxxx1/trace/xxxxxxx1_ora_26860.trc文件可以看到本质上的copy就是先创建表，然后insert数据。
PARSING IN CURSOR #140737295807136 len=40 dep=0 uid=58 oct=1 lid=58 tim=1643253675363545 hv=543733619 ad='98333d5c0' sqlid='7bqp838h6jdvm' CREATE TABLE COPY("NAME" END OF STMT ...... PARSING IN CURSOR #140737294793408 len=36 dep=0 uid=58 oct=2 lid=58 tim=1643253675380431 hv=1962655248 ad='9832eef60' sqlid='9yvpqwjugrghh' INSERT INTO COPY("NAME" )VALUES(:v1) END OF STMT
网上有资料还说insert into ... select ...相比CTAS和copy最慢，而且生成undo和redo最多...（看这里）
不知道是哪里来的数据。。
既然如此，还是从redo层面比对下3种方式产生的redo的大小。
准备工作，需要先创建一张视图方便查询前后的会话的当前使用量。
--SYS用户授权给普通用户ZKM 09:52:44 SYS@xxxxxxx1(328)> grant all on v_$mystat to zkm; Grant succeeded. Elapsed: 00:00:00.01 09:56:29 SYS@xxxxxxx1(328)> grant all on v_$statname to zkm; Grant succeeded. Elapsed: 00:00:00.01 --用户ZKM创建视图 09:53:16 ZKM@xxxxxxx1(321)> create or replace view redosize as select a.name,b.value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name='redo size'; View created. Elapsed: 00:00:00.01
先看CTAS产生的日志量：
14:29:02 ZKM@xxxxxxx1(321)> --当前日志量 14:29:02 ZKM@xxxxxxx1(321)> select * from redosize; NAME VALUE ------------------------- ---------- redo size 0 Elapsed: 00:00:00.00 14:29:04 ZKM@xxxxxxx1(321)> create table copy as select * from test; Table created. Elapsed: 00:00:26.91 14:29:47 ZKM@xxxxxxx1(321)> select * from redosize; NAME VALUE ------------------------- ---------- redo size 1557712 Elapsed: 00:00:00.00 14:29:53 ZKM@xxxxxxx1(321)> select (1557712-0)/1024/1024 mb from dual; MB ---------- 1.48554993 Elapsed: 00:00:00.00
创建时间为26.91s，产生了1.49M的日志。
重复几次上边过程...（第一次创建表时间比较长是因为首次产生物理IO）
| 第一次 | 第二次 | 第三次 | |
| CTAS时间（s） | 26.91 | 14.04 | 13.91 |
| 产生的REDO（MB） | 1.49 | 1.49 | 1.49 |
普通创建然后insert into select的方式：
14:40:40 ZKM@xxxxxxx1(321)> select * from redosize; NAME VALUE ------------------------- ---------- redo size 4972836 Elapsed: 00:00:00.01 14:40:45 ZKM@xxxxxxx1(321)> create table copy as select * from test where 1=2; Table created. Elapsed: 00:00:00.03 14:40:58 ZKM@xxxxxxx1(321)> insert into copy select * from test; 1048576 rows created. Elapsed: 00:01:47.38 14:43:14 ZKM@xxxxxxx1(321)> commit; Commit complete. Elapsed: 00:00:00.32 14:43:19 ZKM@xxxxxxx1(321)> select * from redosize; NAME VALUE ------------------------- ---------- redo size 3534742092 Elapsed: 00:00:00.00 14:43:35 ZKM@xxxxxxx1(321)> select (3534742092-4972836)/1024/1024 mb from dual; MB ---------- 3366.25028 Elapsed: 00:00:00.00
创建时间为107.38s，生产日志为3366.25M。
重复几次上边过程...（第二/三次insert久看了下在等待log buffer space这个事件）
| 第一次 | 第二次 | 第三次 | |
| 创表及insert时间（s） | 107.38 | 282.82 | 216.89 |
| 产生的REDO（MB） | 3366.25 | 3371.29 | 3370.83 |
copy的方式：
14:57:50 ZKM@xxxxxxx1(321)> select * from redosize; NAME VALUE ------------------------- ---------- redo size 0 Elapsed: 00:00:00.00 14:57:52 ZKM@xxxxxxx1(321)> copy from zkm/oracle@192.168.1.224/xxxxxxx create copy using select * from test; Array fetch/bind size is 15. (arraysize is 15) Will commit when done. (copycommit is 0) Maximum long size is 9999. (long is 9999) Table COPY created. 1048576 rows selected from zkm@192.168.1.224/xxxxxxx. 1048576 rows inserted into COPY. 1048576 rows committed into COPY at DEFAULT HOST connection. 15:01:46 ZKM@xxxxxxx1(321)> select * from redosize; NAME VALUE ------------------------- ---------- redo size 3591624916 Elapsed: 00:00:00.00 15:01:58 ZKM@xxxxxxx1(321)> select (3591624916-0)/1024/1024 mb from dual; MB ---------- 3425.24044 Elapsed: 00:00:00.00
时间为234s，产生的日志量为3425.24M。
重复几次上边过程...
| 第一次 | 第二次 | 第三次 | |
| COPY时间（s） | 234 | 244 | 290 |
| 产生的REDO（MB） | 3425.24 | 3425.9 | 3424.94 |
整合下几次的结果：
| 第一次 | 第二次 | 第三次 | |
| CTAS时间（s） | 26.91 | 14.04 | 13.91 |
| 产生的REDO（MB） | 1.49 | 1.49 | 1.49 |
| 创表及insert时间（s） | 107.38 | 282.82 | 216.89 |
| 产生的REDO（MB） | 3366.25 | 3371.29 | 3370.83 |
| COPY时间（s） | 234 | 244 | 290 |
| 产生的REDO（MB） | 3425.24 | 3425.9 | 3424.94 |
看结果从性能以及消耗上看还是CTAS最佳，不过CTAS不够另外两个灵活，比如无法追加数据。
至于insert into select和copy，本质上是一样的都是insert数据。
从使用功能上看，copy还是较为简单的：
15:34:47 ZKM@xxxxxxx1(321)> help copy COPY ---- Copies data from a query to a table in the same or another database. COPY supports CHAR, DATE, LONG, NUMBER and VARCHAR2. COPY {FROM database | TO database | FROM database TO database} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, ...)] USING query where database has the following syntax: username[/password]@connect_identifier
目前我体会到的方便就是可以跨库进行操作，虽然普通insert into select也可以不过需要提前先创建dblink。
这个就不用，直接简易连接就可以。
甚至这个都不需要在服务器上，只要有sqlplus就可以远程两个库进行操作，还是相当方便的。
另外我发现，我上述的copy实验操作都是通过监听简易连接，然后默认在本地create表。
但是实际上copy期间，观察网络流量的话发现业务网卡是不产生额外流量的。（lo产生了）