identify a particular session
One of my database is generating archive logs a lot lately. There is more that 1 session doing a lot of dml.
how can i identify the top session that generate the most redo?
mine the logs and see what is actually in them
i need a query, i cannot open 100MEG archive logs and check them
Identify the time of archive log generation and schedule the below query in appropriate interval.
from v$sesstat st, v$statname sn,v$session se
and st.STATISTIC# = sn.STATISTIC#
and sn.name ='redo size'
and se.username is not null
order by value desc;
Capture the statisitcs to your own table.
Last edited by Thomasps; 01-25-2007 at 04:02 AM.
thats not going to work if the session is not alive anymore or not running.
Whats wrong with mining 100Mb logs - they are pretty small really
Fine... Capture the statistics from a "logoff" trigger. Redo mining is not an issue as long as it is managble.
Originally Posted by davey23uk
column name format a 20
set pause on
from v$sesstat a,v$statname b
and upper(b.name) like '%REDO%' order by sid
Pay attention on
redo blocks written
redo write time
PLEASE NOTE: This shows only the redo gereration of the currently connected sessions since the moment they got connected. No idea about all the sessions that have generated redo and got disconnected
Click Here to Expand Forum to Full Width