Re: How to identify large transactions

From: Jared Still <jkstill_at_gmail.com>
Date: Tue, 4 Jan 2005 21:52:46 +0000
Message-ID: <bf46380501041352754d735@mail.gmail.com>

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

Received on Tue Jan 04 2005 - 15:52:32 CST