Long time ago when i was still an apprentice doing my fist ETL for one i learned about the good and common practice that specific tables in data warehouse should be loaded in NOLOGGING mode. Reasoning for doing this were sound, we did it for the tables that can always be repopulated with ETL. It won’t generate as much as log, plus it can add some speed to loading process.
However, the way it was done was using the NOLOGGING hint on each statement that inserted or merged data to a specific table. At that time i blindly added /*+APPEND NOLOGGING*/ hints almost everywhere, never testing the actual result of the hint. Which was stupid because simple 15 minute test shows what this hit really does. Which is nothing. The hint NOLOGGING does not exist, you can’t find it in the oracle documentation and test shows how generate same amount of redo with or without it.
Even worse. In versions of oracle that i tested this hint (11.2), it has have some influence but not on logging or redo. It influences additional hints that are appearing after NOLOGGING hint.
Here is a test case. Before and after each insert statement i checked redo generated and explain plan.
--1) insert standard insert /*+append */ into tmp_ds_testNolog select * from test_tab ; --1.857.893 rows inserted. --577873 kb redo gerenated --2) insert with nologging hint truncate table tmp_ds_testNolog;  insert /*+append nologging*/ into tmp_ds_testNolog select * from test_tab ; --575979 kb redo --3) insert after altering the table to nologging truncate table tmp_ds_testNolog; alter table tmp_ds_testNolog nologging; insert /*+append noparallel*/ into tmp_ds_testNolog select * from test_tab ; --420 kb redo
Now we have proven that nologging hint has nothing to do with redo logging. And that proper way to do it is to alter table and set nologgin property. But what about that nonsense where nologging cancels all hints that came after? Here is an example. 4 and 5 demonstrate how nologging hint cancels parallel hint. And another example (6) that shows that any other word that doesn’t mean anything has no effect on other hints in the hint comment.
This proves that nologging hint has some logic implemented in oracle, but probably incomplete or deprecated.
--table test_tab has noparallel, we will try to force parallel execution with a hint --4) check explain plan with one hint select /*+parallel(4)*/ from test_tab; --explain plan: ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1871K| 481M| 5441 (1)| 00:00:01 | | 1 | PX COORDINATOR | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | 1871K| 481M| 5441 (1)| 00:00:01 | | 3 | PX BLOCK ITERATOR | | 1871K| 481M| 5441 (1)| 00:00:01 | | 4 | TABLE ACCESS STORAGE FULL| test_tab | 1871K| 481M| 5441 (1)| 00:00:01 | ----------------------------------------------------------------------------------------- --5) check explain plan after adding nologging before the hint select /*+nologging parallel(4)*/ from test_tab; --explain plan: --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1871K| 481M| 19603 (1)| 00:00:01 | | 1 | PARTITION RANGE ALL | | 1871K| 481M| 19603 (1)| 00:00:01 | | 2 | TABLE ACCESS STORAGE FULL| test_tab | 1871K| 481M| 19603 (1)| 00:00:01 | --------------------------------------------------------------------------------------- --6) let's try any other nonsense word before parallel hint: select /*+blahblah parallel(4)*/ from test_tab; --explain plan: ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1871K| 481M| 5441 (1)| 00:00:01 | | 1 | PX COORDINATOR | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | 1871K| 481M| 5441 (1)| 00:00:01 | | 3 | PX BLOCK ITERATOR | | 1871K| 481M| 5441 (1)| 00:00:01 | | 4 | TABLE ACCESS STORAGE FULL| test_tab | 1871K| 481M| 5441 (1)| 00:00:01 | -----------------------------------------------------------------------------------------
SQL used to get redo generated from current session is:
SELECT ROUND(value/1024) redo_kb
FROM v$statname n,v$sesstat s, v$session b
WHERE (b.sid,b.serial#) IN
(SELECT sid SID, serial# Serial#
FROM v$session
WHERE AUDSID = USERENV('SESSIONID'))
AND n.name = 'redo size'
AND s.statistic# = n.statistic#
AND s.sid =b.sid