Home » RDBMS Server » Server Administration » rollback segment header contention
rollback segment header contention [message #216088] Wed, 24 January 2007 23:38 Go to next message
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
Re: rollback segment header contention [message #216105 is a reply to message #216088] Thu, 25 January 2007 00:38 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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.
Re: rollback segment header contention [message #216108 is a reply to message #216105] Thu, 25 January 2007 00:49 Go to previous messageGo to next message
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

Re: rollback segment header contention [message #216159 is a reply to message #216108] Thu, 25 January 2007 05:51 Go to previous messageGo to next message
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
Re: rollback segment header contention [message #216160 is a reply to message #216159] Thu, 25 January 2007 06:05 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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 Go to previous message
Mahesh Rajendran
Messages: 10672
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.
Previous Topic: alert log msg - very urgent
Next Topic: ORA-00604 and ORA-12705 Error on Oracle SQL Developer
Goto Forum:
  


Current Time: Sat Dec 10 03:00:02 CST 2016

Total time taken to generate the page: 0.13368 seconds