OraFAQ Forum: Server Administration » rollback segment header contention

Home » RDBMS Server » Server Administration » rollback segment header contention

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

rollback segment header contention [message #216088]

Wed, 24 January 2007 23:38

sajut
Messages: 69
Registered: January 2007

Member

Hi

my query on v$waitstat give the following result

select class, count from v$waitstat where class like '%undo%';

CLASS COUNT
------------------ ----------
save undo block 0
save undo header 0
system undo header 0
system undo block 0
undo header 329
undo block 267

Is the above really a problem. because I am facing some performance related issues recently.
Can anyone advice how to over this issue please
Thanking in advance

Report message to a moderator

Re: rollback segment header contention [message #216105 is a reply to message #216088]

Thu, 25 January 2007 00:38

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

May be or may not be. You need to consider these numbers (which are minuscule in general standards by the way) over a period of time.
>>because I am facing some performance related issues recently
What exactly? How did you conclude that this was the problem?
Where exactly? Start with isolating the issue. Else it will be a wild goose chase.

Report message to a moderator

Re: rollback segment header contention [message #216108 is a reply to message #216105]

Thu, 25 January 2007 00:49

sajut
Messages: 69
Registered: January 2007

Member

because i got an ora-01555 error. and the statspack report on rollback segments shows the following
RBS No Segment Size Avg Active Optimal Size Maximum Size
------ --------------- --------------- --------------- ---------------
0 385,024 0 385,024
1 3,268,608 849,420 8,511,488
2 2,220,032 583,725 6,414,336
3 495,050,752 887,074,077 495,050,752
4 302,112,768 441,500,473 302,112,768
5 360,833,024 159,613,926 360,833,024
6 12,705,792 142,947,706 142,729,216
7 2,220,032 650,012 6,414,336
8 5,365,760 10,512,261 30,597,120
11 6,414,336 883,805 6,479,872
12 2,220,032 671,572 7,462,912

Report message to a moderator

Re: rollback segment header contention [message #216159 is a reply to message #216108]

Thu, 25 January 2007 05:51

sajut
Messages: 69
Registered: January 2007

Member

my query on v$rollstat gives the following results
Are these values optimal. or are these values any way affect performance

and the storage value for OPTIMAL is not set, do I need to set it in automatic undo mode?

NAME EXTENTS WAITS OPTSIZE HWMSIZE SHRINKS WRAPS EXTENDS AVESHRINK AVEACTIVE
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
SYSTEM 6 0 385024 0 0 0 0 0
_SYSSMU1$ 6 3 8511488 7 59 16 2097152 790847
_SYSSMU2$ 6 0 6414336 6 57 15 2097152 614018
_SYSSMU3$ 8 7 495050752 24 240 177 22850218 472215576
_SYSSMU4$ 10 18 302112768 32 264 183 11993088 154819333
_SYSSMU5$ 10 74 360833024 38 360 292 20254073 56545913
_SYSSMU6$ 7 33 142729216 17 141 114 10239036 128757793
_SYSSMU7$ 6 2 6414336 7 51 16 1947355 700062
_SYSSMU8$ 8 6 30597120 15 148 86 5666679 4934865
_SYSSMU11$ 8 2 6479872 5 64 19 2739404 792245
_SYSSMU12$ 6 5 7462912 9 55 20 1980643 588045

Thanks in advance

Report message to a moderator

Re: rollback segment header contention [message #216160 is a reply to message #216159]

Thu, 25 January 2007 06:05

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>do I need to set it in automatic undo mode
Using 9i?
Increase UNDO_RETENTION.
If that does not work, increase undo tablespace.

[Updated on: Thu, 25 January 2007 06:05]

Report message to a moderator

Re: rollback segment header contention [message #216162 is a reply to message #216160]

Thu, 25 January 2007 06:22

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

And in most cases ora-01555 is because of bad code.
Isolate the sql that experiences this and have a look into it.
May be you are using too much of commits.

Report message to a moderator

Previous Topic:	alert log msg - very urgent
Next Topic:	ORA-00604 and ORA-12705 Error on Oracle SQL Developer

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 05:46:30 CDT 2024