Home » RDBMS Server » Server Administration » Which session generate archive redo log (Sun Solaris version 9)
Which session generate archive redo log [message #305558] Tue, 11 March 2008 04:59 Go to next message
suiren97
Messages: 48
Registered: May 2007
Location: Malaysia
Member
We are in 10g - 10.1.0.4 database.
Archive redo log - 200MB generated almost every sec.
How to check which session is causing the redo log generation?
Pls advise. Thanks.
Re: Which session generate archive redo log [message #305580 is a reply to message #305558] Tue, 11 March 2008 05:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Tracking Redo Log usage

Regards
Michel
Re: Which session generate archive redo log [message #305587 is a reply to message #305580] Tue, 11 March 2008 05:50 Go to previous messageGo to next message
suiren97
Messages: 48
Registered: May 2007
Location: Malaysia
Member
Hi, Thanks for yr fast response. During the query, there are more than 246 rows selected. How to identify which session is the one causing the problem?

select s.sid, s.serial#,i.block_changes from v$session s, v$sess_io i where s.sid = i.sid order by 3

SID SERIAL# BLOCK_CHANGES
---------- ---------- -------------
387 1992 297442
381 3084 4381170
389 106 4470401
823 1 18854811

246 rows selected.

Re: Which session generate archive redo log [message #305601 is a reply to message #305587] Tue, 11 March 2008 06:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You picked up the only WRONG query from the thread (and I clearly said it).
Use:
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;

Regards
Michel
icon14.gif  Re: Which session generate archive redo log [message #305612 is a reply to message #305601] Tue, 11 March 2008 06:31 Go to previous messageGo to next message
suiren97
Messages: 48
Registered: May 2007
Location: Malaysia
Member
Thank you so much for your kind support. Thank you. Smile
Re: Which session generate archive redo log [message #305643 is a reply to message #305612] Tue, 11 March 2008 08:03 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can add "and a.username is not null and a.type='USER'" if you don't want to get background processes.

Regards
Michel
Previous Topic: ORACLE not available
Next Topic: Reclaim Free Datafile Size
Goto Forum:
  


Current Time: Sun Dec 04 12:37:07 CST 2016

Total time taken to generate the page: 0.07902 seconds