Chapter 9. Transaction Size and Performance Issues

Performance plays a very important role in the success of any system—hardware, software, utility—anything you can think of. SQL*Loader provides many ways to control or improve its performance. Previous chapters occasionally discussed performance issues, or mentioned parameters, clauses, or keywords that relate to the performance of SQL*Loader. In this chapter, we consolidate the discussion of SQL*Loader’s performance, explore issues involved with it, and show you ways to control and improve it.

The discussion in this chapter mainly focuses on performance when doing a conventional path load. Direct path loads, because they are performed so differently from conventional path loads, lead to an almost completely different set of performance issues. You’ll find a detailed discussion of direct path loads, including performance issues, in Chapter 10.

Transaction Processing in SQL*Loader

When you load data using SQL*Loader, it prepares SQL INSERT statements for the input data, and passes those statements to the Oracle server. The Oracle server then processes those statements as it loads the data. From the standpoint of the Oracle server, this insertion of data into Oracle tables is nothing but a series of regular transactions. The Oracle server handles these transactions just as it does any other transactions. For each transaction, the Oracle server does the following:

  1. Obtains a lock on the table being loaded.

  2. Records undo information ...

Get Oracle SQL*Loader: The Definitive Guide now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.