I were doing some Oracle tuning jobs in last two weeks, and got a SQL like following :
INSERT /*+ APPEND */ INTO B
SELECT /*+ PARALLEL(A,4) FULL(A) */
SEQ_B.NEXTVAL, A.*
FROM A;
The table size of A is 25GB, contains 100m rows. We are running Oracle on IBM P590, the stroage is EMC DMX4, but it still take us 26 mins. Of cause we cannot afford the long duration. The problem is about the sequence, getting the next value is too slow. The original sequence cache is 20. Then I change the script as following :
ALTER SEQUENCE SEQ_B CACHE 2000;
INSERT /*+ APPEND */ INTO B
SELECT /*+ PARALLEL(A,4) FULL(A) */
SEQ_B.NEXTVAL, A.*
FROM A;
ALTER SEQUENCE SEQ_B CACHE 20;
Now it takes 10 mins, which is acceptable for us. Tuning the sequence saves us 15 mins.