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?
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@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
,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@xxxxxxxxx> 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?
>
> --
> //www.freelists.org/webpage/oracle-l
>
--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
--
//www.freelists.org/webpage/oracle-l
Other related posts:
