In our DWH db, every day during ETL process we use direct path insert as much as possible to speed up load operations. Here are two main approaches we have:
forall i in v_row.First .. v_row.Last
insert /*+ APPEND_VALUES */ into ibs.'||i_table_name||'
values v_row
(i);
commit;
forall i in v_row.First .. v_row.Last
insert /*+ APPEND_VALUES */ into ibs.'||i_table_name||' partition('|| l_part_name || ')
values v_row
(i);
commit;
Logically, these operations should help to improve dml operations. However, I learned that in our db, force logging is enabled on database level. So my question is , is there any point of using direct path insert if force logging is on? If force logging cancels all benefits of direct insert operations, what could be alternative for them?
Force logging merely requires that all block changes be recorded to the redo logs for recoverability if the database were to crash before changes in memory are written to disk, or if files have become corrupted and the DBAs need to restore to a previous point in time. Without force logging, if the table being modified is marked with a NOLOGGING
attribute, a direct path DML operation's changes aren't written to the redo log. Because the redo log imposes a penalty and certain kinds of serialization (more so in the past than in modern versions), this can noticeably speed direct path operations up. But more importantly, it creates fewer archive logs that have to be sent to a backup device and occupy (huge) amounts of space. This particularly compelling if a predominate pattern is regular truncate-and-reload or drop-and-CTAS in which segments are constantly being rebuilt from scratch. But the danger is that if recovery is needed, you'll lose the table's contents entirely, or at least since the last full backup. So it should never be used when the data needs to be protected. Use it for scratch work and for summaries/derivatives that can easily be recreated from other authoritative sources.
But even with logging happening (which always happens for conventional DML), direct path is still very much enabled and useful. Direct path bypasses the buffer cache in shared memory and all the attendant concurrency mechanisms that protect it, and reads/writes privately and directly to disk. This also helps DBWR and its slaves not get overloaded since they aren't doing the work in the background. Direct path inserts also bypass looking among free space bitmaps for empty blocks to reuse and simply allocate entirely new extents from previously unallocated space which are dedicated to the inserting process rather than being shared (until it's committed). This also really speeds up inserts, in addition to featuring an instant rollback on failure instead of a lengthy rollback (simply drops the new extents rather than applying undo). So no, you don't lose direct path just because force logging is enabled. There are still lots of benefits to be enjoyed.