1

Oracle database is on noarchivelog mode and tablespace is configured with nologging option keeping there will not be a situation to recover any data in mind.

Following scenario was occured during ETL process:

  1. 1st hour AWR showing 15GB per second and 131K per transaction in the Load Profile.
  2. 1st hour AWR showing 54TB Redo generation logged in the "Instance Activity Statistics"

I am wondering why such a big volume of redo is generated in this context.

Considering following context for excessive redo:

  1. It's known that dictionary specific tablespace is forced for logging.
  2. Considering that NOLOGGING is not applicable by any mean to any UPDATE operations resulting from the MERGE statement.
  3. NOLOGGING is explicitely specified with CTAS and DMLs like inserts and used /*+ APPEND */ hint with the insert statement to bypass logging even if nologging options is active in table/tablespace level.

Questions:

  1. Can anyone see any other considerations beyond 1, 2 and 3, for generating such big volume REDOs?
  2. In the context of #2, 54TB(seems that Simply computed by 15G per second X 60 for min X 60 for hr) redo generation is not possible within one hour window. Suspecting some bug with the Oracle AWR report, it should be total redo generated since the Oracle is start up.
  3. Suspecting bug with the value of redo generated per seconds.

Any constructive answers for above questions and considerations are highly appreciated, so that I could rectify myself if I am wrong.

Improve this question
2
  • @LalitKumarB In my experience these problems must be solved by programmers 95% of the time. DBAs may be called in to help, but reducing REDO is a difficult process that must be built into many ETL programs and must be done by developers.
    – Jon Heller
    Commented Jan 6, 2015 at 9:44
  • @JonHeller, I agree with you, even I do the same. However, it came with experience. Some, few, most organizations would let a DBA work on it and developers stick to coding. My opinion? I have no issues if the question stays here or goes to dba.stackexchange :-)
    – Lalit Kumar B
    Commented Jan 6, 2015 at 10:16

2 Answers 2

Reset to default
5

To get to the bottom of this you likely need to identify the SQL statements that are generating the REDO. Unfortunately I don't think there is any simple, completely automated way to detect REDO per SQL.

But it's usually not too difficult to track down the worst SQL statement. Since you've already got AWR, look at the SQL Statistics section, ordered by Elapsed Time. If there's a lot of REDO then one of those statements may stick out. For example, if the top statement is an UPDATE, DELETE, MERGE (UPDATE), or an INSERT without a hint (or with a bad hint).

Possibly the top statements are all CTAS or INSERT /*+ APPEND */. In that case, look at the explain plan for each of those top statements, for example by using select * from table(dbms_xplan.display_awr(sql_id => 'sql_id from AWR')); If the execution plan says LOAD AS SELECT then it's using direct path inserts and is OK. If it says LOAD TABLE CONVENTIONAL, that's the problem statement.

Possibly they are all ok, but the REDO is generated by the indexes. The only way an index will not generate REDO is if the index is set to NOLOGGING and if the index is built after the INSERT. Index NOLOGGING only applies to index DDL. You may need to drop/disable and recreate/rebuild indexes to reduce REDO.

In my experience, the most likely causes of an INSERT statement not running in direct-path write are:

  1. Foreign key indexes enabled on the target table, except when reference partitioning is used.
  2. Bad hint syntax. For example, if there's a space before the plus sign, like this: /* +. Or if the hint is placed in a slightly wrong position, like this INSERT INTO /*+ append */ ....
  3. Weird data types that aren't supported. I can't remember exactly, but I think with LOBs there are some extra limitations, like you may need to have partitioning.
  4. Triggers on the target table.
  5. There are likely some other reasons I'm forgetting.

Post the entire SQL statement and execution plan and we may be able to help investigate.

If none of that helps, than maybe it is an AWR bug. 15GB per second is very high, but not impossible. I wouldn't be too surprised if an Exadata system could achieve that.

Improve this answer
4
  • Thanks Jon for the comprehensive response, really appreciated. Based on the Full and Hourly AWR report, most of the top statements are all CTAS or Insert /*+ APPEND */ as you have suspected and they are leading to direct path with few exception. I am working for those exception SQLs so that REDO Logs can be reduced, will work with you guys if need further support.
    – Suresh Gautam
    Commented Jan 8, 2015 at 5:26
  • It's conformed that there are no issue with #2, #3, #4 and #5, all INSERT statements are direct-path enabled and generating REDO LOGs because of associated foreign key indexes enabled on the target table. Additional Question - any feasibility to bypass for indexes enabled on the target table?
    – Suresh Gautam
    Commented Jan 9, 2015 at 6:16
  • 1
    You can use reference partitioning to avoid the limitations on foreign keys. But that's such an unusual feature it would be a risky workaround. Note that the constraint is more damaging than the index - the foreign key constraint both stops direct path and prevents parallelism. If possible, you can simply drop some foreign key constraints. If you must keep them, it would be faster to drop and re-create them. You can use some tricks like RELY and rebuild to recreate a foreign key constraint in parallel. Indexes can be rebuilt in parallel nologging and switched back to noparallel.
    – Jon Heller
    Commented Jan 9, 2015 at 7:42
  • I learn totally new thing from your last comment though it's feasible to apply in my context immediately. Thanks Again.
    – Suresh Gautam
    Commented Jan 9, 2015 at 8:04
3

If indexes are in place: updates in indexes are always logged.

An other thing is: nologging is only done for statements that copy data.

insert into x values (y,z) will always be logged,

insert into x select y,z from source can be unlogged.

Improve this answer
3
  • Just a comment. Where is also a hint APPEND_VALUES which is intended for the 1st case.
    – ibre5041
    Commented Jan 6, 2015 at 12:13
  • yes, good comment, thanks but append has a way of working which makes this only usable in a for all pl/sql construction. Don't do this with single row inserts; it is intended for bulk operations.
    – user953
    Commented Jan 6, 2015 at 13:40
  • @ik_zelf and ibre5041 - Thanks for adding valuable points.
    – Suresh Gautam
    Commented Jan 9, 2015 at 6:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.