| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Redo Log Space ??
In article <eDHWgE6t#GA.295_at_cpmsnbbsa02>,
"Brian Peasland" <peasland_at_email.msn.com> wrote:
> >This mainly seems to depend on the insert/update/delete volume of
your
> >database.
> >I usually start with 4 files of 5M each (and mirrored by Oracle, so
each
> >group 2 members, if at all possible [needs to be on a different disk,
> >otherwise it will only delay]) and that very often seems to be
sufficient.
>
> I read somewhere (although I can't remember where) that in a OLTP
> environment, it is suggested that the redo logs be of sufficient size
so
> that they fill up and switch every 30 minutes (on average).
>
Oracle support once recommended to me trying to average one archieved
log per hour or 24 logs per day. But I think the size you want is the
size where the redo log is big enough to support your heavy update time
period without excessive switching and small enough that the time to
read and reapply activity after a crash is acceptable. If your redo
logs are too small they can have an adverse effect on system wide
performance. Once you increase their size past this point it really
becomes a matter of personal preference.
Here is some SQL posted by B. Mclean last Jan. that will calculate your time between switches so that you can adjust your log sizes: (Note I had to cut and paste this in pieces so if it takes a syntax error and the fix is not obvious e-mail me and I will e-mail it.)
set echo off
rem
rem SQL*Plus script to calculate and display time between redo log
switches
rem
rem 19990125 Mark D Powell Script from newsgroup post by Brian P.
Mclean
rem 19990607 Mark D Powell expand change numbers
rem
ttitle off
set pagesize 24
set linesize 90
set pause off
set termout off
set feedback off
set verify off
col today_dt new_value nv_today_dt noprint select to_char(sysdate, 'YYYYMMDD HH24:MI:SS') today_dt from dual;
set termout on
ACCEPT USER_INPUT1 CHAR PROMPT 'Please enter a Thread# or leave blank
to see all
Threads:>'
et termout off
col v_thread new_value nv_thread noprint select nvl('&USER_INPUT1','ALL') v_thread from dual;
col v_rowcnt new_value nv_rowcnt noprint
col v_avg new_value nv_avg noprint
select count(*) v_rowcnt,
'Average Time Between Log Switches is ' ||
trunc(avg(decode(B.time,NULL,sysdate,to_date(B.time,'MM/DD/YY
HH24:MI:SS'
)
- to_date(A.time,'MM/DD/YY HH24:MI:SS')
)
) || ' Days ' ||
trunc((mod(avg(decode(B.time,NULL,sysdate,
to_date(B.time,'MM/DD/YY HH24:MI:SS')
) - to_date(A.time,'MM/DD/YY HH24:MI:SS')
),1
) / (1 / 86400)
) / 3600
) || ' Hours ' ||
trunc(mod((mod(avg(decode(B.time,NULL,sysdate,
to_date(B.time,'MM/DD/YY HH24:MI:SS')
)
- to_date(A.time,'MM/DD/YY HH24:MI:SS')
),1
) / (1/86400)
), 3600
) / 60
) || ' Minutes ' ||
trunc(mod(mod((mod(avg(decode(B.time,NULL,sysdate,
to_date(B.time,'MM/DD/YY HH24:MI:SS')
)
- to_date(A.time,'MM/DD/YY HH24:MI:SS')
),1
) / (1/86400)
), 3600
), 60
)
) || ' Seconds' v_avg
PROMPT
PROMPT Sort BY Selections
PROMPT ------------------------
PROMPT 1 = First Write Time
PROMPT 2 = Thread, First Write Time
ACCEPT USER_INPUT2 NUMBER PROMPT 'Please enter one now:>'
set termout off
col v_sort_number new_value nv_sort_number noprint col v_sort_text new_value nv_sort_text noprint
select decode(&USER_INPUT2, 1,'2', 2,'1,2', '2') v_sort_number,
decode(&USER_INPUT2, 1,'First Write Time',
2,'Thread, First Write Time',
'First Write Time') v_sort_text
from dual;
ttitle left '&nv_today_dt ' -
center 'Redo Log Switching/Archive Log Generation History' -
right 'Page:' format 999 SQL.PNO skip 1 -
left SQL.USER -
center 'Selected Thread: &nv_thread Number of Log History
Rows Selec
center '&nv_avg' skip 1 -
center 'Sort By : &nv_sort_text' skip 2
col THREAD# format 999999
col SEQUENCE# format 999999
col TIME format a20 heading 'FIRST|WRITE TIME'
col LOW_CHANGE# format 9999999999 heading 'LOW|CHANGE#'
col HIGH_CHANGE# format 9999999999 heading 'HIGH|CHANGE#' col A_D format 999 heading 'ACTIVE|DAYS' col A_H format 99 heading 'ACTIVE|HOURS' col A_M format 99 heading 'ACTIVE|MINUTES' col A_S format 99 heading 'ACTIVE|SECONDS'
break on THREAD# nodup
set termout on
set pause on
set pause 'Hit Enter To Continue.....'
select A.THREAD#, A.SEQUENCE# , A.TIME, A.LOW_CHANGE#, A.HIGH_CHANGE#,
trunc(decode(B.time,NULL,sysdate,to_date(B.time,'MM/DD/YY
HH24:MI:S
S')
)
- to_date(A.time,'MM/DD/YY HH24:MI:SS')
) A_D,
trunc((mod(decode(B.time,NULL,sysdate,to_date(B.time,'MM/DD/YY
HH24:MI:S
)
- to_date(A.time,'MM/DD/YY HH24:MI:SS'),1
) / (1/86400)
) / 3600
) A_H,
)
- to_date(A.time,'MM/DD/YY HH24:MI:SS'),1
) / (1/86400)
), 3600
) / 60
) A_M,
)
- to_date(A.time,'MM/DD/YY HH24:MI:SS'),1
) / (1/86400)
), 3600
), 60
)
) A_S
clear col
clear break
ttitle off
set pause off
--
Mark D. Powell -- The only advice that counts is the advice that
you follow so follow your own advice --
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
Received on Wed Jun 16 1999 - 08:22:13 CDT
 |
 |