Re: Loading 300 million records

AlexK wrote:

> The target table is not partitioned and has no indexes.

When dealing with VLTs (Very Large Tables) and partitioning should always be considered.

> It states that programmatic logic using bulk
> collects would be slower than a SQL statement alone. Does that apply
> in all scenarios?

Yes. The expense is working in another language (like PL/SQL) and having to ship the data across into variables into that language.

Take PL/SQL. Opening a cursor causes a context switch to the SQL engine and runs a SQL statement. Doing a [FETCH cursor INTO var] also does a context switch to the SQL engine to fetch the next row from the database. Changing that FETCH into a BULK COLLECT reduces the number of context switches and improves performance. However, this can never be as fast as doing it all directly in the SQL engine (no context switches) - if possible. And that's the crux as it is not always possible to do everything in SQL and that's why we have PL/SQL.

> I must admit that this is my first experience working with a data
> warehouse this large. As such, I'm not sure if it is normal for it to
> take 10 or more hours to load this data. Has anyone worked with such
> large data sets? Have they seen similar timings?

Depends. One of the major factors in dealing with large volumes of data is I/O. And I/O is the single most expensive operation to do in a database ito performance. To get a block of disk takes x amount of time. And there's very little you can do from a software side to reduce that time. All you can do is reduce the I/O - which is why there are comments such as "no indexes/triggers on target table".

> physical machine has 12 CPUs and logically 3 are allocated to this db.

Hardware partitioning? If not, then all 12 CPUs will be available I believe.

> Finally, to address some comments Billy made. I know the select part
> of the query runs slowly, but I do not know if the insert is running
> slowly as well. I'm not sure how to test that. If you have any advice
> as to how to give you more info, I would be glad to do it.

Have a look at the execution plan of the SELECT statement alone. Consider enabling SQL tracing for the process and TKPROF the output. (all explained in the Performance Tuning Guide)

To determine why the process is "slow" (it may just not be and actually be very fast) have a look at the statistics, wait states and events of the process. This data is stored in the V$ (virtual) views - views on internal data structutures in Oracle (SGA and PGA and UGA) that shows you what is happening under the hood. These views are detailed in the Reference Guide. (e.g. v$sesstat, v$session_event, v$session_wait, v$session_longops etc)

I also suggest looking at o/s stats - will tell you where to start looking for the actual problem causing the slow perfomance. (and how I miss HP-UX's Glance tool on other platforms - that tool alone is worth using HP-UX over other operating systems)

> Also, you
> had made some references to PQ, CTAS, and VLT. Correct me if I'm
> wrong, but I'm assuming PQ is Parallel Query and VLT is Virtual Logical
> Table. What is CTAS, I'm not quite familiar with that acronym.

VLT = Very Large Table
CTAS = Create Table As Select

CTAS is the fastest way to move data into a new table structure within the database. And can be done using PQ - which will parallelise both the readers (the SELECT part) and the writers (pumping the data into the target table).

When dealing with partitioned tables, I prefer using a CTAS to create a new table and then "insert" that table into the VLT by exchanging the contents of the table with that of a partition (see ALTER TABLE .. EXCHANGE PARTITION command).

--
Billy