redoinfo.sql

---*****************************

---*  Commits Class Info       *

---*  By Eini Trujillo. Vzla   *

---*  July 2014                *

---* Created : 12/08/2014      *

---* Updated : 02/09/2014      *
---* version 1.01              *

---*****************************

PROMPT

PROMPT ----- Redo Log Files

SET LIN 120

set verif off

set echo off

SET FEEDBACK off

set timing off

set time off

COL MEMBER FOR A64 HEADING "Redo Log File"

COL WARNING FOR A11  HEADING "Multiplexed" JUS CENTER

COL DAY FOR A12

COL Max_Redo_perHour FOR 9999 HEADING "Max. RedoLog Switch|per  Hour"

COL Avg_Redo_perHour FOR 9999 HEADING "Avg. RedoLog Switch|per Hour"

COL Avg_Min_Switch FOR 9999 HEADING "Avg. RedoLog Switch Time|(Min)"

COL Size_MB FOR 999999 HEADING "Size MB"

COL Avg_Redo_perDay FOR 999,999 HEADING "Avg. RedoLog| Switch per Day"  JUS CENTER

COL Avg_SizeDay FOR 999999 HEADING "Size MB of Redolog | generated per Day"  JUS CENTER

COL EVENT for a24    

COL Total_Waits FOR 999,999,999,999,999 HEADING "Total Waits"

COL Time_Waited FOR 999,999,999,999,999 HEADING "Time Waited"

COL Total_Timeouts FOR 999,999,999,999,999 HEADING "Total Timeouts"

COL SECONDS_IN_WAIT FORMAT 9,999 heading "Second|in Wait"   

COL WAIT_TIME FORMAT 9,999 heading "Wait| Time"   

col sid for 99999

col P1 for 999,999

col P2 for 999,999

col P3 for 99999

col P1TEXT for a9

col P2TEXT for a7

col P3TEXT for a11

col EVENT for a24    

col STATE for a18 HEADING "State"

COL name FOR a32   

COL CALLS FOR 999,999,999,999,999 HEADING "Calls"

COL ROLLBACKS FOR 999,999,999,999,999 HEADING "Transaction|rollbacks"

COL COMMITS FOR 999,999,999,999,999 HEADING "User|Commits"

COL urollbacks FOR 999,999,999,999,999 HEADING "User|Rollbacks"  JUS CENTER

COL Avg_UC FOR 999,999.99 HEADING "Avg. User Calls | Rollbacks + Commits"  JUS CENTER

COL Redo_Buffer FOR 999,999,999 HEADING "Avg."

COL USERNAME FOR A24 HEADING "Username"

COL MACHINE FOR A24 HEADING "Machine"

COL PROGRAM FOR A36 HEADING "Program"

column tm new_value file_time noprint

select to_char(sysdate, 'YYYYMMDDHH24MISS') tm from dual ;

spool infoRedo_&file_time..log

PROMPT

PROMPT --- Configuration

----The size of the Redo log buffer is determined by the LOG_BUFFER parameter in your SPFILE/INIT.ORA file.

----The default setting is normally 512 KB or (128 KB * CPU_COUNT)

PROMPT -- LOG_BUFFER : The default setting is (128 KB * CPU_COUNT)

show parameter log_buffer

show parameter cpu_count

PROMPT

archive log list

PROMPT

PROMPT ---- Redo Log Files

select (SELECT I.INSTANCE_NAME FROM GV$INSTANCE I WHERE I.INST_ID=L.INST_ID) "Nodo",GROUP#,MEMBER

from gv$logfile  L

order by L.INST_ID, GROUP#;

PROMPT

PROMPT

PROMPT ---- Redo Log Group

select (SELECT I.INSTANCE_NAME FROM GV$INSTANCE I WHERE I.INST_ID=L.INST_ID) "Nodo", GROUP#,(BYTES/1024/1024) Size_MB,MEMBERS "Members",STATUS "Status",

decode(members,1,'* NO ','  YES ') Warning

from gv$log L

order by L.INST_ID, GROUP#;

PROMPT

PROMPT --- Behavior of Redo Log File

PROMPT

PROMPT ---- Max. y Avg. Redo Log Files generated per Hour

--- Document:602066.1 How To Maintain and/or Add Redo Logs

--- Document:779306.1 How To Add/Increase The Size Of Redo Log Files In Rac Environment?

select MAX(CNT) Max_Redo_perHour,ROUND(AVG(CNT)) Avg_Redo_perHour, ROUND(60/AVG(CNT)) Avg_Min_Switch FROM

(select to_char(first_time,'YYYYMMDDHH24'),COUNT(1) Cnt

from v$log_history

where FIRST_TIME < trunc(sysdate)

group by to_char(first_time,'YYYYMMDDHH24'));

PROMPT

PROMPT

PROMPT ---- Avg. Archive generated per Day (Size MB)

select lh.Avg_Redo_perDay,  ROUND(lh.Avg_Redo_perDay*l.SIZE_MB) Avg_SizeDay

from

(select AVG(CNT) Avg_Redo_perDay FROM

(select to_char(first_time,'YYYYMMDD') DAY,COUNT(1) Cnt

from v$log_history

where FIRST_TIME < trunc(sysdate)

group by to_char(first_time,'YYYYMMDD'))) lh,

(select AVG(BYTES/1024/1024) size_mb from v$log) l;

SET PAGES 999;

SET NUMWIDTH 3

PROMPT

PROMPT

PROMPT ---- Switch Map of Redo Log

select to_char(FIRST_TIME,'yyyy-mm-dd') day,

sum(decode(to_char(FIRST_TIME,'HH24'),'00',1,0)) "00",

sum(decode(to_char(FIRST_TIME,'HH24'),'01',1,0)) "01",

sum(decode(to_char(FIRST_TIME,'HH24'),'02',1,0)) "02",

sum(decode(to_char(FIRST_TIME,'HH24'),'03',1,0)) "03", 

sum(decode(to_char(FIRST_TIME,'HH24'),'04',1,0)) "04",

sum(decode(to_char(FIRST_TIME,'HH24'),'05',1,0)) "05",

sum(decode(to_char(FIRST_TIME,'HH24'),'06',1,0)) "06",

sum(decode(to_char(FIRST_TIME,'HH24'),'07',1,0)) "07",

sum(decode(to_char(FIRST_TIME,'HH24'),'08',1,0)) "08",

sum(decode(to_char(FIRST_TIME,'HH24'),'09',1,0)) "09",

sum(decode(to_char(FIRST_TIME,'HH24'),'10',1,0)) "10",

sum(decode(to_char(FIRST_TIME,'HH24'),'11',1,0)) "11",

sum(decode(to_char(FIRST_TIME,'HH24'),'12',1,0)) "12",

sum(decode(to_char(FIRST_TIME,'HH24'),'13',1,0)) "13",

sum(decode(to_char(FIRST_TIME,'HH24'),'14',1,0)) "14",

sum(decode(to_char(FIRST_TIME,'HH24'),'15',1,0)) "15",

sum(decode(to_char(FIRST_TIME,'HH24'),'16',1,0)) "16",

sum(decode(to_char(FIRST_TIME,'HH24'),'17',1,0)) "17",

sum(decode(to_char(FIRST_TIME,'HH24'),'18',1,0)) "18",

sum(decode(to_char(FIRST_TIME,'HH24'),'19',1,0)) "19",

sum(decode(to_char(FIRST_TIME,'HH24'),'20',1,0)) "20",

sum(decode(to_char(FIRST_TIME,'HH24'),'21',1,0)) "21",

sum(decode(to_char(FIRST_TIME,'HH24'),'22',1,0)) "22",

sum(decode(to_char(FIRST_TIME,'HH24'),'23',1,0)) "23",

COUNT(to_char(FIRST_TIME,'yyyy-mm-dd')) "Total"

from v$log_history
where thread# =(select THREAD# from v$instance)

group by ROLLUP(to_char(FIRST_TIME,'yyyy-mm-dd'))

order by to_char(FIRST_TIME,'yyyy-mm-dd');

SET NUMWIDTH 12

PROMPT

PROMPT

PROMPT -- Total Waits Events

SELECT event,  Total_Waits, Time_Waited, Total_Timeouts,AVERAGE_WAIT

FROM  v$system_event WHERE event IN

('log file sync','log file parallel write','log buffer space')

order by 1;

PROMPT

PROMPT

PROMPT -- On Line Wait Event

set lin 134

select SID,EVENT,SEQ#,P1TEXT,P1,P2TEXT,P2,P3TEXT,P3,WAIT_TIME, SECONDS_IN_WAIT, STATE    

from V$SESSION_WAIT

WHERE event IN

('log file sync','log file parallel write','log buffer space')

order by 1;

set lin 120

---- "On systems with fast processors but relatively slow disks"

----then processes have had to wait for space in the redo log buffer. The wait can be caused by the log buffer being too small or by checkpointing.

--- The number of times a process has to wait for space in the log buffer

PROMPT

PROMPT

PROMPT  --- Numeber of Wait for space in the log buffer

select   t.VALUE Redo_Buffer

from v$sysstat t,V$STATNAME n

where t.STATISTIC# = n.STATISTIC# 

and  n.name in ('redo buffer allocation retries');

exec sys.DBMS_LOCK.SLEEP(5);

select   t.VALUE Redo_Buffer

from v$sysstat t,V$STATNAME n

where t.STATISTIC# = n.STATISTIC# 

and  n.name in ('redo buffer allocation retries');

PROMPT

PROMPT

PROMPT --- Statistics

PROMPT -- Avg. User Calls/ (Rollbacks + Commits) should be greater than 30

select uc.calls, ur.urollbacks, r.rollbacks, c.commits, (uc.calls/( r.rollbacks + c.commits)) Avg_UC,  round(r.rollbacks/c.commits,2) Roll_NComm

from

(select   t.VALUE rollbacks

from v$sysstat t,V$STATNAME n

where t.STATISTIC# = n.STATISTIC# 

and  n.name in ('transaction rollbacks') )r,

(select   t.VALUE calls

from v$sysstat t,V$STATNAME n

where t.STATISTIC# = n.STATISTIC# 

and  n.name in ('user calls')) uc,

(select   t.VALUE commits

from v$sysstat t,V$STATNAME n

where t.STATISTIC# = n.STATISTIC# 

and  n.name in ('user commits')) c,

(select   t.VALUE urollbacks

from v$sysstat t,V$STATNAME n

where t.STATISTIC# = n.STATISTIC# 

and  n.name in ('user rollbacks')) ur;

PROMPT

PROMPT

PROMPT --- Transaction Rollbacks per Program

SELECT * FROM (select  s.username,s.program,s.machine, SUM(t.VALUE) ROLLBACKS

from v$sesstat t,V$STATNAME n, v$session s

where t.STATISTIC# = n.STATISTIC#

and  s.sid = t.sid 

and  n.name in ('transaction rollbacks')  and t.VALUE>0

group by s.username,s.program,s.machine

order by  SUM(t.VALUE) desc) WHERE ROWNUM<=5;

PROMPT

PROMPT

PROMPT --- User Commits per Program

SELECT * FROM (select  s.username,s.program,s.machine, SUM(t.VALUE) COMMITS

from v$sesstat t,V$STATNAME n, v$session s

where t.STATISTIC# = n.STATISTIC#

and  s.sid = t.sid 

and  n.name in ('user commits')  and t.VALUE>0

group by s.username,s.program,s.machine

order by  SUM(t.VALUE) desc) WHERE ROWNUM<=5;

spool off;