--
-- Author: Mohit Dubey
-- Visit http://www.geocities.com/md_seraphin for more goodies!
--
-- This program is distributed under the GNU Public License Version 2
-- with the additional privisio that the original author's name and
-- contact details must be retained as-is in any modified or copied
-- versions of this program.
--
set serveroutput on size 1000000 feedback off
col instance new_value V_INSTANCE noprint
select lower(replace(t.instance,chr(0),''))||'.log' instance
from v$thread t,
v$parameter p
where p.name = 'thread'
and t.thread# = to_number(decode(p.value,'0','1',p.value));
spool logswitch_&&V_INSTANCE
declare
--
cursor get_log_sizes
is
select distinct ltrim(to_char(bytes/1048576,'99,990'))||'M' mb
from sys.v_$log;
--
cursor get_log_history
is
select to_char(first_time, 'MM/DD/YY HH24:MI:SS') time,
to_number(substr(to_char(first_time,'MM/DD/YY HH24:MI:SS'), 1, 2)) month,
to_number(substr(to_char(first_time,'MM/DD/YY HH24:MI:SS'), 4, 2)) day,
1900 + to_number(substr(to_char(first_time,'MM/DD/YY HH24:MI:SS'), 7, 2)) year,
to_number(substr(to_char(first_time,'MM/DD/YY HH24:MI:SS'), 10, 2)) hour,
to_number(substr(to_char(first_time,'MM/DD/YY HH24:MI:SS'), 13, 2)) minute,
to_number(substr(to_char(first_time,'MM/DD/YY HH24:MI:SS'), 16, 2)) second,
trunc(first_time - to_date('19700101', 'YYYYMMDD')) days,
to_number(to_char(first_time,'D')) day_of_wk,
(to_number(substr(to_char(first_time,'MM/DD/YY HH24:MI:SS'), 1, 2))*2592000) +
(to_number(substr(to_char(first_time,'MM/DD/YY HH24:MI:SS'), 4, 2))*86400) +
(to_number(substr(to_char(first_time,'MM/DD/YY HH24:MI:SS'), 10, 2))*3600) +
(to_number(substr(to_char(first_time,'MM/DD/YY HH24:MI:SS'), 13, 2))*60) +
to_number(substr(to_char(first_time,'MM/DD/YY HH24:MI:SS'), 16, 2)) seconds
from sys.v_$log_history
order by first_time asc;
--
v_earliest_time varchar2(20) := NULL;
v_prev_seconds number := 0;
v_mins number := 0;
v_totlessthan30 number := 0;
v_total number := 0;
v_first_day number := 0;
v_last_day number := 0;
j binary_integer := 0;
--
type num_tab is table of integer index by binary_integer;
t_days num_tab;
t_days_of_wk num_tab;
t_lessthan1 num_tab;
t_lessthan5 num_tab;
t_lessthan10 num_tab;
t_lessthan20 num_tab;
t_lessthan30 num_tab;
t_totlessthan30 num_tab;
t_load num_tab;
t_cnt num_tab;
v_log_sizes varchar2(200) := null;
--
v_errcontext varchar2(200);
v_errmsg varchar2(200);
--
begin
--
for i in 0..23 loop
--
t_lessthan1(i) := 0;
t_lessthan5(i) := 0;
t_lessthan10(i) := 0;
t_lessthan20(i) := 0;
t_lessthan30(i) := 0;
t_totlessthan30(i) := 0;
t_load(i) := 0;
t_cnt(i) := 0;
t_days_of_wk(i) := 0;
--
end loop;
--
v_errcontext := 'open/fetch get_log_sizes';
for x in get_log_sizes loop
--
if get_log_sizes%rowcount = 1 then
v_log_sizes := x.mb;
else
v_log_sizes := ', ' || x.mb;
end if;
--
v_errcontext := 'fetch/close get_log_sizes';
--
end loop;
--
v_errcontext := 'open/fetch get_log_history';
for x in get_log_history loop
--
if get_log_history%rowcount > 1 then
--
v_mins := (x.seconds - v_prev_seconds) / 60;
--
t_cnt(x.hour) := t_cnt(x.hour) + 1;
--
if v_mins <= 1 then
t_lessthan1(x.hour) := t_lessthan1(x.hour) + 1;
elsif v_mins <= 5 then
t_lessthan5(x.hour) := t_lessthan5(x.hour) + 1;
elsif v_mins <= 10 then
t_lessthan10(x.hour) := t_lessthan10(x.hour) + 1;
elsif v_mins <= 20 then
t_lessthan20(x.hour) := t_lessthan20(x.hour) + 1;
elsif v_mins <= 30 then
t_lessthan30(x.hour) := t_lessthan30(x.hour) + 1;
end if;
--
t_days_of_wk(x.day_of_wk) :=
t_days_of_wk(x.day_of_wk) + 1;
--
begin
t_days(x.days - v_first_day) :=
t_days(x.days - v_first_day) + 1;
exception when no_data_found then
t_days(x.days - v_first_day) := 1;
end;
v_last_day := x.days;
--
else
--
v_earliest_time := x.time;
--
t_cnt(x.hour) := t_cnt(x.hour) + 1;
--
t_days(0) := 1;
v_first_day := x.days;
v_last_day := x.days;
--
end if;
--
v_prev_seconds := x.seconds;
--
v_errcontext := 'fetch/close get_log_history';
--
end loop;
--
for i in 0..23 loop
--
t_totlessthan30(i) :=
t_lessthan1(i) +
t_lessthan5(i) +
t_lessthan10(i) +
t_lessthan20(i) +
t_lessthan30(i);
t_load(i) :=
(t_lessthan1(i) * 10000) +
(t_lessthan5(i) * 1000) +
(t_lessthan10(i) * 100) +
(t_lessthan20(i) * 10) +
t_lessthan30(i);
if t_lessthan1(i) = 0 and
t_lessthan5(i) = 0 and
t_lessthan10(i) = 0 and
t_lessthan20(i) = 0 and
t_lessthan30(i) = 0 then
t_lessthan1(i) := null;
t_lessthan5(i) := null;
t_lessthan10(i) := null;
t_lessthan20(i) := null;
t_lessthan30(i) := null;
elsif t_lessthan1(i) = 0 and
t_lessthan5(i) = 0 and
t_lessthan10(i) = 0 and
t_lessthan20(i) = 0 then
t_lessthan1(i) := null;
t_lessthan5(i) := null;
t_lessthan10(i) := null;
t_lessthan20(i) := null;
elsif t_lessthan1(i) = 0 and
t_lessthan5(i) = 0 and
t_lessthan10(i) = 0 then
t_lessthan1(i) := null;
t_lessthan5(i) := null;
t_lessthan10(i) := null;
elsif t_lessthan1(i) = 0 and
t_lessthan5(i) = 0 then
t_lessthan1(i) := null;
t_lessthan5(i) := null;
elsif t_lessthan1(i) = 0 then
t_lessthan1(i) := null;
end if;
--
end loop;
--
dbms_output.put_line('Log History starts at ' || v_earliest_time);
dbms_output.put_line('Redo log files are sized at: ' || v_log_sizes);
dbms_output.put_line('|');
dbms_output.put_line('| ====== Total Swtchs ===== Tot Swt Redo Load');
dbms_output.put_line('|Hr <1 <5 <10 <20 <30 <30 Tot Factor');
dbms_output.put_line('|-- ---- ---- ---- ---- ---- ---- ---- -----------');
for i in 0..23 loop
--
dbms_output.put_line('|' || ltrim(to_char(i+1,'00')) ||
nvl(to_char(t_lessthan1(i),'9990'),' ') ||
nvl(to_char(t_lessthan5(i),'9990'),' ') ||
nvl(to_char(t_lessthan10(i),'9990'),' ') ||
nvl(to_char(t_lessthan20(i),'9990'),' ') ||
nvl(to_char(t_lessthan30(i),'9990'),' ') ||
to_char(t_totlessthan30(i),'9990') ||
to_char(t_cnt(i), '9990') ||
to_char(t_load(i), '999,999,990'));
--
v_totlessthan30 := v_totlessthan30 + t_totlessthan30(i);
v_total := v_total + t_cnt(i);
--
end loop;
--
dbms_output.put_line('| ---- ----');
dbms_output.put_line('| ' ||
to_char(v_totlessthan30, '9990') ||
to_char(v_total, '9990'));
--
dbms_output.put_line('|');
dbms_output.put_line('| Nbr of');
dbms_output.put_line('|Day of Week Swtchs');
dbms_output.put_line('|----------- ------');
dbms_output.put_line('|Sunday ' || to_char(t_days_of_wk(1), '9990'));
dbms_output.put_line('|Monday ' || to_char(t_days_of_wk(2), '9990'));
dbms_output.put_line('|Tuesday ' || to_char(t_days_of_wk(3), '9990'));
dbms_output.put_line('|Wednesday ' || to_char(t_days_of_wk(4), '9990'));
dbms_output.put_line('|Thursday ' || to_char(t_days_of_wk(5), '9990'));
dbms_output.put_line('|Friday ' || to_char(t_days_of_wk(6), '9990'));
dbms_output.put_line('|Saturday ' || to_char(t_days_of_wk(7), '9990'));
--
dbms_output.put_line('|');
dbms_output.put_line('| Nbr of');
dbms_output.put_line('|Date Swtchs');
dbms_output.put_line('|---- ------');
for i in 0..(v_last_day - v_first_day) loop
--
begin
j := t_days(i);
exception when no_data_found then j := 0;
end;
dbms_output.put_line('|' ||
to_char(to_date('19700101','YYYYMMDD')+v_first_day+i,
'Day MON-DD: ') || to_char(j, '9990'));
--
end loop;
--
exception
when others then
v_errmsg := substr(sqlerrm,1,200);
raise_application_error(-20001, v_errcontext || ': ' ||
v_errmsg);
end;
/
spool off
(
geocities.com/md_seraphin/toolbox) (
geocities.com/md_seraphin)