Find Sessions Generating Lots of Redo or Archive logs

If You got below Alert than what is your second steps:-

From: idea Grid Alerts [mailto:oracle@XXXX.com]
Sent: Thursday, April 10, 2013 3:50 PM
To: Sujeet jha
Subject: EM Alert: Critical:idea1.oracle.com - Metrics "Redo Generated Per Sec" is at 748413.88013

Target Name=idea1.oracle.com 
Target type=Database Instance
Host=idea1.oracle.com
Occurred At=Apr 10, 2014 4:51:14 PM (UTC-04:00)
Message=Metrics "Redo Generated Per Sec" is at 748413.8774
Metric=Redo Generated (per second)
Metric value=748413
Severity=Critical
Acknowledged=No
Notification Rule Name=Idea1 production monitoring
Notification Rule Owner=SYSMAN

what is causing this increase in redo in the last 1 hour. I am try to identify the session and the associated sql with the redo size of each that would total this metrics.

Find Sessions Generating Lots of Redo or Archive logs

1. Login to a user with dba privilege

2. Execute the query

sql> SELECT s.sid, s.serial#, s.username, s.program,  i.block_changes

         FROM v$session s, v$sess_io i

       WHERE s.sid = i.sid 

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

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

Question: What is the best way to tune the Oracle redo logs?

Answer: Oracle redo logs are archived when you turn-on archivelog mode, and the overhead relates to the work done by the LGWR and ARCH background processes as they write the redo information. 

The steps for tuning redo log performance are straightforward:

1 - Determine the optimal sizing of the log_buffer.
 

2 - Size online redo logs to control the frequency of log switches and minimize system waits.

3 - Optimize the redo log disk to prevent bottlenecks.  In high-update databases, no amount of disk tuning may relieve redo log bottlenecks, because Oracle must push all updates, for all disks, into a single redo location.

Once you have optimized your redo and I/O sub-system, you have few options to relieve redo-induced contention.  This can be overcome by employing super-fast solid-state disk for your online redo log files, since SSD has far greater bandwidth than platter disk.  This will greatly improve DML throughput for high-update databases.  As SSD prices continue to fall, SSD becomes increasing affordable, and SSD will someday replace spindle platter disk for all Oracle data storage.

Size of the redo log members (files on disk) size, then use the query below:

-- Show Redo Logs info

set linesize 300

column REDOLOG_FILE_NAME format a50

SELECT

    a.GROUP#,

    a.THREAD#,

    a.SEQUENCE#,

    a.ARCHIVED,

    a.STATUS,

    b.MEMBER    AS REDOLOG_FILE_NAME,

    (a.BYTES/1024/1024) AS SIZE_MB

FROM v$log a

JOIN v$logfile b ON a.Group#=b.Group#

ORDER BY a.GROUP# ASC;

The query output should look like:

GROUP   #THREAD#  SEQUENCE# ARC STATUS   REDOLOG_FILE_NAME      SIZE_MB

---------- ---------- ---------- --- ---------------- ---------------------

1       1       4 YES INACTIVE /ORACLE/oradata/orcl1/redo01.log   50

2       1       5 YES INACTIVE /ORACLE/oradata/orcl1/redo02.log   50

3       1       6 NO  CURRENT  /ORACLE/oradata/orcl1/redo03.log   50

 Tracking Redo Log usage

select * from 

(select a.username,a.sid,b.value 

from v$session a,v$sesstat b,v$statname c 

where a.sid=b.sid and b.statistic#=c.statistic# and c.name='redo size' order by b.value desc) 

where rownum < 11;

Some of the scripts which can be useful. 

PROMPT REDO GENERATION:-

select value from v$sysstat 
where name = 'redo size' 
/ 

PROMPT 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' 
/ 

PROMPT 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 
/ 
----------------------------------------------------------------------------------------------------

SELECT s.sid, s.serial#, s.username, s.program, 
i.block_changes 
FROM v$session s, v$sess_io i 
WHERE s.sid = i.sid 
ORDER BY 5 desc, 1, 2, 3, 4; 
/ 

----------------------------------------------------------------------------------------------------------

SELECT s.sid, s.serial#, s.username, s.program, 
t.used_ublk, t.used_urec 
FROM v$session s, v$transaction t 
WHERE s.taddr = t.addr 
ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;

-----------------------------------------------------------------------------------------------------

select b.inst_id, b.SID, b.serial# sid_serial, b.username, machine, b.osuser, b.status, a.redo_mb MB

from (select n.inst_id, sid, round(value/1024/1024) redo_mb from gv$statname n, gv$sesstat s

where n.inst_id=s.inst_id and n.statistic#=134 and s.statistic# = n.statistic# order by value desc) a, gv$session b

where b.inst_id=a.inst_id

  and a.sid = b.sid

and   rownum <= 10;

----------------------------------------------------------------------------------------------------------

SELECT to_char(begin_interval_time,’YYYY_MM_DD HH24:MI’),

dbms_lob.substr(sql_text,4000,1),

dhss.instance_number,

dhss.sql_id,executions_delta,rows_processed_delta

FROM dba_hist_sqlstat dhss,

dba_hist_snapshot dhs,

dba_hist_sqltext dhst

WHERE upper(dhst.sql_text) LIKE ‘%segmentname%’

AND dhss.snap_id=dhs.snap_id

AND dhss.instance_Number=dhs.instance_number

AND begin_interval_time BETWEEN to_date(’2013_06_22 17',’YYYY_MM_DD HH24')

AND to_date(’2013_07_22 21',’YYYY_MM_DD HH24')

AND dhss.sql_id = dhst.sql_id;

The accounting for redo size by session is located in v$sesstat.  You can do a

Simple query for a RAC cluster to see the largest redo generating session:

select s.inst_id, s.sid, serial#, program, module, username, value redo_size

from gv$session s, gv$sesstat ss, v$statname sn where s.sid = ss.sid and

ss.statistic# = sn.statistic# and sn.name = 'redo size'

and s.inst_id = ss.inst_id

order by redo_size

Queries for getting the information for the redo generation based object name and will get the sql id’s.

1)  SELECT dhso.object_name,

sum(db_block_changes_delta)

FROM dba_hist_seg_stat dhss,

dba_hist_seg_stat_obj dhso,

dba_hist_snapshot dhs

WHERE dhs.snap_id = dhss.snap_id

AND dhs.instance_number = dhss.instance_number

AND dhss.obj# = dhso.obj#

AND dhss.dataobj# = dhso.dataobj#

AND begin_interval_time BETWEEN to_date('2014_04_10 16:50', 'YYYY_MM_DD HH24:MI') AND to_date('2014_04_10 17:05', 'YYYY_MM_DD HH24:MI')

GROUP BY dhso.object_name

order by sum(db_block_changes_delta) desc

/

OBJECT_NAME                    SUM(DB_BLOCK_CHANGES_DELTA)

------------------------------ ---------------------------

PLUSER_SORT                                        1762784

PK_PORTALUSER                                       831232

SEQ$                                                815168

SESSION_RATES                                        73792

SESSION_PROMOTIONS                                   64656

CDATA                                                59472

SESSION_PROMOTIONS                                   43376

SESSION_RATES                                        39808

SESSION_NON_AVAILABILITY                             19360

SESSION_NON_AVAIL                                     9136

SESSION_AVAILABILITY                                  6160

2)  SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24') WHEN,

       dbms_lob.substr(sql_text,4000,1) SQL,

       dhss.instance_number INST_ID,

       dhss.sql_id,

       executions_delta exec_delta,

       rows_processed_delta rows_proc_delta

  FROM dba_hist_sqlstat dhss,

       dba_hist_snapshot dhs,

       dba_hist_sqltext dhst

  WHERE upper(dhst.sql_text) LIKE '%&OBJECT_NAME%'

    AND ltrim(upper(dhst.sql_text)) NOT LIKE 'SELECT%'

    AND dhss.snap_id=dhs.snap_id

    AND dhss.instance_number=dhs.instance_number

    AND dhss.sql_id=dhst.sql_id

    AND begin_interval_time BETWEEN to_date('2014_04_10 16:50', 'YYYY_MM_DD HH24:MI')

                                AND to_date('2014_04_10 17:05', 'YYYY_MM_DD HH24:MI')

;