LOGGING or NOLOGGING, that is the question – Part VIII

By Francisco Munoz Alvarez

How to find Sessions Generating Lots of Redo

To find sessions generating lots of redo, you can use either of the following methods. Both methods examine the amount of undo generated. When a transaction generates undo, it will automatically generate redo as well.

The methods are:

1) Query V$SESS_IO. This view contains the column BLOCK_CHANGES which indicates how much blocks have been changed by the session. High values indicate a session generating lots of redo.

The query you can use is:

SQL> SELECT s.sid, s.serial#, s.username, s.program,

2 i.block_changes

3 FROM v$session s, v$sess_io i

4 WHERE s.sid = i.sid

5 ORDER BY 5 desc, 1, 2, 3, 4;

Run the query multiple times and examine the delta between each occurrence of BLOCK_CHANGES. Large deltas indicate high redo generation by the session.

2) Query V$TRANSACTION. These view contains information about the amount of undo blocks and undo records accessed by the transaction (as found in the USED_UBLK and USED_UREC columns).

The query you can use is:

SQL> SELECT s.sid, s.serial#, s.username, s.program,

2 t.used_ublk, t.used_urec

3 FROM v$session s, v$transaction t

4 WHERE s.taddr = t.addr

5 ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;

Run the query multiple times and examine the delta between each occurrence of USED_UBLK and USED_UREC. Large deltas indicate high redo generation by the session.

You use the first query when you need to check for programs generating lots of redo when these programs activate more than one transaction. The latter query can be used to find out which particular transactions are generating redo.

Useful Scripts

To see the redo generated since instance started:

col name format a30 heading ‘Statistic|Name’

col value heading ‘Statistic|Value’

start title80 “Redo Log Statistics”

spool rep_out\&db\red_stat

SELECT name, value

FROM v$sysstat

WHERE name like ‘%redo%’

order by name

/

spool off

pause Press enter to continue

ttitle off

The redo generated during my session since the session started:

select value redo_size

from v$mystat, v$statname

where v$mystat.STATISTIC# = v$statname.STATISTIC#

and name = ‘redo size’

/

The redo generated by current user sessions:

select v$session.sid, username, value redo_size

from v$sesstat, v$statname, v$session

where v$sesstat.STATISTIC# = v$statname.STATISTIC#

and v$session.sid = v$sesstat.sid

and name = ‘redo size’

and value > 0

and username is not null

order by value

/

Provide a current status for redo logs:

column first_change# format 999,999,999 heading Change#

column group# format 9,999 heading Grp#

column thread# format 999 heading Th#

column sequence# format 999,999 heading Seq#

column members format 999 heading Mem

column archived format a4 heading Arc?

column first_time format a25 heading First|Time

break on thread#

set pages 60 lines 132 feedback off

start title132 ‘Current Redo Log Status’

spool rep_out\&db\log_stat

select thread#, group#, sequence#,

bytes, members,archived,status,first_change#,

to_char(first_time,’dd-mon-yyyy hh24:mi’) first_time

from sys.v_$log

order by thread#, group#;

spool off

pause Press Enter to continue

set pages 22 lines 80 feedback on

clear breaks

clear columns

ttitle off

/

Provide redo log groups and log switch (archive generation) information:

set echo on

set linesize 150

set pagesize 500

column day format a16 heading ‘Dia’

column d_0 format a3 heading ’00’

column d_1 format a3 heading ’01’

column d_2 format a3 heading ’02’

column d_3 format a3 heading ’03’

column d_4 format a3 heading ’04’

column d_5 format a3 heading ’05’

column d_6 format a3 heading ’06’

column d_7 format a3 heading ’07’

column d_8 format a3 heading ’08’

column d_9 format a3 heading ’09’

column d_10 format a3 heading ’10’

column d_11 format a3 heading ’11’

column d_12 format a3 heading ’12’

column d_13 format a3 heading ’13’

column d_14 format a3 heading ’14’

column d_15 format a3 heading ’15’

column d_16 format a3 heading ’16’

column d_17 format a3 heading ’17’

column d_18 format a3 heading ’18’

column d_19 format a3 heading ’19’

column d_20 format a3 heading ’20’

column d_21 format a3 heading ’21’

column d_22 format a3 heading ’22’

column d_23 format a3 heading ’23’

column Total format 9999

column status format a8

column member format a40

column archived heading ‘Archived’ format a8

column bytes heading ‘Bytes|(MB)’ format 9999

Ttitle ‘Log Info’ skip 2

select l.group#,f.member,l.archived,l.bytes/1078576 bytes,l.status,f.type

from v$log l, v$logfile f

where l.group# = f.group#

/

Ttitle off

prompt =========================================================================================================================

Ttitle ‘Log Switch on hour basis’ skip 2

select to_char(FIRST_TIME,’DY, DD-MON-YYYY’) dia,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’00’,1,0)),0,’-‘,sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’00’,1,0))) d_0,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’01’,1,0)),0,’-‘,sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’01’,1,0))) d_1,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’02’,1,0)),0,’-‘,sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’02’,1,0))) d_2,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’03’,1,0)),0,’-‘,sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’03’,1,0))) d_3,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’04’,1,0)),0,’-‘,sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’04’,1,0))) d_4,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’05’,1,0)),0,’-‘,sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’05’,1,0))) d_5,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’06’,1,0)),0,’-‘,sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’06’,1,0))) d_6,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’07’,1,0)),0,’-‘,sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’07’,1,0))) d_7,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’08’,1,0)),0,’-‘,sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’08’,1,0))) d_5,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’09’,1,0)),0,’-‘,sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’09’,1,0))) d_9,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’10’,1,0)),0,’-‘,sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’10’,1,0))) d_10,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’11’,1,0)),0,’-‘,sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’11’,1,0))) d_11,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’12’,1,0)),0,’-‘,sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’12’,1,0))) d_12,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’13’,1,0)),0,’-‘,sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’13’,1,0))) d_13,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’14’,1,0)),0,’-‘,sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’14’,1,0))) d_14,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’15’,1,0)),0,’-‘,sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’15’,1,0))) d_15,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’16’,1,0)),0,’-‘,sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’16’,1,0))) d_16,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’17’,1,0)),0,’-‘,sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’17’,1,0))) d_17,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’18’,1,0)),0,’-‘,sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’18’,1,0))) d_18,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’19’,1,0)),0,’-‘,sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’19’,1,0))) d_19,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’20’,1,0)),0,’-‘,sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’20’,1,0))) d_20,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’21’,1,0)),0,’-‘,sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’21’,1,0))) d_21,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’22’,1,0)),0,’-‘,sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’22’,1,0))) d_22,

decode(sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’23’,1,0)),0,’-‘,sum(decode(substr(to_char(FIRST_TIME,’HH24′),1,2),’23’,1,0))) d_23,

count(trunc(FIRST_TIME)) Total

from v$log_history

group by to_char(FIRST_TIME,’DY, DD-MON-YYYY’)

order by to_date(substr(to_char(FIRST_TIME,’DY, DD-MON-YYYY’),5,15) )

/

Ttitle off

How to check for LOGGING/NOLOGGING objects in the DB:

Two example methods of querying the database for this information:

select owner , table_name, index_name
from dba_indexes
where logging=’NO’;

select tablespace_name, logging
from dba_tablespaces

/

Kind Regards,

Francisco Munoz Alvarez