6 Replies Latest reply: Jun 8, 2011 1:40 PM by 843389 RSS

    How not to generate archive logs for database jobs

    TamasSzecsy
      Hi,

      I have a 10g R2 EE RDBMS with archive log 'ON'. In the database I have a job that runs every two hours and executes a procedure. This procedure makes some heavy processing (lots of select statements) and during this process it refreshes the content of 30 or so reporting tables.

      My problem is that during this process 50 GB (!) of archive log is generated, that is double the size of the database's actual valuable data content size.

      On the other hand I would happily switch redo log generation off for this process. These reporting tables are not valuable at all in the sense that should the database instance crash, I could recreate these tables and by executing the named process I could reproduce this information any time.

      Is it possible to force Oracle not to generate redo log for this job?

      Regards, Tamas
        • 1. Re: How not to generate archive logs for database jobs
          GokhanAtil
          Hello Tamas,

          You can't just turn off logging for a process but you can reduce log generation by using "nologging" mode on some operations.

          Please read:
          MOS [ID 188691.1] How to Avoid Generation of Redolog Entries
          MOS [ID 290161.1] The Gains and Pains of Nologging Operations
          http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#sthref2199

          Regards

          Gokhan Atil
          • 2. Re: How not to generate archive logs for database jobs
            TamasSzecsy
            Hello Gokhan,

            It does not help me, but at least I know it is 'normal'. Thank you.

            Regards, Tamas
            • 3. Re: How not to generate archive logs for database jobs
              John Spencer
              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:
              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;
              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.

              John
              • 4. Re: How not to generate archive logs for database jobs
                730428
                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
                ALTER TABLE MY_REPORTING_TABLE NOLOGGING;
                All direct-path inserts won't be logged...

                Max
                • 5. Re: How not to generate archive logs for database jobs
                  TamasSzecsy
                  John, Massimo,

                  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.

                  Regards, Tamas
                  • 6. Re: How not to generate archive logs for database jobs
                    843389
                    becareful with no logging in certain cases such as active clones it can cause block corruption.