High "transaction rollbacks " value in v$sysstat 2005-09-12 - By Jared Still
On 9/12/05, Allen, Brandon <Brandon.Allen@(protected)> 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 (See ORA-00001.ora-code.com): 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
|
|
