RE: Paralellizing Pl/sql inserts
Date: Wed, 23 Jan 2008 09:56:10 -0500
Yes, I have received many good suggestions and I am benchmarking them now. Here are the results so far.
The big table has 14 partitions on sales date. Each partition corresponds to a calendar quarter (2005q1, 2005q2, 2005q3 etc.) Primary partition refers to the partition that will receive 80-85% of the inserts. Unfortunately the other 15% of data hits all the other partitions.
Case 1 - Use bulk inserts with save exception clause - PK + 5 indexes enabled - 1,000,000 test - 15 minutes
Case 2 - Merge when not matched insert (no parallel) - PK + 5 indexes enabled - 1,000,000 test - 11 minutes
Case 3 - Merge when not matched insert (no parallel) - PK + 5 indexes enabled - 2,000,000 test - 11 minutes
Case 4 - Merge when not matched insert (parallel 16) - PK + 5 indexes enabled - 1,000,000 test
Case 5 - Merge when not matched insert (parallel 16) - PK enabled and 5 indexes disabled on primary partition- 1,000,000 test
Case 6 - Use bulk inserts with save exception clause - PK enabled and 5 indexes disabled on primary partition - 1,000,000 test
Case 7 - Use an insert as select with a not exists - TBD
From: Finn Jorgensen [mailto:finn.oracledba_at_gmail.com]
Sent: Wednesday, January 23, 2008 9:23 AM
Cc: Ken Naim; oracle-l_at_freelists.org
Subject: Re: Paralellizing Pl/sql inserts
Lots of good suggestions here.
How is your big table partitioned? If it's something like 128 hash partitions it's going to be a pain, but if it's something more manageable, you could try splitting your 65 million row inserts into data by partition and do insert /*+ append */ bigtable (partition). Each of those can go in parallel (seperate stmts). Drop indexes first and recreate using massive parallel hint after inserts are done.
Let us know how it works out for you. It's an interesting problem.
FinnReceived on Wed Jan 23 2008 - 08:56:10 CST