随笔- 248 文章- 0 评论- 18 阅读- 21万

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