Re: High "transaction rollbacks" value in v$sysstat

On 9/12/05, Allen, Brandon <Brandon.Allen_at_oneneck.com> 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.
--
http://www.freelists.org/webpage/oracle-l