The performance view of the import process

Edit online

From the performance perspective, the import process is a mechanism with a complex structure.

Figure 1 illustrates the import process.
Figure 1. Structure of the import process

Figure 2: Structure of the import process
  1. The procedure starts with the Oracle Import process. This import process is a single threaded application, so it does not benefit from multiple processors. This process reads a certain chunk of data from a file, prepares the data, and transfers them to the database.

    This pure sequential read disk I/O and CPU processing requires a high disk I/O bandwidth and a fast CPU. Sequential I/O is a very fast I/O pattern. Performance benefits from preserving the sequential characteristics on a disk. So we did not mix this pattern with any other disk I/O load on the same disk.

    This import process itself can use only one CPU, but there is no specific affinity to a certain CPU - therefore we cannot conclude from the performance data, how much load on a certain CPU is caused by the import process and how much from the database. The average CPU utilization was about 64% and 5% I/O wait for one CPU, and about 49% with 3% I/O wait for the other. The total CPU utilization was of about 110% (where 100% is one CPU), indicating that there is also significant effort from the database (see bullet 2.). The value of 5% I/O wait can be considered as low, reading the import data is one reason for this value.

  2. The database waits to get the input from the import process and then inserts it into the tables. After this it waits again for new data from the import process. These activities are very short and switch with a high frequency, so they cannot be isolated in the performance data. From the performance perspective, the database has two types of disk I/O load patterns:
    1. Inserting new data and creating indices is randomized read and write I/O (DBW0)
    2. Writing the logs is pure sequential write (LOGWR)
    As before, the sequential write pattern of the log related disk I/O should be preserved instead of being mixed with any other activity on the same disk. Writing the log is also a candidate to cause I/O wait. Our low value of I/O wait indicates that the setup and the disk subsystem provide sufficient I/O bandwidth.

    The database management system provides a very efficient large buffering system and many optimizations for the data related disk I/O, especially when using Direct I/O and Asynchronous I/O (filesystemio_options = setall).

  3. Once a table is loaded, the corresponding indices are created. This is a very special phase. Now the import process is waiting, while the database management system shows all its strengths in dispatching and balancing workloads. This phase works with multiple processes, the more CPUs are available, the shorter this phase.

    During this phase both CPUs are fully utilized and there is nearly no I/O wait, but an impressing read rate of about 250 MB/sec.

In summary, the performance of the import process determines the behavior of the whole system - it needs a fast CPU and a high disk I/O bandwidth. For the database logs, focusing on separate disks and a high disk I/O bandwidth is also important. For the processing of the imported data, we rely on the disk I/O optimization features of the database management system for the data I/O stream itself. The index creating phase benefits from fast and from multiple CPUs. We expected the whole process to improve significantly when migrating from System z9® to System z10™, just because of the faster CPUs.

The import process runs locally on the same system as the database management system, so we do not need to consider network performance.