Reducing REDO log churing

From: Gregory P Lechkun <lechkung_at_dteenergy.com>
Date: Fri, 11 Feb 2000 16:51:16 -0500
Message-ID: <38A48454.E750881A_at_dteenergy.com>



Here's a challenge (especially for data modelers),

Question:
How can I reduce the loading on my DB redo logs and improve performance?

Background:
I have a database that's loading realtime data in a couple tables my main data tablespace (there is also an index in an index tablespace).  Originally, it was built to 1)delete the table, then 2)insert 100,000+ rows of data every minute.  You can imagine what this was doing to the redo logs!   In our second implementation, we deadbanded the data (if it didn't change by .1% we didn't update it).  This second implementation does a full flush every 60 minutes (the two steps in first implementation), and an update on the table every 30 seconds (which is approximately 20,000 rows every 30 seconds).   Now with this second implementation, we're seeing switching of the redo logs every 5-7 minutes (3 redo log files sized as 62MB each).

As you can probably guess, we are not archiving, which I think is a problem in itself.

Now, the data we're loading isn't important enough for us to keep (remember, in the original implementation it was being replace every minute).  However, in the future we will be moving a copy of the data to another table every 15 minutes (that data we will want to log and keep data consistency).

Solution:
My thought is that, the tables that are being load/updated with the realtime data every 30-60 seconds should be put into a tablespace that was created with the  NOLOGGING parameter.  This way any data changes will no longer be logged in the redo log, and I won't have my redo logs churning away anymore.

Question:
If the above solution works, should I put the index in a tablespace that was created the NOLOGGING parameter?

Does someone have any comments for this solution?  (all welcomed)

--
Regards,
Greg Lechkun
gpl :-)
 
DTE Energy====================================================================
DTE     ////// ////////////       Gregory Lechkun - EM&D/PDO-PT
DTE    //   //  //   //           Power Application Software Engineer
DTE   //   //  //   ////          lechkung_at_detroitedison.com
DTE  //   //  //   //             g.p.lechkun_at_ieee.org (personal)
DTE //////   //   //////          (313)235-9445 Office   (313)940-3306 Pgr.
                                  (313)235-8597 FAX
  Received on Fri Feb 11 2000 - 22:51:16 CET

Original text of this message