Re: why CTAS faster than Insert select?

Linda,

Trying to emulate your test case - but on a smaller scale - I don't get any conclusive answers. If anything, I get slightly better performance on the insert than I do on
the create.

I have seen a couple of oddities with
parallel execution on 9.2.0.5 in the
past (including CTAS dumping data
to the temp tablespace when it shouldn't). It is possible that there is a version dependent difference between what you are seeing and what I am seeing.

Given that you are running parallel 24, you should have 48 PX slaves running for the tests - and you really need to find out where they are losing their time and using their CPU. Unfortunately, although PX slaves sum their session stats back to the co-ordinator, they do not sum their wait information. So you may want to try running your tests when nothing else is using the database, and report

    v$system_event
to see if you can spot any significant differences in the two test cases. Alternatively, you could wait until just a few seconds before you expect the statements to complete, and run a query at the session level to capture detailed information from all the PX sessions.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated March 9th 2005






"linda" <linglipeng_at_yahoo.com> wrote in message 
news:1112290381.855628.4540_at_g14g2000cwa.googlegroups.com...


> Jonathan,

>

> Here is the details:

>  version of oracle: 9.2.0.5.0

>  time recorded in v$session_event:

>  CTAS: