RE: Redo Generation - Excessive ?

Replies BELOW :-

> -----Original Message-----
> From: Jared Still [SMTP:jkstill_at_cybcon.com]
> Sent: Saturday, August 04, 2001 12:05 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Redo Generation - Excessive ?
>
>
> Vivek,
>
> You left a few things out.
>
> What's 'intensive'?
>

        Bank Interest Calc. Job doing DMLs to Most A/cs

> As in how many rows are affected?
>

        1784813 rows may be Affected ( By DMLs )

> How big are the transactions?
>

        Average Row Size for Most Tables = 200 Bytes

> Updates and deletes?
>

        Primarily UPDATES , Inserts 
        Deletes Only to 1 Table having 21953 Records 

> 11 gig doesn't sound like all that much to me
> if it's you're doing massive dml.
>
> I had 1 gig logs on a similar hardware setup on a DW
> that would easily generate 18 gig in 6 hours.
>

        Application if a BANKING Product though this particular Operation is Batch Processing (Interest Calculation ) Intensive

> ( Yes, archiving was on. Don't ask! )
>

        Qs. Can Number of Log switches in alert_SID.log be Taken as Equal to the Archived Redo that would be Generated with Archiving On ?

        Look BELOW for FUTHER Details

> Jared
>
>

16 Redo Log Switches Occured ( In alert_SID.log) from Sequence 30 to 46 ( During Interest Run Batch Processing )

• Considering ONLY Tables to which INSERT Operations Occur (Assuming they will generate the MAX. Redo OR IS THIS ASSUMPTION WRONG ? ) from the Above Set .

(231*104989)+(95*104989)+(79*104989)+(209*21953)+(136*104989)+(98*29489) +(144*21953)+(53*21953)+(180*21953)+(106*21953)+(92*104989)+(387*21953)= 93,035,246 Bytes

NOTE Thus There Seems to be a VAST Gap Between the Number of Log Switches = 16 Corresponding to (16 * 175M) = 2.8 GB & 93MB of Calculated for Inserts Size

Type & Number of Bank A/cs :-

Types COUNT(*)
---- ----------

CAA       9780
LAA      29489
ODA        899
SBA      42868
TDA      21953

Total Number of A/cs = 104,989

List of Tables on which DMLs Happen :-

TABLE_NAME                     AVG_ROW_LEN
------------------------------ -----------
Tables COMMON to ALL Types of A/cs ( CAA , LAA , ODA , SBA , TDA ) :-
DAILY_TRAN_DETAIL_TABLE                231
DAILY_TRAN_HEADER_TABLE                 95
TEMP_DAILY_TRAN_TABLE                   92
ENTITY_INTEREST_TABLE                  209
EOD_ACCT_BAL_TABLE                      79
GENERAL_ACCT_MAST_TABLE                387
INTEREST_DETAILS_TABLE                 136

Only Updates Happen to these 2 Tables :-
INTEREST_SUMMARY_MOD_TABLE              74
INTEREST_SUMMARY_TABLE                  85

Tables Additionally Existing in LAA Type :-
LA_ACCT_MAST_TABLE                     194
LA_DMD_ADJ_TABLE                        81
LA_DMD_TABLE                            98

Tables Additionaly Existing in TDA Type 
TD_ACCT_MASTER_TABLE                   180
TD_DEFN_TABLE                          106
TAX_DED_AT_SOURCE_TABLE                144
TDS_INTERFACE_TABLE                     53

16 rows selected.

> On Friday 03 August 2001 14:28, VIVEK_SHARMA wrote:
> > While Running a Set of BATCH Processing JOBs , Archived Redo of 11
> GB is
> > being Generated over a 6 Hours Run on a E6500 Server .
> > NOTE - The batch jobs are DML intensive in nature indeed .
> >
> > Online Redo Logfile Size = 175 MB
> > LOG_CHECKPOINT_INTERVAL= 400000
> >
> > Qs Is there any way to know whether this Redo Generation is NORMAL
> or
> > ABNORMALLY High in some manner ?
> >
> > Oracle 8.1.7 on Solaris 8
> >
> > Prima facie it seems Exceedingly High
>

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: VIVEK_SHARMA
  INET: VIVEK_SHARMA_at_infy.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).