-
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
-
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
-
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?
-
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
-
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?
-
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
-
Not my script, I don't have any parenthesis...
Jeff Hunter
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|