Re: why CTAS faster than Insert select?

From: linda <linglipeng_at_yahoo.com>
Date: 4 Apr 2005 14:09:08 -0700
Message-ID: <1112648948.418519.311360@f14g2000cwb.googlegroups.com>

Jonathan,

Below is the main waits from v$system_event (delta value) for CTAS and INSERT: CTAS:
RUNI EVENT

---- ----------------------------------------------------------------

TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT TIME_WAITED_MICRO
----------- -------------- ----------- ------------ -----------------
CTAS PX Deq: Execute Reply

1604 61 40451 -1 404507101

CTAS PX Deq: Execution Msg

5371 3049 627460 -3 6274606403

CTAS PX Deq: Table Q Normal

419903 1298 762431 0 7624304483

INSERT:
RUNID EVENT

------ ----------------------------------------------------------------

TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT TIME_WAITED_MICRO
----------- -------------- ----------- ------------ -----------------
INSERT PX Deq: Execute Reply

1749 164 61942 2 619426065

INSERT PX Deq: Table Q Normal

542384 568 729409 0 7294090952

INSERT PX Deq: Execution Msg

7402 4764 969411 0 9694109378

The top waits from v$session_event for all PX slaves and coordinator session (summed up by event):
CTAS:
EVENT
WAITS

TOTAL_TIME

PX Deq: Execute Reply
1530

29976

direct path write
45080

37758

direct path read
35197

45250

PX Deq: Execution Msg
5164

603556

INSERT:
EVENT
WAITS

TOTAL_TIME

PX Deq: Execute Reply
1677

52637

direct path write
19129

190675

PX Deq: Execution Msg
3243

555230

PX Deq: Table Q Normal
346561

571523

other than PX related, direct path write for INSERT is much higher than CTAS

Also the result from sql trace file with 10046 event at level 8.

CTAS:

command type                count       cpu   elapsed         disk
  query
     current         rows    misses


----------------------- --------- --------- --------- ------------

------------ --------- create table........... 2 1.19 388.18 5003892 3600257 11008 36326088 1 alter session.......... 5 0.00 0.01 0 0 0 0 3
----------------------- --------- --------- --------- ------------
------------ --------- total.................. 7 1.19 388.19 5003892 3600257 11008 36326088 4

INSERT:

command type                count       cpu   elapsed         disk
  query
     current         rows    misses


----------------------- --------- --------- --------- ------------

------------ --------- insert................. 2 0.89 592.10 8 767 116 36326088 1 alter session.......... 5 0.00 0.00 0 0 0 0 0 commit................. 2 0.09 8.52 6464862 3599220 1003994 0 1 pl/sql execute......... 2 0.01 0.36 0 0 0 1 1
----------------------- --------- --------- --------- ------------
------------ --------- total.................. 11 0.99 600.98 6464870 3599987 1004110 36326089 3

I wonder how PX slaves behave differently for CTAS and INSERT, both at the same version 9.2.0.5. I remember did some test before with lower volume and indeed INSERTs sometimes faster than CTAS. But as we test higher volumes, almost consistently CTAS is faster, that is why I couldn't understand.

Thanks,
Linda Received on Mon Apr 04 2005 - 16:09:08 CDT