RE: How to identify large transactions

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?

e.g

begin

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

etc

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

-----Original Message-----
From: Jared Still [mailto:jkstill_at_gmail.com] Sent: Tuesday, January 04, 2005 9:53 PM
To: jdunn_at_sefas.com
Cc: Oracle-L_at_freelists.org
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


      ,s.username

      ,s.sid

      ,r.segment_name

      ,t.space

      ,t.recursive

      ,t.noundo

      ,t.used_ublk

      ,t.used_urec

      ,t.log_io

      ,t.phy_io

      ,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.

Jared

On Tue, 4 Jan 2005 13:08:03 -0000, John Dunn <jdunn_at_sefas.com> wrote: > I believe that some developers sql is generating large transactions possibly

> due to lack of commit statements.
>
> How can I identify these large transactions and the sql responsible?
>
> --
> http://www.freelists.org/webpage/oracle-l
>

--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l