Re: To find out the Top process or Top User sessions generating highest number of archive logs
- From: Jeremiah Wilton <jwilton@xxxxxxxxxxxxx>
- To: anuragdba@xxxxxxxxx
- Date: Thu, 16 Feb 2012 08:06:04 -0700
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
