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而言在查询插入时所消耗的资源较高。