RE: To find out the Top process or Top User sessions generating highest number of archive logs

I note that the query below always seems to return smon as the largest redo 
generator on the couple of systems where I checked so if you are interested in 
only user sessions you might want to add "and s.username is not null" as a 
condition to filter out the Oracle rdbms background sessions.


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Jeremiah Wilton
Sent: Thursday, February 16, 2012 10:06 AM
To: anuragdba@xxxxxxxxx
Cc: ORACLE-L
Subject: Re: To find out the Top process or Top User sessions generating 
highest number of archive logs

On Feb 15, 2012, at 10:37 PM, Anurag Verma wrote:
> Hi all,
> 
> How to find out which process or user session(s) are generating 
> highest number of archive logs in a RAC database?
> 
> My database is in 10.2.0.3 RAC
> 
> In 10.2.0.3 Grid control, I noticed there is a link "Top Segments", 
> but it does not have any info on segments having highest physical writes.


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

This query might help you find the culprit. However, this only gives you the 
currently logged-in sessions and their total redo size since they logged in. So 
if you are seeing lots of redo generation right now, you need to sample this 
query several times over a period when the redo is being generated, and delta 
the values, using instead, sid and serial# as the keys.

You could write a simple piece of PL/SQL to do this, or you could use Tanel 
Poder's wonderful Snapper script, available right here:

http://files.e2sn.com/scripts/snapper.sql

Good luck!
--
Jeremiah Wilton
http://www.bluegecko.net



--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l


Other related posts: