Reducing REDO log churing
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