Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!f14g2000cwb.googlegroups.com!not-for-mail From: "linda" Newsgroups: comp.databases.oracle.server Subject: Re: why CTAS faster than Insert select? Date: 4 Apr 2005 14:09:08 -0700 Organization: http://groups.google.com Lines: 141 Message-ID: <1112648948.418519.311360@f14g2000cwb.googlegroups.com> References: <1112121974.648540.12120@l41g2000cwc.googlegroups.com> <1112290381.855628.4540@g14g2000cwa.googlegroups.com> NNTP-Posting-Host: 198.204.141.208 Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Trace: posting.google.com 1112648952 32379 127.0.0.1 (4 Apr 2005 21:09:12 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Mon, 4 Apr 2005 21:09:12 +0000 (UTC) In-Reply-To: User-Agent: G2/0.2 Complaints-To: groups-abuse@google.com Injection-Info: f14g2000cwb.googlegroups.com; posting-host=198.204.141.208; posting-account=8YnLxQ0AAABONu0N15O4Fdw7fNDVhFAc Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:239614 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