Re: Redo Log Space ??

From: Erik <ecotsonas_at_saraswati.com>
Date: Thu, 17 Jun 1999 21:01:58 GMT
Message-ID: <7kbnnu$uj3$1@nnrp1.deja.com>

In article <7kbci3$pp8$1_at_nnrp1.deja.com>, markp7832_at_my-deja.com wrote:
> Warning the code I posted to calculate the time between log switches
is
> for version 7.3 as v$log_history was changed in ver. 8. Three of the
> columns changed names, time => first_time, low_change => first_change,
> high_change => next_change and first_time is now a date column type
> instead of a varchar2. When I can I will try to rework the code. If
> someone else beats me to it, please post the fix.
>
> --
> 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.
>

Ok, I think that I have hacked it properly to work with Oracle8. Note that this script ONLY works with Oracle8. (By the way, I have over 57,000 record in my v$log_history from a large update that we ran and the log was switching every second or so. Does the v$log_history ever get cleared out?)

Oracle8 Version:

----------------  cut here ----------------------------

set echo off
rem
rem SQL*Plus script to calculate and display first_time between redo log switches
rem
rem NOTE: ORACLE VERSION 8 ONLY
rem
rem 19990125 Mark D Powell Script from newsgroup post by Brian P. Mclean

rem  19990607  Mark D Powell     expand change numbers
rem  19990616  Kevin A Lewis     Minor formatting changes and

localization of dates
rem 19990617 Erik C Cotsonas Modified to work with Version 8

rem                              1.  Change for v$log_history
rem                                    time => first_time
rem                                    next_change => first_change#
rem                                    high_change => next_change#
rem                              2.  First_time is now a date column
type instead
rem                                  of a varchar2 (removed to_date)

rem

ttitle off
set pagesize 24
set linesize 110
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, 'DD Mon YYYY 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:>'
set 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 first_time Between Log Switches is ' ||
       trunc(avg(decode(B.first_time,NULL,sysdate,B.first_time)
                       - A.first_time
                  )
              ) || ' Days ' ||
       trunc((mod(avg(decode(B.first_time,NULL,sysdate,
                      B.first_time
                             ) - A.first_time
                     ),1
                 ) / (1 / 86400)
              ) / 3600
            ) || ' Hours ' ||
       trunc(mod((mod(avg(decode(B.first_time,NULL,sysdate,
                 B.first_time
                         )
                         - A.first_time
                      ),1
                  ) / (1/86400)
             ), 3600
            ) / 60
         ) || ' Minutes ' ||
       trunc(mod(mod((mod(avg(decode(B.first_time,NULL,sysdate,
                              B.first_time
                             )
                           - A.first_time
                     ),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 first_time
PROMPT 2 = Thread, First Write first_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 first_time',
                            2,'Thread, First Write first_time',
                            'First Write first_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 Selected: ' &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 FIRST_TIME         format a20    heading   'FIRST|WRITE TIME'

col FIRST_CHANGE# format 9999999999 heading 'LOW|CHANGE#'

col NEXT_CHANGE# format 9999999999 heading 'NEXT|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# , to_char(A.first_time,'MM/DD/YYYY HH24:MI:SS') time, -

			A.first_change#, A.NEXT_CHANGE#,
             trunc(decode(B.first_time,NULL,sysdate,B.first_time
                         )
                       - A.first_time
                  ) A_D,
        trunc((mod(decode(B.first_time,NULL,sysdate,B.first_time
                         )
                       - A.first_time,1
                  ) / (1/86400)
              ) / 3600
             ) A_H,
    trunc(mod((mod(decode(B.first_time,NULL,sysdate,B.first_time
                         )
                       - A.first_time,1
                  ) / (1/86400)
              ), 3600
             ) / 60
         ) A_M,
trunc(mod(mod((mod(decode(B.first_time,NULL,sysdate,B.first_time
                         )
                       - A.first_time,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

cut here ----------------

Erik

--
Consultant
Saraswati Systems Corporation - (SSC)

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Jun 17 1999 - 16:01:58 CDT