The basis was;

SQL> set timing on
SQL> create table X0 as select * from Y;

Table created.

Elapsed: 00:03:09.60

SQL> select count(*) from Y;

  COUNT(*)
----------
    108411

The table Y is a very ordinary table, without any strange datatypes, just numbers and varchars. Its situated on a powerfull machine. The same create on a much less powerfull machine, it takes 2 sec! Its critical to find a solution, so I went to find something that could help.

SQL> create table X1 parallel (degree 25) as select * from Y;

Table created.

Elapsed: 00:00:28.20

That helped ! However its a workaround - but gave me some time. I pulled an AWR report, looked at it myself, and sent a copy to the supplier, who was having this performance problem. They returned quickly! very worried that during the snapshot (1 hour from 09:00 to 10:00) 97% was spent on CPU.

Statistic                                                   Name Time (s) % of DB Time
sql execute elapsed time                                    1,215.14      99.88
DB CPU                                                      1,188.51      97.69
parse time elapsed                                             20.32       1.67
PL/SQL execution elapsed time                                   2.52       0.21
hard parse elapsed time                                         2.02       0.17
PL/SQL compilation elapsed time                                 0.31       0.03
connection management call elapsed time                         0.11       0.01
failed parse elapsed time                                       0.04       0.00
hard parse (sharing criteria) elapsed time                      0.01       0.00
repeated bind elapsed time                                      0.01       0.00
DB time                                                     1,216.63  
background elapsed time                                       283.52  
background cpu time                                           101.19

However thats not the way I see it! I see it as beeing excellent actually. It just tells that my machine spent its time executing SQL, in reaction to users asking for work to be done. DB CPU tells us, that the database is using the machine, and the machine is excuting SQL, super!

Back to the problem! After having read the AWR report, I noticed a lot of activity around Oracles recycle bin. For the fun of it lets look into that.

SQL> alter session set recyclebin=OFF;

SQL> create table X2 as select * from Y;
create table X2 parallel (degree 25) as select * from Y
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P021
ORA-01652: unable to extend temp segment by 8 in tablespace CREWS

Ups!  I done this a couple of times! There is data inside the recycle bin, the solution is purge.

SQL> alter session set recyclebin=ON;

SQL> purge recyclebin;

Recyclebin purged.

Again for the fun of it! Lets not set recyclebin=OFF, and lets try without using parallel, to see if theres any effect.

SQL> create table X3 as select * from Y;

Table created.

Elapsed: 00:00:00.62

Et Voila!