RE: undo segments online causing performance problems.

  • From: <Peter.Hitchman@xxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 29 Dec 2005 16:15:29 -0000

Are you re-starting the database each day? From what you said here and other 
replies, that seems to be implied. If so, why is this being done?



-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of The Human Fly
Sent: 29 December 2005 10:02
To: oracle-l
Subject: undo segments online causing performance problems.

Hello Everbody,

Our share trading application database, patch set 9205, running on AIX
5.3 64bit with 12CPU + 30 GB Memory,. Everyday, during trading
beginning hours, end-users complains that the database is performing
very slow, like hang stage for some times. We noticed that Oracle has
brough many undo segs. online.  As per Oracle Support, we have set an
databases event '10511' for SMON to prevent taking unused undo segs
offline, i.e. keeping always undo segs online. But, this event brought
some other problems. SMON is not offlining the undo segs. rather, what
we see is that a lot of expired undo segs and everyday brings arond 40
undo segs. online and still the same story repeates. When we count for
online undo segs from dba_rollback_segs, what we see is around 100+
undo segs. are online.
 We have set session parameter around 1400 and our undo tablespace
size is  6.6 gb and undo retention is set to default, i.e. 3 hrs.

Is there any one facing the similar kind of problem. If so, is there
any work around. We are thinking of going back to MUM, where we create
around 100 rollback segments manually and size them accordingly. Is
that a good idea?

Thanks for your time.

Best Regards,
Syed Jaffar Hussain
OCP 8i & 9i DBA,
Banque Saudi Fransi,
Saudi Arabia
"Winners don't do different things. They do things differently."


Other related posts: