This content has been marked as final. Show 6 replies
You can't just turn off logging for a process but you can reduce log generation by using "nologging" mode on some operations.
MOS [ID 188691.1] How to Avoid Generation of Redolog Entries
MOS [ID 290161.1] The Gains and Pains of Nologging Operations
Based on your description, it sounds like you may bedoing lots of row by row processing, possibly with commits scattered around during the processing of each of the reporting tables. Something like:
If that is your processing, then you need to look at changing the row by row processing to set based processing and only commit once at the end of each logical transaction.
FOR r in cursor LOOP UPDATE table SET column = cursor.column WHERE other_column = cursor.other_column; rows_update := rows_update + 1; IF rows_update > some_value THEN COMMIT; rows_updated := 0; END IF; END LOOP;
First of all let me say that if the redo logs generated by an application is greather then the whole db probably there's some tuning to do in the application.
The redo logs don't store the data you change but the sql statements you issue...
Are you sure you cannot change the application to run less sql statements (for example replacing some INSERT...VALUES with an equivalent INSERT...SELECT)?
Anyway, if you can change your inserts to direct-path inserts (using the /*+ APPEND */ hint) you can turn off logging on these reporting tables
All direct-path inserts won't be logged...
ALTER TABLE MY_REPORTING_TABLE NOLOGGING;
actually the code definitely needs tunings. It has been written by someone, who already left the company and the documentation is pretty questionable. It is hard to touch it without risking getting some unexpected results. All the same, based on what you wrote, I think it is unavoidable.
Thank you for the tips and help.