DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Trans Tracking

  1. #1
    Join Date
    Nov 2000
    Posts
    416
    Guys, Can you tell me how can I see at anytime what is the top5 or top10 largest transaction is going in Rollback and etc and what are they? A good script appreciated
    An ounce of prevention is worth a pound of cure

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Code:
    col name for a10
    col username for a20
    col status for a10
    col sql_text for a40
    set linesize 132
    select a.name, b.xacts, c.sid, c.serial#, c.username, d.sql_text, e.status
    from  v$rollname a, v$rollstat b, v$session c, v$sqltext d, v$transaction e
    where a.usn = b.usn
    and b.usn=e.xidusn
    and c.taddr = e.addr
    and c.sql_address = d.address
    and c.sql_hash_value = d.hash_value
    order by a.name, c.sid, d.piece
    /
    Jeff Hunter

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    If I may add:

    IMHO it is very inaccurate and problematic to mesure the size of the transaction by the amount if UNDO information it creates. For example, INSERT transaction will produce minimal amount of rollback, when compared to equivalent UPDATE or DELETE transaction.

    I think slightly more accurate method for measuring transaction sizes would be to track the amount of REDO information generated. Of course, if the transactions are not happening in NOLOGGING mode.

    But then again, if majority of the transactions on your system are of the same tipe (for example only INSERTS or only UPDATES) then it makes no difference which method you use if you only are looking for the top-N largest transactions.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Nov 2000
    Posts
    416
    Jurig & Jeff , We have all kind of Transacrions I mean INSERT & UPDATE and DELETE , so Jeff if you agree with what Jurig said and I think he is right, what is a better measure / script you recommend and/or how can we rank Top-N based on track the amount of REDO generated.

    An ounce of prevention is worth a pound of cure

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Well, the way I see it is this:

    It doesn't matter which is better measurement: the amount of REDO or UNDO generated. Because I think you have one much better measurement available - the amount of logical I/O generated by a transaction. And you can get this directly from V$TRANSACTION.

    So the quick and dirty script for top-10 transactions would be:

    select * from
    (select s.sid, s.serial#, t.log_io, sql.sql_text
    from v$transaction t, v$session s, v$sqlarea sql
    where s.taddr = t.addr
    and s.sql_address = sql.address
    and s.sql_hash_value = sql.hash_value
    order by log_io desc
    )
    where rownum <= 10;

    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Nov 2000
    Posts
    416
    Jeff , your script gives this error :

    ORA-00907: missing right parenthesis

    I don't know why ?
    An ounce of prevention is worth a pound of cure

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Not my script, I don't have any parenthesis...
    Jeff Hunter

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    If you are refering to my script, then there could not be any missing parenthesis - i only have two in it: one left and one right. And it runs just fine for me.....

    But I know where could be your problem: perhaps you are using a release 8.0 or earlier? The allowance to use ORDER BY inside a view (inline view in my example) was introduced only in 8i. So the implementation of TOP-N or BOTTOM-N queries is verry different (and more complicated and less efficient) in releases prior to 8i. But it can be done.

    So here is the modification of my original query that should return the correct output for TOP-10 transactions based on their logical I/O. Note that it will again not be usable for releases prior to 7.3 (or maybe 7.2, I'm not sure when inline views were introduced in Oracle7). Note also that I can't test it on any release prior to 8i right now, but i think it should run OK.

    select x.sid, x.serial#, y.log_io, x.sql_text
    from
    (select t.log_io, t.addr
    from v$transaction t, dual
    where -t.log_io = decode(dummy(+),'X',-1,-1)
    ) y,
    (select s.sid, s.serial#, s.taddr, sql.sql_text
    from v$session s, v$sqlarea sql
    where s.sql_address = sql.address
    and s.sql_hash_value = sql.hash_value
    ) x
    where y.addr = x.taddr
    and rownum <= 10;




    [Edited by jmodic on 04-05-2002 at 02:30 PM]
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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