create as & insert into 语句性能分析

1、会话设置

alter session set sql_trace=true;
alter session set tracefile_identifier='mysession'；
create table t2 as select * from t1;

2、格式化mysession生成的trace文件；

Plan Hash: 1069440229
create table t2 as select * from t1
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.06       0.06          1        423        927       80050
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.06       0.06          1        424        927       80050

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84

Rows     Row Source Operation
-------  ---------------------------------------------------
0  LOAD AS SELECT  (cr=555 pr=1 pw=377 time=0 us)
80050   TABLE ACCESS FULL T1 (cr=381 pr=0 pw=0 time=12148 us cost=109 size=7024522 card=88918)


insert into
SQL ID: 6xcqhqnm53x7g
Plan Hash: 3617692013
insert into t3 select * from t1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.12       0.22          2       1165       4334       80050
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.13       0.22          2       1166       4334       80050

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84


Rows     Row Source Operation
-------  ---------------------------------------------------
0  LOAD TABLE CONVENTIONAL  (cr=0 pr=0 pw=0 time=0 us)
1   TABLE ACCESS FULL T1 (cr=4 pr=0 pw=0 time=0 us cost=109 size=7024522 card=88918)

---

结果显示 insert into 相较create table而言在查询插入时所消耗的资源较高。