Re: High "transaction rollbacks" value in v$sysstat
- From: Jared Still <jkstill@xxxxxxxxx>
- To: Brandon.Allen@xxxxxxxxxxx
- Date: Mon, 12 Sep 2005 17:11:19 -0700
On 9/12/05, Allen, Brandon <Brandon.Allen@xxxxxxxxxxx> wrote:
>"write consistency" does not show up in the 'transaction rollbacks'
statistic. So, I've come
> to believe that the high 'transaction rollbacks' are the result of failed
> DML statements
> e.g. due to unique constraint violations.
>
Easy to test:
Session 1:
drop table rtest;
create table rtest ( x number, primary key (x));
insert into rtest values(1);
Session 2:
select n.name, s.value
from v$mystat s, v$statname n
where n.statistic# = s.statistic#
and n.name in ('user rollbacks','transaction rollbacks')
/
NAME VALUE
---------------------------------------------------------------- ----------
user rollbacks 0
transaction rollbacks 0
2 rows selected.
insert into rtest values(1);
(waits...)
Session 1:
commit;
Session 2:
insert into rtest values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (JS.SYS_C0022235) violated
select n.name, s.value
from v$mystat s, v$statname n
where n.statistic# = s.statistic#
and n.name in ('user rollbacks','transaction rollbacks')
/
NAME VALUE
---------------------------------------------------------------- ----------
user rollbacks 0
transaction rollbacks 1
2 rows selected.
Do this a few times, and xaction rollbacks will continue to increase
while user rollbacks remains at 0.
--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
11+ years of trying to appear to know what I'm doing.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
