I have increased my number of rollback segments, but it doesnt seems to help when i check for the undo from the V$waitstat table. However, when I check for the waits from v$rollstat table, they are showing 0 or minimum. Therefore why is it that the number of undo are not decreasing ??
To get the clear picture of the rollback segments contention, you have to look at the ratio between the total number of request for data over the same period of time and the statistics in v$waitstat, not just the numbers in v$waitstat.
Get the statistics with:
select class, count
where class like '%undo%;
Then get the number of consistent gets:
where name = 'consistent gets';
The ratio of waits for any class should be less than 1% of the total number of requets. The solution is to add more rollback segments.
Now, you know the count number won't Decrease don't you... It'll only reset to zero after instance shutdown and restart. If the undo count don't increase (or are increasing VERY slowly), you may have solved the issue.