| 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 ?
I monitor the log switch rate - the average time between log switches over 4 to 24 hours. I like 30+ minutes, 15 is the low end for me.
I rebuild indexes with logging set off to minimize log data there too.
Cheers,
Earl
--- TheOracleDBA theoracledba_at_lycos.com On Thu, 09 Aug 2001 08:55:14 Jared Still wrote:Received on Thu Aug 09 2001 - 20:03:14 CDT
>
>Vivek,
>
>Often the only way to tell if a given set of statistics
>is abnormal is to compare it to the historical record.
>
>These vary by application and it's very difficult to
>look at a set of statistics and say it's out of line.
>
>There are exceptions of course, but most of us don't
>spend a lot of time looking at redo stats unless we
>suspect they are causing a performance problem.
>
>Are you experiencing a performance problem, and do
>you suspect the redo?
>
>If not, don't worry about it.
>
>Jared
>
>
>On Thursday 09 August 2001 02:15, VIVEK_SHARMA wrote:
>> 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: '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: Jared Still
> INET: jkstill_at_cybcon.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).
>
Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: The Oracle DBA INET: theoracledba_at_lycos.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).
 |
 |