| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Redo Generation - Excessive ?
NOTE - SQL Run AFTER Completion of Batch Processing Run BUT Before the Bouncing of Database :-
SQL> column value format 999,999,990
SQL> select a.name,b.value
2 from v$statname a,v$sysstat b
3 where a.statistic# = b.statistic#
4 and a.name like '%redo%'
5 /
NAME
VALUE
14 rows selected.
Qs. Are Any of the Above Values ABNORMAL ? What may be Done , if any to address the Same ?
> -----Original Message-----
> From: VIVEK_SHARMA
> Sent: Monday, August 06, 2001 2:38 PM
> To: 'jkstill_at_cybcon.com'
> Cc: 'ORACLE-L_at_fatcity.com'
> Subject: 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'?
>
Ans Bank Interest Calc. Job doing DMLs to Most A/cs
> As in how many rows are affected?
>
Ans 1784813 rows may be Affected ( By DMLs )
> How big are the transactions?
>
Ans Average Row Size for Most Tables = 200 Bytes
> Updates and deletes?
>
Ans 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.
>
Ans 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*2948
> 9)+(144*21953)+(53*21953)+(180*21953)+(106*21953)+(92*104989)+(387*219
> 53)= 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).Received on Thu Aug 09 2001 - 13:00:37 CDT
 |
 |