Database Reference
In-Depth Information
To improve efficiency and, thereby, performance, a direct-path insert uses direct writes to load data directly
above the high watermark of the modified segment. This fact has important implications:
•
The buffer cache, because of direct writes, is bypassed.
•
Concurrent
DELETE
,
INSERT
,
MERGE
, and
UPDATE
statements, as well as the (re)build of
indexes on the modified segment, aren't permitted. Naturally, segment locks are obtained to
guarantee this.
•
The blocks containing free space below the high watermark aren't taken into consideration.
This means that even if
DELETE
statements are regularly executed in order to purge data, the
size of the segment would increase constantly.
One of the reasons that direct-path inserts lead to better performance is that only minimal undo is generated for
the table segment. In fact, undo is generated only for space management operations (for example, to increase the high
watermark and add a new extent to the segment), and not for the rows contained in the blocks that are inserted via
direct-path. If the table is indexed, however, undo is normally generated for the index segments. If you want to avoid
the undo related to index segments as well, you can make the indexes unusable before the load and rebuild them
when the load is finished. Especially in ETL jobs, this is common practice; also, it's popular because it may be faster to
rebuild the index than to let the database engine do the maintenance at the end of the load.
To further improve performance, you can also use
minimal logging
. The aim of minimal logging is to minimize
redo generation. This is optional, but it's often essential to greatly reduce response time. You can instruct minimal
logging to be used by setting the
nologging
parameter at the table or partition level. The essential thing to understand
is that minimal logging is supported only for direct-path inserts and some DDL statements. In fact, redo is always
generated for all other operations. Be aware that minimal logging can't be used for tables stored in clusters.
■
You should specify
nologging
and, thereby, minimize redo generation only if you fully understand the implica-
tions of doing so. In fact, media recovery can't be performed for blocks modified with minimal logging. This means that if
media recovery is performed, the database engine can only mark those blocks as logically corrupted, because media
recovery needs to access the redo information in order to reconstruct the block's contents, and that isn't possible since, as
mentioned previously, redo information isn't stored when using minimal logging. as a result, SQL statements that access
objects containing such blocks terminate with an
ORA-26040: Data block was loaded using the NOLOGGING option
error. Therefore, you should use minimal logging only if either you can manually reload data, you're willing to make a
backup after the load, or you can afford to lose data.
Note
Figure
15-11
shows an example of the improvement you can achieve with direct-path inserts. These figures were
measured by starting the
dpi_performance.sql
script on my test system.
