If you are performing a direct path load in which the same date or timestamp values are loaded many times, then a large percentage of total load time can end up being used for converting date and timestamp data. This is especially true if multiple date columns are being loaded. In such a case, it may be possible to improve performance by using the SQL*Loader data cache.
The data cache reduces the number of date conversions done when many duplicate values are present in the input data. It enables you to specify the number of unique dates anticipated during the load.
The data cache is enabled by default. To completely disable the data cache, set it to 0.
The default data cache size is 1000 elements. If the default is used and the number of unique input values loaded exceeds 1000, then the data cache is automatically disabled for that table. This prevents excessive and unnecessary lookup times that could affect performance. However, if instead of using the default, you specify a nonzero value for the data cache and it is exceeded, then the data cache is not disabled. Instead, any input data that exceeded the maximum is explicitly converted using the appropriate conversion routines.
The data cache can be associated with only one table. No data cache sharing can take place across tables. A data cache is created for a table only if all of the following conditions are true:
DATA_CACHE parameter is not set to 0
One or more date values, timestamp values, or both are being loaded that require data type conversion in order to be stored in the table
The load is a direct path load
Data cache statistics are written to the log file. You can use those statistics to improve direct path load performance as follows:
If the number of cache entries is less than the cache size and there are no cache misses, then the cache size could safely be set to a smaller value.
If the number of cache hits (entries for which there are duplicate values) is small and the number of cache misses is large, then the cache size should be increased. Be aware that if the cache size is increased too much, then it may cause other problems, such as excessive paging or too much memory usage.
If most of the input date values are unique, then the data cache will not enhance performance and therefore should not be used.
Data cache statistics are not written to the SQL*Loader log file if the cache was active by default and disabled because the maximum was exceeded.
If increasing the cache size does not improve performance, then revert to the default behavior or set the cache size to 0. The overall performance improvement also depends on the data types of the other columns being loaded. Improvement will be greater for cases in which the total number of date columns loaded is large compared to other types of data loaded.