VERY nice bit of code - I only tweeked in a few places - localized the date
formats and set the linesize to 110 to nice up the format. Other than that
brilliant I will use it from now on regularly.
Thanks
--
Kevin A Lewis (BOCM PAULS LTD - Animal Feed Manufacturer - Ipswich England)
<KevinALewis_at_HotMail.com>
The views expressed herein by the author of this document
are not necessarily those of BOCM PAULS Ltd.
<markp7832_at_my-deja.com> wrote in message news:7k88dq$juq$1_at_nnrp1.deja.com...
> 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
> from v$log_history B, v$log_history A
> where (to_char(A.thread#) = '&nv_thread' or '&nv_thread' = 'ALL')
> and A.thread# = B.thread# (+)
> and A.sequence# + 1 = B.sequence# (+);
> set termout on
>
> 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
> ted: ' &nv_rowcnt skip 1 -
> 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
> S')
> )
> - to_date(A.time,'MM/DD/YY HH24:MI:SS'),1
> ) / (1/86400)
> ) / 3600
> ) A_H,
> trunc(mod((mod(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'),1
> ) / (1/86400)
> ), 3600
> ) / 60
> ) A_M,
> trunc(mod(mod((mod(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'),1
> ) / (1/86400)
> ), 3600
> ), 60
> )
> ) A_S
> from v$log_history B, v$log_history A
> where (to_char(A.thread#) = '&nv_thread' or '&nv_thread' = 'ALL')
> and A.thread# = B.thread# (+)
> and A.sequence# + 1 = B.sequence# (+)
> order by &nv_sort_number;
>
> 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 Thu Jun 17 1999 - 06:19:27 CDT
