When you want to refresh your data warehouse database using parallel insert, update, or delete operations on a data warehouse, there are additional issues to consider when designing the physical database. These considerations do not affect parallel execution operations. These issues are:
If a parallel restriction is violated, the operation is simply performed serially. If a direct-path
INSERT restriction is violated, then the
APPEND hint is ignored and a conventional insert operation is performed. No error message is returned.
For tables that do not have the parallel DML
itl invariant property (tables created before Oracle9i Release 2 (9.2) or tables that were created with the
COMPATIBLE initialization parameter set to less than
9.2), the degree of parallelism (DOP) equals the number of partitions or subpartitions. That means that, if the table is not partitioned, the query runs serially. To see what tables do not have this property, issue the following statement:
SELECT u.name, o.name FROM obj$ o, tab$ t, user$ u WHERE o.obj# = t.obj# AND o.owner# = u.user# AND bitand(t.property,536870912) != 536870912;
For information about the interested transaction list (ITL), also called the transaction table, refer to Oracle Database Concepts.
If you have global indexes, a global index segment and global index blocks are shared by server processes of the same parallel DML statement. Even if the operations are not performed against the same row, the server processes can share the same index blocks. Each server transaction needs one transaction entry in the index block header before it can make changes to a block. Therefore, in the
INDEX statements, you should set
INITRANS, the initial number of transactions allocated within each data block, to a large value, such as the maximum DOP against this index.
There is a limitation on the available number of transaction free lists for segments in dictionary-managed tablespaces. After a segment has been created, the number of process and transaction free lists is fixed and cannot be altered. If you specify a large number of process free lists in the segment header, you might find that this limits the number of transaction free lists that are available. You can abate this limitation the next time you re-create the segment header by decreasing the number of process free lists; this leaves more room for transaction free lists in the segment header.
DELETE operations, each server process can require its own transaction free list. The parallel DML DOP is thus effectively limited by the smallest number of transaction free lists available on the table and on any of the global indexes the DML statement must maintain. For example, if the table has 25 transaction free lists and the table has two global indexes, one with 50 transaction free lists and one with 30 transaction free lists, the DOP is limited to 25. If the table had 40 transaction free lists, the DOP would have been limited to 30.
FREELISTS parameter of the
STORAGE clause is used to set the number of process free lists. By default, no process free lists are created.
The default number of transaction free lists depends on the block size. For example, if the number of process free lists is not set explicitly, a 4 KB block has about 80 transaction free lists by default. The minimum number of transaction free lists is 25.
Parallel DML operations use a large number of data, index, and undo blocks in the buffer cache during a short interval. For example, suppose you see a high number of
free_buffer_waits after querying the
V$SYSTEM_EVENT view, as in the following syntax:
SELECT TOTAL_WAITS FROM V$SYSTEM_EVENT WHERE EVENT = 'FREE BUFFER WAITS';
[NO]LOGGING clause applies to tables, partitions, tablespaces, and indexes. Virtually no log is generated for certain operations (such as direct-path
INSERT) if the
NOLOGGING clause is used. The
NOLOGGING attribute is not specified at the
INSERT statement level but is instead specified when using the
CREATE statement for a table, partition, index, or tablespace.
When a table or index has
NOLOGGING set, neither parallel nor serial direct-path
INSERT operations generate redo logs. Processes running with the
NOLOGGING option set run faster because no redo is generated. However, after a
NOLOGGING operation against a table, partition, or index, if a media failure occurs before a backup is performed, then all tables, partitions, and indexes that have been modified might be corrupted.
INSERT operations (except for dictionary updates) never generate redo logs if the
NOLOGGING clause is used. The
NOLOGGING attribute does not affect undo, only redo. To be precise,
NOLOGGING allows the direct-path
INSERT operation to generate a negligible amount of redo (range-invalidation redo, as opposed to full image redo).
For backward compatibility,
[UN]RECOVERABLE is still supported as an alternate keyword with the
TABLE statement. This alternate keyword might not be supported, however, in future releases.
At the tablespace level, the logging clause specifies the default logging attribute for all tables, indexes, and partitions created in the tablespace. When an existing tablespace logging attribute is changed by the
TABLESPACE statement, then all tables, indexes, and partitions created after the
ALTER statement have the new logging attribute; existing ones do not change their logging attributes. The tablespace-level logging attribute can be overridden by the specifications at the table, index, or partition level.
The default logging attribute is
LOGGING. However, if you have put the database in
NOARCHIVELOG mode, by issuing
NOARCHIVELOG, then all operations that can be done without logging do not generate logs, regardless of the specified logging attribute.