Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Thursday, 3 May 2012

Script: Track Redo Generation

While Google I found some useful script to Track Redo Generation. Thanks to Govinsoorma - Oracle DBA - Tips and Technique blog for sharing these useful script.

Script - Track Redo Generation by Calender Year 
select to_char(first_time,'mm.DD.rrrr') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
from v$log_history group by to_char(first_time,'mm.DD.rrrr')
order by day
/
Script- Track Redo generation by day
select trunc(completion_time) logdate, count(*) logswitch, round((sum(blocks*block_size)/1024/1024)) "REDO PER DAY (MB)"
from v$archived_log
group by trunc(completion_time)
order by 1
/
Script - Track Redo Log File Switches – By hour of the day
prompt
prompt “Morning ……….”
select to_char(first_time,’DD/MON’) day,
to_char(sum(decode(to_char(first_time,’HH24?),’07?,1,0)),’000?)”07?,
to_char(sum(decode(to_char(first_time,’HH24?),’08?,1,0)),’000?)”08?,
to_char(sum(decode(to_char(first_time,’HH24?),’09?,1,0)),’000?)”09?,
to_char(sum(decode(to_char(first_time,’HH24?),’10?,1,0)),’000?)”10?,
to_char(sum(decode(to_char(first_time,’HH24?),’11?,1,0)),’000?)”11?,
to_char(sum(decode(to_char(first_time,’HH24?),’12?,1,0)),’000?)”12?,
to_char(sum(decode(to_char(first_time,’HH24?),’13?,1,0)),’000?)”13?,
to_char(sum(decode(to_char(first_time,’HH24?),’14?,1,0)),’000?)”14?,
to_char(sum(decode(to_char(first_time,’HH24?),’15?,1,0)),’000?)”15?,
to_char(sum(decode(to_char(first_time,’HH24?),’16?,1,0)),’000?)”16?,
to_char(sum(decode(to_char(first_time,’HH24?),’17?,1,0)),’000?)”17?,
to_char(sum(decode(to_char(first_time,’HH24?),’18?,1,0)),’000?)”18?
from v$log_history
WHERE TRUNC(FIRST_TIME) > TRUNC(SYSDATE) – 7
group by to_char(first_time,’DD/MON’);
prompt
prompt
Prompt “Evening ……..”
prompt
select to_char(first_time,’DD/MON’) day,
to_char(sum(decode(to_char(first_time,’HH24?),’19?,1,0)),’000?)”19?,
to_char(sum(decode(to_char(first_time,’HH24?),’20?,1,0)),’000?)”20?,
to_char(sum(decode(to_char(first_time,’HH24?),’21?,1,0)),’000?)”21?,
to_char(sum(decode(to_char(first_time,’HH24?),’22?,1,0)),’000?)”22?,
to_char(sum(decode(to_char(first_time,’HH24?),’23?,1,0)),’000?)”23?,
to_char(sum(decode(to_char(first_time,’HH24?),’00?,1,0)),’000?) “00?,
to_char(sum(decode(to_char(first_time,’HH24?),’01?,1,0)),’000?)”01?,
to_char(sum(decode(to_char(first_time,’HH24?),’02?,1,0)),’000?)”02?,
to_char(sum(decode(to_char(first_time,’HH24?),’03?,1,0)),’000?)”03?,
to_char(sum(decode(to_char(first_time,’HH24?),’04?,1,0)),’000?)”04?,
to_char(sum(decode(to_char(first_time,’HH24?),’05?,1,0)),’000?)”05?,
to_char(sum(decode(to_char(first_time,’HH24?),’06?,1,0)),’000?)”06?
from v$log_history
WHERE TRUNC(FIRST_TIME) > TRUNC(SYSDATE) – 7
group by to_char(first_time,’DD/MON’)
/

0 comments:

Post a Comment