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
----------------------------------------------------------------
------------
redo synch writes
88,776
redo synch time
1,601,682
redo entries
3,914,890
redo size
############
redo buffer allocation retries
2,038
redo wastage
15,313,112
redo writer latching time
13
redo writes
61,248
redo blocks written
2,972,994
redo write time
811,873
redo log space requests
100
redo log space wait time
5,257
redo log switch interrupts
0
redo ordering marks
218
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: '[EMAIL PROTECTED]'
> Cc: '[EMAIL PROTECTED]'
> Subject: RE: Redo Generation - Excessive ?
>
> Replies BELOW :-
>
> -----Original Message-----
> From: Jared Still [SMTP:[EMAIL PROTECTED]]
> 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
