identify a particular session
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: identify a particular session

  1. #1
    Join Date
    Nov 2000
    Posts
    440

    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?

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    mine the logs and see what is actually in them

  3. #3
    Join Date
    Nov 2000
    Posts
    440
    i need a query, i cannot open 100MEG archive logs and check them

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    there is no such query

  5. #5
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Identify the time of archive log generation and schedule the below query in appropriate interval.

    select se.username,st.value
    from v$sesstat st, v$statname sn,v$session se
    where st.sid=se.sid
    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 05:02 AM. Reason: update
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    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

  7. #7
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Quote Originally Posted by davey23uk
    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.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  8. #8
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    What about
    column name format a 20
    set pause on
    select a.sid,b.name,a.value
    from v$sesstat a,v$statname b
    where a.statistic#=b.statistic#
    and upper(b.name) like '%REDO%' order by sid

    Pay attention on
    redo entries
    redo writes
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width