Re: why CTAS faster than Insert select?

From: linda <linglipeng_at_yahoo.com>
Date: 31 Mar 2005 09:33:01 -0800
Message-ID: <1112290381.855628.4540@g14g2000cwa.googlegroups.com>

Jonathan,

Here is the details:
  version of oracle: 9.2.0.5.0
  time recorded in v$session_event:
  CTAS:

EVENT                               WAITS   TIMEOUTS TOTAL_TIME

AVG
------------------------------ ---------- ---------- ----------

PX Deq: Execute Reply                1612         63      39707
25
SQL*Net message from client            24          0        688
29
PX Deq: Parse Reply                    16          0          2
 0
latch free                              1          0          0
 0
enqueue                                 1          0          0
 0
control file sequential read            4          0          0
 0
SQL*Net more data from client           1          0          0
 0
SQL*Net message to client              24          0          0
 0
log file sync                           5          0          0
 0
PX Deq: Join ACK                       25          0          0
 0
PX Deq: Signal ACK                      6          0          0

11 rows selected.

Insert Select:

EVENT                               WAITS   TIMEOUTS TOTAL_TIME

AVG
------------------------------ ---------- ---------- ----------

PX Deq: Execute Reply                1729        170      62951
36
SQL*Net message from client            27          0        888
33
PX Deq: Signal ACK                      2          2         11
 5
log file sync                           5          0          2
 0
PX Deq: Parse Reply                    17          0          2
 0
latch free                              3          0          1
 0
control file sequential read            4          0          0
 0
db file sequential read                 8          0          0
 0
PX qref latch                           1          0          0
 0
SQL*Net more data from client           1          0          0
 0
SQL*Net message to client              27          0          0
 0
PX Deq: Join ACK                        7          0          0

12 rows selected.
  redo generated:
  CTAS: 2130240
  INSERT: 4753332

I consider 2M difference is minimum. The table is created nologging so INSERT will not generate redo. The full redo size would be huge if table created logging (I measured about 1G per minute before). CPU time recorded in v$sesstat: CTAS: Elapsed: 00:06:47.40 NAME VALUE

recursive cpu usage 62 CPU used when call started 306439 CPU used by this session 306439 parse time cpu 17

INSERT: Elapsed: 00:10:43.61
NAME
VALUE

recursive cpu usage
62
CPU used when call started
437671
CPU used by this session
437671
parse time cpu
24
Framework of the two tests:
CTAS:
drop table tmp_pkt_colt_fncl_st_spst;
alter session force parallel dml;

CREATE TABLE TMP_PKT_COLT_FNCL_ST_SPST NOLOGGING

PARALLEL 24                       -- since 24 is used by Insert as well

(oracle determines it)
PARTITION BY ...
AS SELECT ... FROM ...WHERE ...; INSERT Select:
truncate table tmp_pkt_colt_fncl_st_spst; -- same table created as NOLOGGING before
alter session force parallel dml;

INSERT /*+ APPEND */
INTO TMP_PKT_COLT_FNCL_ST_SPST
SELECT ... FROM ... WHERE ...; For both tests, the table is created as nologging.

Thanks,
Linda Received on Thu Mar 31 2005 - 11:33:01 CST