Re: Loading 300 million records

AlexK wrote:

> I'm trying to improve the performance of a procedure that loads
> approximately 300 million records into a table.
<snipped>

What is missing from this is the actual problem definition. Yes, you have made the problem statement - the load is slow. Great. Next, and I would think is The Very Obvious Question, is WHY IS IT SLOW?

Is the bottleneck the query that does the join of the tables in order to supply the INSERT with data?

Is the bottleneck the INSERT?

Is the bottleneck I/O for either the reader (the SELECT part) or the writer (the INSERT part)?

Is the bottleneck CPU load? Or swap space? A small db cache?

Is this done via a dblink in which case is the bottleneck could be the network?

The only way to do an accurate diagnosis of a problem is to have an accurate problem definition.

I can tell you to use CTAS instead, running PQ (which multi-threads both reader and writer parts of the process), creating a new table. Then exchange the contents of the table with a partition in your VLT. It may be sensible advice, but utterly useless if the bottleneck is I/O and by running multiple PQ I/O processes you are now so seriously trashing the I/O subsystem that the o/s either grinds to a halt, or even crash.

I can tell you to look at the execution plan of the SELECT, but that could be futile if the INSERT process is serialised doing all the I/O - there is a physical "speed limit" for a process writing to disk. And there's almost nothing you can do to increase that speed limit.. besides increasing the I/O capacity somehow (striping, multiple I/O paths to the same device, etc.)

The basic and most fundemental rule in solving problems (be that bugs, performance or writing code or business processes) is to IDENTIFY THE PROBLEM. Only then do you have a hope to solve it.

Which IMO also explains the bullet holes in so many peoples' feet. A quick draw means jack nothing if you do not know what to shoot at.

--
Billy