RE: How to identify large transactions

  • From: "John Dunn" <jdunn@xxxxxxxxx>
  • To: "'Jared Still'" <jkstill@xxxxxxxxx>
  • Date: Wed, 5 Jan 2005 14:16:23 -0000

The problem seems to be a large delete generating lots of redo/rollback.

I presume that several smaller deletes based upon a column in the table will
require less rollback segment space?



 delete from table whee column <= 100000;
 delete from table whee column > 100000 and column < 200000;


The table has a primary index, which I suppose if also not helping with the
rollback space.

-----Original Message-----
From: Jared Still [mailto:jkstill@xxxxxxxxx]
Sent: Tuesday, January 04, 2005 9:53 PM
To: jdunn@xxxxxxxxx
Cc: Oracle-L@xxxxxxxxxxxxx
Subject: Re: How to identify large transactions

You will may want to check out v$session_longops.

In regards to suggestions that you investigate v$transaction,
this will get you started:

select s.osuser
      ,substr(sa.sql_text,1,200) txt
from v$session s,
     v$transaction t,
     dba_rollback_segs r,
     v$sql sa
where s.saddr=t.ses_addr
and   t.xidusn=r.segment_id(+)
and   s.sql_address=sa.address(+)

You will need to adjust it if using Automatic Undo.


On Tue, 4 Jan 2005 13:08:03 -0000, John Dunn <jdunn@xxxxxxxxx> wrote:
> I believe that some developers sql is generating large transactions
> due to lack of commit statements.
> How can I identify these large transactions and the sql responsible?
> --
> //

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist


Other related posts: