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:
- 1st hour AWR showing 15GB per second and 131K per transaction in the
Load Profile. - 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:
- It's known that dictionary specific tablespace is forced for
logging. - Considering that
NOLOGGING
is not applicable by any mean to any
UPDATE operations resulting from theMERGE
statement. - 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:
- Can anyone see any other considerations beyond
1, 2 and 3
, for generating such big volume REDOs? - 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. - 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.
Best Answer
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 anINSERT
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 usingselect * from table(dbms_xplan.display_awr(sql_id => 'sql_id from AWR'));
If the execution plan saysLOAD AS SELECT
then it's using direct path inserts and is OK. If it saysLOAD 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 theINSERT
. IndexNOLOGGING
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:/* +
. Or if the hint is placed in a slightly wrong position, like thisINSERT INTO /*+ append */ ...
.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.